I've tried to port this from a similar post, Return a column per date in a range however I haven't been able to quite get it to do what I need... Please find some example code below :
What i'm after is summing the QTYOrdered between two dates looking back over a 6 week period. I'm not quite able to get this to work and need help! ;) I'm sure @Bluefeet would be able to sort this out in a min!
DROP TABLE #Sales
DROP TABLE #TempDates
CREATE TABLE #Sales
(
ID INT ,
ItemID NVARCHAR(6) ,
[Description] NVARCHAR(10) ,
QTYOrdered NUMERIC ,
ReceiptDate DATETIME
)
INSERT INTO #Sales
( ID ,
ItemID ,
[Description] ,
QTYOrdered ,
ReceiptDate
)
VALUES ( 1 , -- ID - int
N'AAL100' , -- ItemID - nvarchar(6) ,
'Ice Cream' , -- Description -- nvarchar(10) ,
21 , -- QTYOrdered - numeric
DATEADD(WEEK, -1, GETDATE()) -- ReceiptDate - datetime
)
INSERT INTO #Sales
( ID ,
ItemID ,
[Description] ,
QTYOrdered ,
ReceiptDate
)
VALUES ( 2 , -- ID - int
N'AAL100' , -- ItemID - nvarchar(6)
'Ice Cream' , -- Description -- nvarchar(10) ,
40 , -- QTYOrdered - numeric
DATEADD(WEEK, -1, GETDATE()) -- ReceiptDate - datetime
)
INSERT INTO #Sales
( ID ,
ItemID ,
[Description] ,
QTYOrdered ,
ReceiptDate
)
VALUES ( 3 , -- ID - int
N'AAL100' , -- ItemID - nvarchar(6)
'Ice Cream' , -- Description -- nvarchar(10) ,
50 , -- QTYOrdered - numeric
DATEADD(WEEK, -3, GETDATE()) -- ReceiptDate - datetime
)
INSERT INTO #Sales
( ID ,
ItemID ,
[Description] ,
QTYOrdered ,
ReceiptDate
)
VALUES ( 4 , -- ID - int
N'AAL101' , -- ItemID - nvarchar(6)
'Burger' , -- Description -- nvarchar(10) ,
43 , -- QTYOrdered - numeric
DATEADD(WEEK, -3, GETDATE()) -- ReceiptDate - datetime
)
INSERT INTO #Sales
( ID ,
ItemID ,
[Description] ,
QTYOrdered ,
ReceiptDate
)
VALUES ( 5 , -- ID - int
N'AAL101' , -- ItemID - nvarchar(6)
'Burger' , -- Description -- nvarchar(10) ,
42 , -- QTYOrdered - numeric
DATEADD(WEEK, -5, GETDATE()) -- ReceiptDate - datetime
)
INSERT INTO #Sales
( ID ,
ItemID ,
[Description] ,
QTYOrdered ,
ReceiptDate
)
VALUES ( 6 , -- ID - int
N'AAL101' , -- ItemID - nvarchar(6)
'Burger' , -- Description -- nvarchar(10) ,
76 , -- QTYOrdered - numeric
DATEADD(WEEK, -5, GETDATE()) -- ReceiptDate - datetime
)
INSERT INTO #Sales
( ID ,
ItemID ,
[Description] ,
QTYOrdered ,
ReceiptDate
)
VALUES ( 7 , -- ID - int
N'AAL102' , -- ItemID - nvarchar(6)
'Pizza' , -- Description -- nvarchar(10) ,
65 , -- QTYOrdered - numeric
DATEADD(WEEK, -7, GETDATE()) -- ReceiptDate - datetime
)
INSERT INTO #Sales
( ID ,
ItemID ,
[Description] ,
QTYOrdered ,
ReceiptDate
)
VALUES ( 8 , -- ID - int
N'AAL102' , -- ItemID - nvarchar(6)
'Pizza' , -- Description -- nvarchar(10) ,
34 , -- QTYOrdered - numeric
DATEADD(WEEK, -7, GETDATE()) -- ReceiptDate - datetime
)
INSERT INTO #Sales
( ID ,
ItemID ,
[Description] ,
QTYOrdered ,
ReceiptDate
)
VALUES ( 9 , -- ID - int
N'AAL102' , -- ItemID - nvarchar(6)
'Pizza' , -- Description -- nvarchar(10) ,
56 , -- QTYOrdered - numeric
DATEADD(WEEK, -9, GETDATE()) -- ReceiptDate - datetime
)
INSERT INTO #Sales
( ID ,
ItemID ,
[Description] ,
QTYOrdered ,
ReceiptDate
)
VALUES ( 10 , -- ID - int
N'AAL103' , -- ItemID - nvarchar(6)
'Burrito' , -- Description -- nvarchar(10) ,
75 , -- QTYOrdered - numeric
DATEADD(WEEK, -9, GETDATE()) -- ReceiptDate - datetime
)
SELECT *
FROM #Sales
DECLARE @Cols AS NVARCHAR(MAX) ,
@Query AS NVARCHAR(MAX);
WITH CTE ( DateList, MaxDate )
AS ( SELECT DATEADD(WEEK, -6, CONVERT(DATE, GETDATE())) AS DateList ,
MAX(ReceiptDate) MaxDate
FROM #Sales
UNION ALL
SELECT DATEADD(WEEK, 1, DateList) ,
MaxDate
FROM CTE
WHERE CTE.DateList < CTE.MaxDate
)
SELECT C.DateList
INTO #TempDates
FROM CTE C
SELECT @Cols = STUFF((SELECT DISTINCT
',' + QUOTENAME(CONVERT(CHAR(10), DateList, 120))
FROM #TempDates
FOR XML PATH('') ,
TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SET @Query = 'SELECT
ITEMID ,
[Description] ,
' + @Cols
+ ' FROM
( SELECT
ITEMID ,
[Description] ,
QTYOrdered ,
CONVERT(CHAR(10), DateList, 120) PivotDate
FROM #TempDates D
INNER JOIN #Sales S ON ReceiptDate BETWEEN DateList and DATEADD(DAY, 6, DateList)
) X
PIVOT
(
SUM(QTYOrdered)
FOR PivotDate in (' + @Cols + ')
) P '
PRINT @Query
EXECUTE(@query)
Example desired output :
ID ITEMID Description 2014-09-15 2014-09-22 2014-09-29 2014-10-06 2014-10-13 2014-10-20
1 AAL100 Ice Cream 0 0 0 0 50 0
2 AAL101 Burger 0 0 0 118 43 0
3 AAL102 Pizza 0 56 34 0 99 0
4 AAL103 Burrito 0 75 0 0 0 0
Excuse formatting... I don't know how to format text in the little editor window...
@bluefeet your fiddle seems close however when I run against my actual data i'm getting multiple lines and some of the summed qty's don't seem to be correct...
PIVOT
the data in your query? Can't you let SSRS do this by using a matrix? – mheptinstall Oct 27 '14 at 12:12