Please help with this query. This is very challenging.
-
I have the following data in my single table.
create table dbo.test_table (part varchar(5), stat varchar(2), datevalue datetime)
insert into dbo.test_table
select 'AAA01', 'ZZ', '2012-01-01' union all
select 'AAA02', 'AA', '2012-01-02' union all
select 'AAA03', 'BB', '2012-01-03' union all
select 'AAA01', 'CC', '2012-01-04' union all
select 'AAA03', 'ZZ', '2012-01-05' union all
select 'AAA02', 'AA', '2012-01-06' union all
select 'AAA02', 'CC', '2012-01-07' union all
select 'AAA03', 'AA', '2012-01-08' union all
select 'AAA01', 'ZZ', '2012-01-09' union all
select 'AAA01', 'AA', '2012-01-10' union all
select 'AAA02', 'ZZ', '2012-01-11' union all
select 'AAA01', 'AA', '2012-01-12' union all
select 'AAA02', 'BB', '2012-01-13' union all
select 'AAA01', 'AA', '2012-01-14'
The Datevalue is always increasing. The result that I am looking after is that, when a part value has stat = ZZ, it should return the next available row that stat is in ('AA', 'BB', 'CC') for that part value.
I would like it to return the following result:
'AAA01', 'ZZ', '2012-01-01'
'AAA01', 'CC', '2012-01-04'
'AAA03', 'ZZ', '2012-01-05'
'AAA03', 'AA', '2012-01-08'
'AAA01', 'ZZ', '2012-01-09'
'AAA01', 'AA', '2012-01-10'
'AAA02', 'ZZ', '2012-01-11'
'AAA02', 'BB', '2012-01-13'
Or it can return the following result
'AAA01', 'ZZ', '2012-01-01', 'CC', '2012-01-04'
'AAA03', 'ZZ', '2012-01-05', 'AA', '2012-01-08'
'AAA01', 'ZZ', '2012-01-09', 'AA', '2012-01-10'
'AAA02', 'ZZ', '2012-01-11', 'BB', '2012-01-13'
Or it can just return the following result:
'AAA01', 'CC', '2012-01-04'
'AAA03', 'AA', '2012-01-08'
'AAA01', 'AA', '2012-01-10'
'AAA02', 'BB', '2012-01-13'
Basically, I just need the first row that the stat is equal to 'AA' or 'BB' or 'CC' for the same part value. There is also a rowid column in my table, it is a unique key that always increment by one, not sure if that is useful with this query or not.
Thanks a lot!
-
WITH CTE AS (SELECT part, stat, datevalue,
ROW_NUMBER() OVER(PARTITION BY part ORDER BY datevalue) AS rn
FROM dbo.test_table)
SELECT z.part,z.stat,z.datevalue,n.stat,n.datevalue
FROM CTE z
INNER JOIN CTE n ON n.part=z.part
AND n.rn=z.rn+1
AND n.stat IN ('AA','BB','CC')
WHERE z.stat='ZZ'
ORDER BY z.datevalue;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark, you are AWESOME!!
Thank you very much for your quick reply.
-
Mark-101232 (2/8/2012)
WITH CTE AS (SELECT part, stat, datevalue,
ROW_NUMBER() OVER(PARTITION BY part ORDER BY datevalue) AS rn
FROM dbo.test_table)
SELECT z.part,z.stat,z.datevalue,n.stat,n.datevalue
FROM CTE z
INNER JOIN CTE n ON n.part=z.part
AND n.rn=z.rn+1
AND n.stat IN ('AA','BB','CC')
WHERE z.stat='ZZ'
ORDER BY z.datevalue;
Oh, be careful, Mark. You might not want to put something like that into production. Every time you have another instance of the CTE in your query, the CTE gets re-exeuted (just like views do). For small stuff like this, it might be ok but for larger stuff, consider dumping the CTE into a Temp Table with SELECT/INTO (which will be minimally logged, BTW) and then join twice on the temp table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply