How to partition and add sequence/rownumber using CTE or Row_number()

  • Hi there,

    I have the following table struction, lets call it table A.

    bookidstartdate endate

    2001 2000-01-01 2000-01-05

    3001 2001-01-01 2001-01-02

    4001 2002-01-01 2002-01-04

    and i want the end result to be look like this in table B.

    bookidstartdate endate bookidrowdate bookidlogseqrowsequence

    2001 2000-01-012000-01-05 2000_01_01 2001_0 0

    2001 2000-01-012000-01-05 2000_02_01 2001_1 1

    2001 2000-01-012000-01-05 2000_03_01 2001_2 2

    2001 2000-01-012000-01-05 2000_04_01 2001_3 4

    3001 2002-02-01 2003-02-02 2000_01_01 3001_0 0

    3001 2002-02-01 2003-02-02 2000_02_01 3001_1 1

    4001 2002-01-01 2002-01-04 2002-01-01 4001_0 0

    4001 2002-01-01 2002-01-04 2002-02-01 4001_1 1

    4001 2002-01-01 2002-01-04 2002-02-01 4001_2 2

    The script below works but i have a break when datediff (days,startdate, endate) reaches 0. For every bookidm i want to iterate till the datediff is zero then move on to next bookid and do the sam thing.

    declare @orders table

    (

    bookid int,

    startdate date,

    endate date,

    rowsequence int

    )

    insert @orders (bookid, startdate, endate) values

    (2001,'2000-01-01','2000-01-05'),

    (3001,'2001-01-01','2001-01-02'),

    (4001,'2002-01-01','2002-01-04')

    select *, CAST(bookid as CHAR(4)) + '_' + CAST(rowsequence as char(2)) bookidlogseq

    ,CASE WHEN rowsequence<= DATEDIFF(DAY, startdate, endate) THEN ---THIS IS NOT WORKING!

    DATEADD(mm, rowsequence, CONVERT(VARCHAR(10),startdate,112))--have this avoid arithmetic overflow

    ELSE '1900-01-01'

    END AS bookidrowdate

    from (

    select bookid, startdate, endate,

    ROW_NUMBER() over(Partition by bookid order by startdate)-1 rowsequence

    from @orders

    ) ord

    any suggestions?

  • sqlbidev (7/6/2015)


    Hi there,

    I have the following table struction, lets call it table A.

    bookidstartdate endate

    2001 2000-01-01 2000-01-05

    3001 2001-01-01 2001-01-11

    4000 2002-01-01 2002-01-06

    and i want the end result to be look like this IN table B.

    bookidstartdate endate bookidrowdate bookidlogseqrowsequence

    2001 2000-01-012000-01-05 20000101_0 2001_0 0

    2001 2000-01-012000-01-05 20000101_1 2001_1 1

    2001 2000-01-012000-01-05 20000101_2 2001_2 2

    2001 2000-01-012000-01-05 20000101_3 2001_3 4

    3001 2002-02-02 2003-02-02 20020202_0 2001_0 0

    ...

    Why does your end result contain 4 rows for bookid 2001, only one row for bookid 3001, and no rows at all for bookid 4000?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (7/6/2015)


    sqlbidev (7/6/2015)


    Hi there,

    I have the following table struction, lets call it table A.

    bookidstartdate endate

    2001 2000-01-01 2000-01-05

    3001 2001-01-01 2001-01-11

    4000 2002-01-01 2002-01-06

    and i want the end result to be look like this IN table B.

    bookidstartdate endate bookidrowdate bookidlogseqrowsequence

    2001 2000-01-012000-01-05 20000101_0 2001_0 0

    2001 2000-01-012000-01-05 20000101_1 2001_1 1

    2001 2000-01-012000-01-05 20000101_2 2001_2 2

    2001 2000-01-012000-01-05 20000101_3 2001_3 4

    3001 2002-02-02 2003-02-02 20020202_0 2001_0 0

    ...

    Why does your end result contain 4 rows for bookid 2001, only one row for bookid 3001, and no rows at all for bookid 4000?

    I agree with Chris - the output you posted is confusing. I know this is a guess, but this this what you're looking for?

    WITH cte AS (

    SELECT bookid, startdate, endate, DATEDIFF(day, startdate, endate) diff,

    DATEADD(DAY, t.N, startdate) running_date,

    ROW_NUMBER() OVER(PARTITION BY bookid ORDER BY startdate) rn

    FROM @orders

    CROSS APPLY dbo.Tally1K t

    WHERE t.N BETWEEN 1 AND DATEDIFF(day, startdate, endate)

    )

    SELECT bookid, startdate, endate, running_date, rn

    FROM cte;

    If I'm reading your requirements correctly, you want to generate a table with one row for each day a book is checked out - between the start and end dates. You'll need a tally table to do this efficiently. If you aren't familiar with them yet, see the link in my signature for details. They're well worth the time to learn and will change the way you look at data.

  • My bad guys , I have now rectified the mistake and edited the post. T´thanks chris for pointing and ed, i will follow your lead. thanks guys, will get back asap.

  • sqlbidev (7/6/2015)


    Hi there,

    I have the following table struction, lets call it table A.

    bookidstartdate endate

    2001 2000-01-01 2000-01-05

    3001 2001-01-01 2001-01-11

    4000 2002-01-01 2002-01-06

    and i want the end result to be look like this IN table B.

    bookidstartdate endate bookidrowdate bookidlogseqrowsequence

    2001 2000-01-012000-01-05 2000_01_01 2001_0 0

    2001 2000-01-012000-01-05 2000_02_01 2001_1 1

    2001 2000-01-012000-01-05 2000_03_01 2001_2 2

    2001 2000-01-012000-01-05 2000_04_01 2001_3 4

    3001 2002-02-02 2003-02-02 2000_01_01 2001_0 0

    4001 2002-01-01 2002-01-04 2002-01-01 4001_0 1

    4001 2002-01-01 2002-01-04 2002-02-01 4001_2 2

    4001 2002-01-01 2002-01-04 2002-02-01 4001_3 3

    The script below works but i have a break when datediff (days,startdate, endate) reaches 0. For every bookidm i want to iterate till the datediff is zero then move on to next bookid and do the sam thing.

    declare @orders table

    (

    bookid int,

    startdate date,

    endate date,

    rowsequence int

    )

    insert @orders (bookid, startdate, endate) values

    (2001,'2000-01-01','2000-01-05'),

    (3001,'2001-01-01','2001-01-11'),

    (4000,'2002-01-01','2002-01-06')

    select *, CAST(bookid as CHAR(4)) + '_' + CAST(rowsequence as char(2)) bookidlogseq

    ,CASE WHEN rowsequence<= DATEDIFF(DAY, startdate, endate) THEN ---THIS IS NOT WORKING!

    DATEADD(mm, rowsequence, CONVERT(VARCHAR(10),startdate,112))--have this avoid arithmetic overflow

    ELSE '1900-01-01'

    END AS bookidrowdate

    from (

    select bookid, startdate, endate,

    ROW_NUMBER() over(Partition by bookid order by startdate)-1 rowsequence

    from @orders

    ) ord

    any suggestions?

    Why do you only have 1 row in the output for bookid 3001?

    Why has the startdate in the output for 3001 changed from the startdate in the source table?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I'm still wondering what the goal is. It looks like you already have your date window defined in your base table. It looks like you want to break it out into having 1 row for each book for each day, but why? What are you trying to do by breaking it out?

  • You got it right, i want to break down each row for everyone day, its a client request. The query and table is much bigger than this, i have just given a small example here. my query is able to break down by single orderid but i cant seems to break it down further with number of days (which i get it from datediff.)

  • sqlbidev (7/6/2015)


    You got it right, i want to break down each row for everyone day, its a client request. The query and table is much bigger than this, i have just given a small example here. my query is able to break down by single orderid but i cant seems to break it down further with number of days (which i get it from datediff.)

    So did the query I posted get you what you want?

  • sqlbidev (7/6/2015)


    You got it right, i want to break down each row for everyone day, its a client request. The query and table is much bigger than this, i have just given a small example here. my query is able to break down by single orderid but i cant seems to break it down further with number of days (which i get it from datediff.)

    Please adjust your required output set (the results you want from the three sample rows you have provided) until it's complete and correct. You shouldn't expect people to have to guess what you want because you're too lazy to type a few numbers.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi Ed, yes! oh yes! its working exactly what i was looking for. Thank you guys for your time and effort and ed, i a m reading the article you suggested.

  • sqlbidev (7/6/2015)


    Hi Ed, yes! oh yes! its working exactly what i was looking for. Thank you guys for your time and effort and ed, i a m reading the article you suggested.

    Glad I could help. Thanks for the feedback. Next time, a clear definition of how to get from what you have to what you want will go a long way toward getting your problem solved more quickly.

Viewing 11 posts - 1 through 10 (of 10 total)

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