Finished Pivot table should show all Customs (not only wich bought already)

[Former Member]

Hi,

this table should show basically all customers. At the moment it shows only customers which has already bought. What can I do? Thanks a lot!

Declare @ART NvarChar(20)  = /* SELECT FROM INV1 X2 WHERE X2.ItemCode =*/ '[%0]'  



SELECT  [Cardcode] as CardCode,[CustName] as CustName,[ZipCode], [PartName] as ItemNo, [1] as KW1, [2] as KW2, [3] as KW3, [4] as KW4, [5] as KW5, [6] as KW6, [7] as KW7, [8] as KW8, [9] as KW9, [10] as KW10, [11] as KW11, [12] as KW12, [13] as KW13, [14] as KW14, [15] as KW15, [16] as KW16, [17] as KW17, [18] as KW18, [19] as KW19, [20] as KW20, [21] as KW21, [22] as KW22, [23] as KW23, [24] as KW24, [25] as KW25, [26] as KW26, [27] as KW27, [28] as KW28, [29] as KW29, [30] as KW30 , [31] as KW31, [32] as KW32, [33] as KW33, [34] as KW34, [35] as KW35, [36] as KW36, [37] as KW37, [38] as KW38, [39] as KW39, [40] as KW40, [41] as KW41, [42] as KW42, [43] as KW43, [44] as KW44, [45] as KW45, [46] as KW46, [47] as KW47, [48] as KW48, [49] as KW49, [50] as KW50, [51] as KW51, [52] as KW52
from

( SELECT T0.[CardName] as CustName,T0.[Cardcode] as CardCode, T2.[ZipCode], T1.[ItemCode] as PartName, CONVERT(nvarchar(2), DATEPART(ISOWK, T1.ShipDate)) AS KW, CONVERT(decimal(19,2), sum(T1.Quantity * T1.NumPerMsr)) AS Tonnen



FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode



 WHERE T1.ItemCode =@ART  and T1.ShipDate >= '01.01.2016'


GROUP BY T0.CardName, T1.ShipDate, T1.Quantity, T1.ItemCode, T0.Cardcode, T2.ZipCode



union all



SELECT T0.[CardName] as CustName,T0.[Cardcode] as CardCode, T2.[ZipCode], T1.[ItemCode] as PartName, CONVERT(nvarchar(2), DATEPART(ISOWK, T1.ShipDate)) AS KW, CONVERT(decimal(19,2), -sum(T1.Quantity * T1.NumPerMsr)) AS Tonnen



FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode



 WHERE T1.ItemCode =@ART and T1.ShipDate >= '01.01.2016'


GROUP BY T0.CardName, T1.ShipDate, T1.Quantity, T1.ItemCode, T0.Cardcode, T2.ZipCode )S



Pivot



(Sum(Tonnen) FOR KW  IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12], [13], [14],[15],[16],[17],[18],[19],[20],[21], [22], [23], [24], [25], [26], [27], [28],[29], [30], [31], [32],[33],[34],[35],[36],[37],[38],[39],[40], [41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52] )) PP

[former_member207653]

It is difficult to answer without knowing the database schema.
Because there is an UNION clause, you can focus on the first or second SELECT statement for simplifying. How do you determine if a customer has already bought or not? Is it from the ShipDate? If yes, you can adjust the WHERE clause accordingly.

[Former Member]

Hi

this query compares invoices with deposits. If a customer hasn't already a invoice with the chosen article, the customer don't will be displayed.

I think I can solve it with a sub-query.Later I will try to fix it.