on 06-23-2016 12:10 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.