Skip to Content
0
Dec 31, 2022 at 11:12 AM

Add Customer Code & GST No.in this report.

65 Views

select z.CardName,z.GroupName,z.state,z.City,z.JAN,z.FEB,z.MAR,z.APR,z.MAY,z.JUL,z.JUN,z.JUL,z.AUG,z.SEP,z.OCT,z.NOV,z.DEC,z.TOTAL from

(

SELECT DISTINCT T1.[CardName],-- ,T2.[GroupName],t4.name as'State'--,t3.City,

(Select a.GroupName From OCRG a where a.GroupCode=T1.GroupCode) as "GroupName",

--t3.StateB,

(select name from ocst where code=t2.state and Country=t2.Country)as state,

t1.city,

--(select name from ocst where t2.state=OCST.Code)as state,

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 1 AND YEAR(T2.DocDate) = 2022 AND T2.[CardCode] = T0.[CardCode] and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)) as 'JAN',

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 2 AND YEAR(T2.DocDate) = 2022 AND T2.[CardCode] = T0.[CardCode] and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)) as 'FEB',

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 3 AND YEAR(T2.DocDate) = 2022 AND T2.[CardCode] = T0.[CardCode] and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)) as 'MAR',

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 4 AND YEAR(T2.DocDate) = 2022 AND T2.[CardCode] = T0.[CardCode] and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)) as 'APR',

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 5 AND YEAR(T2.DocDate) = 2022 AND T2.[CardCode] = T0.[CardCode] and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)) as 'MAY',

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 6 AND YEAR(T2.DocDate) = 2022 AND T2.[CardCode] = T0.[CardCode] and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)) as 'JUN',

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 7 AND YEAR(T2.DocDate) = 2022 AND T2.[CardCode] = T0.[CardCode] and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)) as 'JUL',

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 8 AND YEAR(T2.DocDate) = 2022 AND T2.[CardCode] = T0.[CardCode] and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)) as 'AUG',

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 9 AND YEAR(T2.DocDate) = 2022 AND T2.[CardCode] = T0.[CardCode] and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)) as 'SEP',

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 10 AND YEAR(T2.DocDate) = 2022 AND T2.[CardCode] = T0.[CardCode] and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)) as 'OCT',

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 11 AND YEAR(T2.DocDate) = 2022 AND T2.[CardCode] = T0.[CardCode] and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)) as 'NOV',

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 12 AND YEAR(T2.DocDate) = 2022 AND T2.[CardCode] = T0.[CardCode] and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)) as 'DEC',

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE T2.[CardCode] = t0.cardcode and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)) as 'TOTAL'

FROM OINV T0

INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode] and t0.CANCELED='n'

inner join crd1 t2 on t1.cardcode=t2.cardcode and t2.Address=t0.PayToCode and t2.AdresType='B'

left join inv12 t3 on t0.DocEntry=t3.DocEntry

where t0.CANCELED='N'

UNION

SELECT Distinct ' ',NULL,null,null,

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 1 AND YEAR(T2.DocDate) = 2022 and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)),

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 2 AND YEAR(T2.DocDate) = 2022 and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)),

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 3 AND YEAR(T2.DocDate) = 2022 and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)),

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 4 AND YEAR(T2.DocDate) = 2022 and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)),

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 5 AND YEAR(T2.DocDate) = 2022 and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)),

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 6 AND YEAR(T2.DocDate) = 2022 and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)),

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) =7 AND YEAR(T2.DocDate) = 2022 and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)),

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 8 AND YEAR(T2.DocDate) = 2022 and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)),

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 9 AND YEAR(T2.DocDate) = 2022 and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)),

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 10 AND YEAR(T2.DocDate) = 2022 and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)),

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 11 AND YEAR(T2.DocDate) = 2022 and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)),

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE MONTH(T2.DocDate) = 12 AND YEAR(T2.DocDate) = 2022 and t2.CANCELED='N' and T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0)),

(SELECT DISTINCT SUM(T2.DocTotal-T2.VatSum) FROM OINV T2 WHERE T2.DOCNUM NOT IN (SELECT DISTINCT T0.[BaseRef] FROM RIN1 T0))

FROM OINV T0 INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode] and t0.CANCELED='N'

INNER JOIN OCRG T2 ON T1.[GroupCode] = T2.[GroupCode]

inner join crd1 t4 on t1.cardcode=T0.cardcode and t4.Address=t0.PayToCode and t4.AdresType='B'

left join inv12 t3 on t0.DocEntry=t3.DocEntry

where t0.CANCELED='N'

)z where z.CardName <>''