Adding OSLP To Query

[Former Member]

Good Afternoon,

How can i add OSLP table to the below?

SELECT q.[CC] as 'Customer Code', max(q.[CN]) as 'Customer Name', Sum(q.[TOT]) as 'Tot', Sum(q.[JAN]) as 'January', Sum(q.[FEB]) as 'February', Sum(q.[MAR]) as 'March', Sum(q.[APR]) as 'April', Sum(q.[MAY]) as 'May', Sum(q.[JUN]) as 'June', Sum(q.[JUL]) as 'July', Sum(q.[AUG]) as 'August', Sum(q.[SEP]) as 'September', Sum(q.[OCT]) as 'October', Sum(q.[NOV]) as 'November' , Sum(q.[DEC]) as 'December' FROM( SELECT T0.[CardCode], T0.[CardName], [Total] = case when T2.[QryGroup20] = 'y' and T1.[DocDate] between '2017/01/01' and '2017/12/31' then T1.[Quantity] else 0 end, [Jan] = case when T2.[QryGroup20] = 'y' and T1.[DocDate] between '2017/01/01' and '2017/01/31' then T1.[Quantity] else 0 end, [Feb] = case when T2.[QryGroup20] = 'y' and T1.[DocDate] between '2017/02/01' and '2017/02/28' then T1.[Quantity] else 0 end, [Mar] = case when T2.[QryGroup20] = 'y' and T1.[DocDate] between '2017/03/01' and '2017/03/31' then T1.[Quantity] else 0 end, [Apr] = case when T2.[QryGroup20] = 'y' and T1.[DocDate] between '2017/04/01' and '2017/04/30' then T1.[Quantity] else 0 end, [May] = case when T2.[QryGroup20] = 'y' and T1.[DocDate] between '2017/05/01' and '2017/05/31' then T1.[Quantity] else 0 end, [Jun] = case when T2.[QryGroup20] = 'y' and T1.[DocDate] between '2017/06/01' and '2017/06/30' then T1.[Quantity] else 0 end, [Jul] = case when T2.[QryGroup20] = 'y' and T1.[DocDate] between '2017/07/01' and '2017/07/31' then T1.[Quantity] else 0 end, [Aug] = case when T2.[QryGroup20] = 'y' and T1.[DocDate] between '2017/08/01' and '2017/08/31' then T1.[Quantity] else 0 end, [Sep] = case when T2.[QryGroup20] = 'y' and T1.[DocDate] between '2017/09/01' and '2017/09/30' then T1.[Quantity] else 0 end, [Oct] = case when T2.[QryGroup20] = 'y' and T1.[DocDate] between '2017/10/01' and '2017/10/31' then T1.[Quantity] else 0 end, [Nov] = case when T2.[QryGroup20] = 'y' and T1.[DocDate] between '2017/11/01' and '2017/11/30' then T1.[Quantity] else 0 end, [Dec] = case when T2.[QryGroup20] = 'y' and T1.[DocDate] between '2017/12/01' and '2017/12/31' then T1.[Quantity] else 0 end FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OCRD T4 on T0.CardCode = T4.CardCode INNER JOIN OITB T6 on T2.[ItmsGrpCod] = T6.[ItmsGrpCod] WHERE T0.[DocDate] >= CASE '[%0]' When '' Then '01.01.2000' Else '[%0]' End and T0.[DocDate] <= CASE '[%1]' When '' Then '01.01.2099' Else '[%1]' End AND T6.[ItmsGrpNam] >= CASE '[%2]' when '' then 'aaaaaa' Else '[%2]' End and T6.[ItmsGrpNam] <= CASE '[%3]' when '' Then 'ZZZZZZ' Else '[%3]' end AND (T0.CardCode= '[%4]' or '[%4]'='') and t2.itemcode BETWEEN '[%5]' AND '[%6]' --UNION ALL --SELECT --T0.[CardCode], T0.[CardName], ---[Total] = case when T2.[QryGroup40] = 'y' then T1.[LineTotal] *-1 when T2.[QryGroup36] = 'y' then T1.[LineTotal] *-1 when T2.[QryGroup38] = 'y' then T1.[LineTotal] *-1 --when T2.[QryGroup37] = 'y' then T1.[LineTotal] *-1 when T2.[QryGroup39] = 'y' then T1.[LineTotal] *-1 when T2.[QryGroup41] = 'y' then T1.[LineTotal] *-1 when T2.[QryGroup42] = 'y' then T1.[LineTotal] *-1 else 0 end, --[B/CW] = case when T2.[QryGroup40] = 'y' then T1.[LineTotal] *-1 else 0 end, --[P] = case when T2.[QryGroup36] = 'y' then T1.[LineTotal] *-1 else 0 end, --[WI] = case when T2.[QryGroup38] = 'y' then T1.[LineTotal] *-1 else 0 end, --[COI] = case when T2.[QryGroup37] = 'y' then T1.[LineTotal] *-1 else 0 end, --[SHARK & Gel] = case when T2.[QryGroup39] = 'y' then T1.[LineTotal] *-1 else 0 end, --[Marine] = case when T2.[QryGroup41] = 'y' then T1.[LineTotal] *-1 else 0 end, --[Other] = case when T2.[QryGroup42] = 'y' then T1.[LineTotal] *-1 else 0 end --FROM ORIN T0 --INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry --INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode --INNER JOIN OCRD T4 on T0.CardCode = T4.CardCode --INNER JOIN OITB T6 on T2.[ItmsGrpCod] = T6.[ItmsGrpCod] --WHERE T0.[DocDate] >= CASE '[%0]' When '' Then '01.01.2000' Else '[%0]' End and T0.[DocDate] <= CASE '[%1]' When '' Then '01.01.2099' Else '[%1]' End --AND T6.[ItmsGrpNam] >= CASE '[%2]' when '' then 'aaaaaa' Else '[%2]' End and T6.[ItmsGrpNam] <= CASE '[%3]' when '' Then 'ZZZZZZ' Else '[%3]' end --AND (T0.CardCode= '[%4]' or '[%4]'='') and t2.itemcode BETWEEN '[%5]' AND '[%6]' ) q (CC, CN, TOT, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC) GROUP BY CC