Abstract Thinking & Psuedo code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Abstract Thinking & Psuedo code

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2012-07-25 : 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?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-25 : 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
Posting Yak Master

170 Posts

Posted - 2012-07-25 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-25 : 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
Posting Yak Master

170 Posts

Posted - 2012-07-25 : 14:29:37

Ok, I was wrong. It actually uses a few different commands I'm not familiar with. Little help?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-25 : 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
Posting Yak Master

170 Posts

Posted - 2012-07-25 : 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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-25 : 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

30421 Posts

Posted - 2012-07-25 : 17:11:07

[code]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[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources