sum of Line total is quite differnt in my query?

[karthick_s8]

Hi all,

in this Query Sum(Line Total ) is providing Different ans when Compare to Actual line Total

SELECT DISTINCT T2.[DocEntry],

T3.[DocNum], T3.[DocDate],T3.CardName, T3.[NumAtCard], T3.[DocTotal],

T3.[Comments],T2.[BaseRef],T3.TaxDate, 

case  when T4.ItmsGrpCod= 102 then (sum(T2.LineTotal))

when  T4.ItmsGrpCod<> 103 then  (sum(T2.LineTotal))

end as 'Total Amount-s',

T3.VatSum,

T3.[U_PrevVATBill],

T3.[U_PreSTbill],  T5.[U_Stdate], T5.[U_Clodate], T5.[U_Noc],

T7.[Street], T7.[Block], T7.[ZipCode], T7.[City], T7.[Country], T7.[State]

FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

LEFT OUTER JOIN INV1 T2 ON T2.BaseEntry=T1.DocEntry AND T2.BaseLine=T1.LineNum

INNER JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry]

INNER JOIN OITM T4 ON T1.[ItemCode] = T4.[ItemCode]

LEFT JOIN [dbo].[@TNDRMGMT]  T5 ON T5.U_ClntCod=T0.CardCode and T5.DocEntry=T0.DocEntry

LEFT JOIN INV4 T6 ON T3.[DocEntry] = T6.[DocEntry]

INNER JOIN CRD1 T7 ON T3.[CardCode] = T7.[CardCode]

WHERE T0.DocEntry=1

GROUP BY T2.[DocEntry],T3.[DocNum], T3.[DocDate],T3.CardName, T3.[NumAtCard], T3.[DocTotal],

T3.[Comments],T2.[BaseRef],T3.TaxDate,  T3.VatSum,

T3.[U_PrevVATBill],

T3.[U_PreSTbill],  T5.[U_Stdate], T5.[U_Clodate], T5.[U_Noc],

T4.ItmsGrpCod,T7.[Street], T7.[Block], T7.[ZipCode], T7.[City], T7.[Country], T7.[State]

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT DISTINCT T2.[DocEntry],

T3.[DocNum], T3.[DocDate],T3.CardName, T3.[NumAtCard], T3.[DocTotal],

T3.[Comments],T2.[BaseRef],T3.TaxDate,

case  when T4.ItmsGrpCod= 102 then (sum(T2.LineTotal))

when  T4.ItmsGrpCod<> 103 then  (sum(T2.LineTotal))

end as 'Total Amount-s',

T3.VatSum,

T3.[U_PrevVATBill],

T3.[U_PreSTbill],  T5.[U_Stdate], T5.[U_Clodate], T5.[U_Noc],

T7.[Street], T7.[Block], T7.[ZipCode], T7.[City], T7.[Country], T7.[State]

FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

LEFT OUTER JOIN INV1 T2 ON T2.BaseEntry=T1.DocEntry AND T2.BaseLine=T1.LineNum

INNER JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry]

INNER JOIN OITM T4 ON T1.[ItemCode] = T4.[ItemCode]

LEFT JOIN [dbo].[@TNDRMGMT]  T5 ON T5.U_ClntCod=T0.CardCode and T5.DocEntry=T0.DocEntry

LEFT JOIN INV4 T6 ON T3.[DocEntry] = T6.[DocEntry]

INNER JOIN CRD1 T7 ON T3.[CardCode] = T7.[CardCode]  inner join OCRD T8 on T8.cardcode  = t7.cardcode and t8.billtodef = t7.address

WHERE T0.DocEntry=1

GROUP BY T2.[DocEntry],T3.[DocNum], T3.[DocDate],T3.CardName, T3.[NumAtCard], T3.[DocTotal],

T3.[Comments],T2.[BaseRef],T3.TaxDate,  T3.VatSum,

T3.[U_PrevVATBill],

T3.[U_PreSTbill],  T5.[U_Stdate], T5.[U_Clodate], T5.[U_Noc],

T4.ItmsGrpCod,T7.[Street], T7.[Block], T7.[ZipCode], T7.[City], T7.[Country], T7.[State]

Thanks

[Former Member]

Hi Karthik,

I think due to CRD1 table only your query result getting duplicated, Kindly remove that table and its fields in query and check the result.

Regards,

Bharathiraja

[former_member212181]

Hi karthick,

Please check the case statement part for Line Total. It looks like incomplete.

and change INV1 join as below

LEFT OUTER JOIN INV1 T2 ON T2.BaseType = 17 and T2.BaseEntry=T1.DocEntry AND T2.BaseLine=T1.LineNum

Thanks

Unnikrishnan

[former_member212181]

If Query results showing more than actual figure, check for duplicate records by commenting Line total area.

Difference may occur due to duplicate records in query result.

Thanks

Unnikrishnan

[karthick_s8]

I have tried BaseType Still Problem not Solved.Query may Be Problem i think