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)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply