| Author |
 Topic  |
|
|
SergioM
Yak Posting Veteran
68 Posts |
 Posted - 07/25/2012 : 10:42:18
|
I'm trying to brainstorm on how to write the following code, but I'm stumped. So, while I'm not asking you to write it, I wonder how it can be written. I have a list of sales. Amongst other data, each row has a SKU & a date. I need to find the last 10 sales for each SKU.
So if my list looks like this:
OrderID SKU Date
25 CandyBar 5/12/2012
26 Chocolate 5/12/2012
27 CandyBar 5/13/2012
28 Gum 5/13/2012
29 CandyBar 5/13/2012
30 Chocolate 5/14/2012
31 CandyBar 5/14/2012
32 Gum 5/15/2012
33 Gum 5/15/2012
I have to make it look like this:
SKU 10th 9th 8th 7th
CandyBar 5/12/2012 5/13/2012 5/13/2012 5/14/2012
Chocolate 5/12/2012 5/14/2012
Gum 5/13/2012 5/15/2012 5/15/2012
Any thoughts on where to start, or how (using words or SQL) you might write it? |
Edited by - SergioM on 07/25/2012 10:49:55
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/25/2012 : 10:54:27
|
You can use a combination of row_number function and pivoting to do this. Since you asked only for psuedo code, this is REALLY pseudo (a.k.a. completely untested ;);WITH cte1 AS
(
SELECT
SKU,Date,
ROW_NUMBER() OVER(PARTITION BY sku ORDER BY Date DESC) AS RN
FROM
YourTable
)
SELECT
*
FROM
cte1
PIVOT( MAX(date) FOR RN IN ([1],[2],[3],[4],[5])) P
|
 |
|
|
SergioM
Yak Posting Veteran
68 Posts |
Posted - 07/25/2012 : 11:24:53
|
| Nice! Thanks! It doesn't work, but it's 13 lines of code. I don't think it should take too long to figure this out. |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 07/25/2012 : 11:49:45
|
| Welcome to set-based programing. Sometimes it takes a fraction of the lines of code that a procedural language would. Sometimes it takes many times the LOC that a procedural language would.. :) |
|
|
|
SergioM
Yak Posting Veteran
68 Posts |
Posted - 07/25/2012 : 14:29:37
|
Ok, I was wrong. It actually uses a few different commands I'm not familiar with. Little help?  |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/25/2012 : 14:54:39
|
You can copy the code below to a SSMS window and run it. I added comments explaining what I am trying to do.-- CREATE A TEST TABLE WITH THE COLUMNS DESCRIBED IN YOUR ORIGINAL POST
CREATE TABLE #tmp (OrderID INT, SKU VARCHAR(64), Date DATETIME);
-- INSERT THE SAMPLE DATA.
insert into #tmp values ('25','CandyBar','5/12/2012');
insert into #tmp values ('26','Chocolate','5/12/2012');
insert into #tmp values ('27','CandyBar','5/13/2012');
insert into #tmp values ('28','Gum','5/13/2012');
insert into #tmp values ('29','CandyBar','5/13/2012');
insert into #tmp values ('30','Chocolate','5/14/2012');
insert into #tmp values ('31','CandyBar','5/14/2012');
insert into #tmp values ('32','Gum','5/15/2012');
insert into #tmp values ('33','Gum','5/15/2012');
-- THE INNER QUERY WITHIN THE CTE ADDS A ROW NUMBER ORDERED
-- THE WAY WE WANT THE PIVOT COLUMNS TO BE ORDERED. YOU WILL
-- SEE THIS IF YOU RUN THIS QUERY
SELECT
SKU,Date,
ROW_NUMBER() OVER(PARTITION BY sku ORDER BY Date DESC) AS RN
FROM
#tmp
-- THE ACTUAL QUERY TAKES THE RESULTS OF THE CTE (THE INNER QUERY)
-- AND PIVOTS IT.
;WITH cte1 AS
(
SELECT
SKU,Date,
ROW_NUMBER() OVER(PARTITION BY sku ORDER BY Date DESC) AS RN
FROM
#tmp
)
SELECT
*
FROM
cte1
PIVOT( MAX(date) FOR RN IN ([1],[2],[3],[4],[5])) P
-- CLEAN UP
DROP TABLE #tmp; |
|
|
|
SergioM
Yak Posting Veteran
68 Posts |
Posted - 07/25/2012 : 15:51:38
|
Wow, thanks again!
I now realize that he error I was getting may be related to one server. I have two SQL 2008 servers. It works perfectly on one of them & on the other it gives me an error on line 39.
PIVOT( MAX(date) FOR RN IN ([1],[2],[3],[4],[5])) P
Msg 102, Level 15, State 1, Line 39
Incorrect syntax near '('.
I would have guessed that it is a database constraint, but this is all on a temp table. Both are SQL 2008. Do you know if there is a server-wide setting that I need to change?
Thanks again, this was extremely helpful. |
Edited by - SergioM on 07/25/2012 15:51:51 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/25/2012 : 16:58:55
|
Yes, check the Compatibility setting on the database.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/25/2012 : 17:11:07
|
DECLARE @Sample TABLE
(
OrderID INT NOT NULL,
SKU VARCHAR(64) NOT NULL,
[Date] DATETIME
);
INSERT @Sample
(
OrderID,
SKU,
[Date]
)
VALUES (25, 'CandyBar', '20120512'),
(26, 'Chocolate', '20120512'),
(27, 'CandyBar', '20120513'),
(28, 'Gum', '20120513'),
(29, 'CandyBar', '20120513'),
(30, 'Chocolate', '20120514'),
(31, 'CandyBar', '20120514'),
(32, 'Gum', '20120515'),
(33, 'Gum', '20120515');
-- Solution
SELECT p.SKU,
p.[1] AS [10th],
p.[2] AS [9th],
p.[3] AS [8th],
p.[4] AS [7th],
p.[5] AS [6th],
p.[6] AS [5th],
p.[7] AS [4th],
p. AS [3rd],
p.[9] AS [2nd],
p.[10] AS [1st]
FROM (
SELECT SKU,
[Date],
ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY OrderID) AS rn
FROM @Sample
) AS s
PIVOT (
MAX(s.[Date])
FOR s.rn IN ([1], [2], [3], [4], [5], [6], [7], , [9], [10])
) AS p
ORDER BY p.SKU
N 56°04'39.26"
E 12°55'05.63" |
|
|
| |
Topic |
|
|
|
Abstract Thinking & Psuedo code
