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 <>''