SQL report

[former_member208987]

Hi,

i created an query but want to add an extra line per docnum with the doctotal of the invoice

SELECT T0.DocNum, CONVERT (VARCHAR(10), T0.DocDate,5) AS [Invoice Date] , T1.BaseRef, T1.LineNum, T1.ItemCode, T1.Dscription, T1.Quantity, T1.Price, T1.LineTotal, SUM(T0.DocTotal) AS DocTotal FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocDate >= '20110701' AND T0.DocDate <= '20110731'

GROUP BY T0.DocNum, T0.DocDate, T1.BaseRef, T1.LineNum,T1.ItemCode, T1.Dscription,

T1.Quantity, T1.Price, T1.LineTotal

How can i do this??

Mark

[Former Member · Accepted solution]

Hi Mark......

Try this........

SELECT T0.DocNum, (CONVERT(VARCHAR(11),T0.DOCDATE,106)) AS 'Invoice Date' , T1.BaseRef, T1.LineNum, T1.ItemCode, T1.Dscription, T1.Quantity, T1.Price, T1.LineTotal, SUM(T0.DocTotal) AS DocTotal FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocDate >= '20100401' AND T0.DocDate <= '20110831'
GROUP BY T0.DocNum, T0.DocDate, T1.BaseRef, T1.LineNum,T1.ItemCode, T1.Dscription,
T1.Quantity, T1.Price, T1.LineTotal
Union All
SELECT T0.DocNum, Null, Null, Null, Null,'Total', Sum(T1.Quantity), Null, Sum(T1.LineTotal), SUM(T0.DocTotal) FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocDate >= '20110701' AND T0.DocDate <= '20110731'
GROUP BY T0.DocNum, T0.DocDate, T1.BaseRef, T1.LineNum,T1.ItemCode, T1.Dscription,
T1.Quantity, T1.Price, T1.LineTotal
Order By 1,6

Regards,

Rahul

[Former Member]

Hi Mark,

Is this you are looking for?

SELECT T0.DocNum, CONVERT (VARCHAR(10), T0.DocDate,5) AS 'Invoice Date' , T1.BaseRef, T1.LineNum, T1.ItemCode, T1.Dscription, T1.Quantity, T1.Price, T1.LineTotal, SUM(T0.DocTotal) AS DocTotal

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocDate >= '20110701' AND T0.DocDate <= '20110731'

GROUP BY T0.DocNum, T0.DocDate, T1.BaseRef, T1.LineNum,T1.ItemCode, T1.Dscription,

T1.Quantity, T1.Price, T1.LineTotal

UNION ALL

SELECT T0.DocNum, '','' ,'' ,'' ,'' ,0,0,0, SUM(T0.DocTotal) AS DocTotal

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocDate >= '20110701' AND T0.DocDate <= '20110731'

GROUP BY T0.DocNum

ORDER By T0.DocNum

Thanks,

Gordon