Post reply

Introduce rows for ROW_NUMBER Solution

  • August 5, 2017 at 8:15 am

    #339212

    Hi All,
    Thanks for any  help I may receive as this is complex for me and am really struggle a best way to acheive this.
    I am trying to get my data to a stage where i can do a one to one match for a row_number over stage.
    I have two tables .

    Table ATable B
    USERIDSTARTDATEEND DATEREFUSERIDSTARTDATEENDDATETITLEID
    15101/01/200501/01/2006216815101/01/200501/01/2006FPA1000
    15117/10/200715102/01/200615/10/2007ASA1250
    15116/10/2007BTA2500
    16110/10/201515/12/201516110/10/201516/12/2015RTA2000
    16118/05/201616119/12/201512/05/2016RTB1000
    16116/05/2016BHA1000

    If you look at USERID 151  it has in Table A it has the first row which can match with the first row from Table B. However the second row in Table A is a a match with the third row in Table B. So I would like to introduce a row in Table A based on the start date of the preceding row and the end in Table B on row 2. This has been made difficult by the fact that dates are not always match in both tables. The only items matching is the USERID and order of the dates. The stage I am trying to get the data to is shown below witht he ultimate goal of a one to one match.

    Table ATable B
    USERIDSTARTDATEEND DATEREFUSERIDSTARTDATEENDDATETITLEID
    15101/01/200501/01/2006216815101/01/200501/01/2006FPA1000
    15101/01/200515/10/2007216815102/01/200615/10/2007ASA1250
    15117/10/2007340015116/10/2007BTA2500
    16110/10/201515/12/2015312016110/10/201516/12/2015RTA2000
    16110/10/201512/05/2016312016119/12/201512/05/2016RTB1000
    16118/05/2016620016116/05/2016BHA1000

    I'm not even sure if this is possible in one sweep. Thanks for any help.

  • August 8, 2017 at 7:08 am

    #1954457

    Hi,
    I know this scenario I have is quite a messy situation. I have managed to get my data to a better stage but have encountered what I am hoping is less of a complex situation.
    I now have the data as below:-

  • USERID StartDateEndDateRef
    216825-Nov-0426-Apr-061005909
    216827-Apr-0610-Jul-06NULL
    216811-Jul-0625-Aug-06NULL
    216816-Apr-0722-Nov-071005909
    216816-May-0802-Jan-101005909
    216817-Jun-1608-Dec-161012729
    216809-Dec-1602-Jan-17NULL
    216820-Feb-1708-Jun-171012729
    216809-Jun-17NULLNULL
    218827-Apr-0610-Jul-06NULL
    218811-Jul-0625-Aug-06NULL
    218816-Apr-0722-Nov-071005909
    218816-May-0802-Jan-101005909
    218817-Jun-1608-Dec-161012729
    218809-Dec-1602-Jan-17NULL
    218820-Feb-1708-Jun-171012729
    218809-Jun-17NULLNULL

    • I am trying to update the Ref column with a value from the previous row. If the previous row is NULL then it should go up another row and retrieve that value.
    Wonder if anyone knows of a way to achieve this. Again thanks in advance.

  • August 8, 2017 at 7:43 am

    #1954465

    Johnny D - Tuesday, August 8, 2017 7:08 AM

    Hi,
    I know this scenario I have is quite a messy situation. I have managed to get my data to a better stage but have encountered what I am hoping is less of a complex situation.
    I now have the data as below:-

  • USERID StartDateEndDateRef
    216825-Nov-0426-Apr-061005909
    216827-Apr-0610-Jul-06NULL
    216811-Jul-0625-Aug-06NULL
    216816-Apr-0722-Nov-071005909
    216816-May-0802-Jan-101005909
    216817-Jun-1608-Dec-161012729
    216809-Dec-1602-Jan-17NULL
    216820-Feb-1708-Jun-171012729
    216809-Jun-17NULLNULL
    218827-Apr-0610-Jul-06NULL
    218811-Jul-0625-Aug-06NULL
    218816-Apr-0722-Nov-071005909
    218816-May-0802-Jan-101005909
    218817-Jun-1608-Dec-161012729
    218809-Dec-1602-Jan-17NULL
    218820-Feb-1708-Jun-171012729
    218809-Jun-17NULLNULL

    • I am trying to update the Ref column with a value from the previous row. If the previous row is NULL then it should go up another row and retrieve that value.
    Wonder if anyone knows of a way to achieve this. Again thanks in advance.

    Try an APPLY block with max date.

    “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

  • August 8, 2017 at 9:21 am

    #1954488

    Hi ChrisM,
    I have never used APPLY but I am not sure how this would solve the issue. After reading about APPLY it seems more like a inner join which would not give the back fill I am trying to achieve. Apologies if I have misunderstood how this is best used.

  • August 8, 2017 at 9:24 am

    #1954491

    CREATE TABLE

    Johnny D - Tuesday, August 8, 2017 9:21 AM

    Hi ChrisM,
    I have never used APPLY but I am not sure how this would solve the issue. After reading about APPLY it seems more like a inner join which would not give the back fill I am trying to achieve. Apologies if I have misunderstood how this is best used.

    Tell you what - knock up a sample data set and someone here will show you how it's done 😉
    CREATE TABLE ...
    followed by
    INSERT INTO ...

    “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

  • August 8, 2017 at 9:33 am

    #1954496

    a helping hand 😀  (courtesy of http://www.convertcsv.com/csv-to-sql.htm  ...simple cut and paste !)

    CREATE TABLE #yourdata(
     USERID  INTEGER NOT NULL
    ,StartDate DATETIME NOT NULL
    ,EndDate DATETIME
    ,Ref   INTEGER
    );
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'25-Nov-04','26-Apr-06',1005909);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'27-Apr-06','10-Jul-06',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'11-Jul-06','25-Aug-06',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'16-Apr-07','22-Nov-07',1005909);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'16-May-08','02-Jan-10',1005909);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'17-Jun-16','08-Dec-16',1012729);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'09-Dec-16','02-Jan-17',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'20-Feb-17','08-Jun-17',1012729);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'09-Jun-17',NULL,NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'27-Apr-06','10-Jul-06',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'11-Jul-06','25-Aug-06',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'16-Apr-07','22-Nov-07',1005909);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'16-May-08','02-Jan-10',1005909);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'17-Jun-16','08-Dec-16',1012729);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'09-Dec-16','02-Jan-17',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'20-Feb-17','08-Jun-17',1012729);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'09-Jun-17',NULL,NULL);

    SELECT * FROM #yourdata

    DROP TABLE #yourdata

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • August 8, 2017 at 9:44 am

    #1954500

    J Livingston SQL - Tuesday, August 8, 2017 9:33 AM

    a helping hand 😀  (courtesy of http://www.convertcsv.com/csv-to-sql.htm  ...simple cut and paste !)

    CREATE TABLE #yourdata(
     USERID  INTEGER NOT NULL
    ,StartDate DATETIME NOT NULL
    ,EndDate DATETIME
    ,Ref   INTEGER
    );
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'25-Nov-04','26-Apr-06',1005909);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'27-Apr-06','10-Jul-06',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'11-Jul-06','25-Aug-06',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'16-Apr-07','22-Nov-07',1005909);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'16-May-08','02-Jan-10',1005909);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'17-Jun-16','08-Dec-16',1012729);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'09-Dec-16','02-Jan-17',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'20-Feb-17','08-Jun-17',1012729);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'09-Jun-17',NULL,NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'27-Apr-06','10-Jul-06',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'11-Jul-06','25-Aug-06',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'16-Apr-07','22-Nov-07',1005909);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'16-May-08','02-Jan-10',1005909);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'17-Jun-16','08-Dec-16',1012729);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'09-Dec-16','02-Jan-17',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'20-Feb-17','08-Jun-17',1012729);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2188,'09-Jun-17',NULL,NULL);

    SELECT * FROM #yourdata

    DROP TABLE #yourdata

    Hahaha thanks G!

    SELECT *

    FROM #yourdata y

    OUTER APPLY (

    SELECT Ref = MAX(Ref)

    FROM #yourdata yi

    WHERE yi.USERID = y.USERID

    AND yi.StartDate < y.StartDate

    AND y.Ref IS NULL

    ) x

    ORDER BY USERID, StartDate

    “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

  • August 9, 2017 at 2:04 am

    #1954561

    That seems to work ChrisM. Thanks. 
    Thanks J Livingstone too.
    Just one other question. How do I get the original non NULL values to appear in the new column too along with the new values.
    I'm still trying to understand how this is actually working as I've never come across APPLY until now.
    Thanks

  • August 9, 2017 at 3:17 am

    #1954565

    just a word of caution here....the solution is using MAX(ref) ...which works for your data as posted....but consider the following data. This does not deliver the "previous row"

    CREATE TABLE #yourdata(
     USERID  INTEGER NOT NULL
    ,StartDate DATETIME NOT NULL
    ,EndDate DATETIME
    ,Ref   INTEGER
    );
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'25-Nov-04','26-Apr-06',200);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'27-Apr-06','10-Jul-06',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'11-Jul-06','25-Aug-06',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'16-Apr-07','22-Nov-07',10);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'16-May-08','02-Jan-10',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'17-Jun-16','08-Dec-16',5);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'09-Dec-16','02-Jan-17',NULL);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'20-Feb-17','08-Jun-17',30);
    INSERT INTO #yourdata(USERID,StartDate,EndDate,Ref) VALUES (2168,'09-Jun-17',NULL,NULL);

    SELECT userid,
       StartDate,
       EndDate,
       y.ref,
       COALESCE(y.ref, x.ref) new_ref
    FROM #yourdata y
      OUTER APPLY
    (
      SELECT Ref = MAX(Ref)
      FROM #yourdata yi
      WHERE yi.USERID = y.USERID
        AND yi.StartDate < y.StartDate
        AND y.Ref IS NULL
    ) x
    ORDER BY USERID, StartDate;

    DROP TABLE #yourdata

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • August 9, 2017 at 5:19 am

    #1954581

    Thanks J Livingstone. 

    You sure are a SQL Champion. I had a feeble effort myself using CASE and had some strange results.

  • August 9, 2017 at 8:41 am

    #1954648

    Johnny D - Wednesday, August 9, 2017 5:19 AM

    Thanks J Livingstone. 

    You sure are a SQL Champion. I had a feeble effort myself using CASE and had some strange results.

    I can assure you that I am far far from being any sort of SQL Champion !!!
    did you run the example code I posted (which has changes to show where you may not get what you expect)  ??

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

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

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