Row_Number In Action — a Sample Code


CREATE TABLE test (Person VARCHAR(20), Action VARCHAR(10), [Date] DATE)
INSERT INTO test
VALUES (‘MAX’,’Sale’,’20100720′),
(‘MAX’,’Sale’,’20101026′),
(‘MAX’,’Sale’,’20101103′),
(‘MAX’,’Sale’,’20110401′),
(‘MAX’,’No Sale’,’20110528′),
(‘MAX’,’Sale’,’20110613′),
(‘MAX’,’Sale’,’20110711′),
(‘MAX’,’Sale’,’20110819′),
(‘MAX’,’No Sale’,’20111001′),
(‘MAX’,’No Sale’,’20111209′),
(‘MAX’,’Sale’,’20120201′),
(‘MAX’,’No Sale’,’20120201′),
(‘MAX’,’Sale’,’20120207′),
(‘MAX’,’Sale’,’20120213′),
(‘MAX’,’No Sale’,’20130706′);
;with mycte as
(
select *, row_number() Over(order By [Date],Action Desc) rn1
, row_number() Over(partition by Action order By Date, aCTION desc) rn2
, row_number() Over(order By [Date],Action DESC ) – row_number() Over(Partition by Action Order By [Date] ) rn3
from test)
, mycte2 as
( Select *,row_number() Over(partition by rn3 order By CASE WHEN Action=’Sale’Then [Date] END, CASE WHEN Action=’No Sale’Then [Date] END DESC) rn4
, row_number() Over(partition by Action, rn3 order By Action ) rn5
from mycte )
SELECT Person,Action,[Date],’Y’ AS flag
FROM mycte2
WHERE rn4 = 1 OR rn5 = 1
ORDER BY [Date], ACTION DESC

DROP TABLE test

/*
Person Action Date flag
MAX Sale 2010-07-20 Y
MAX No Sale 2011-05-28 Y
MAX Sale 2011-06-13 Y
MAX No Sale 2011-12-09 Y
MAX Sale 2012-02-01 Y
MAX No Sale 2012-02-01 Y
MAX Sale 2012-02-07 Y
MAX No Sale 2013-07-06 Y

*/

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/70d0a54f-a7c1-42c7-afe0-300ecd6d57bd/sql-query?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s