Post reply

date fillers - dates in two rows

  • August 30, 2017 at 4:00 pm

    #345900

    Hello All,
    I need a very efficient way to duplicate rows based on dates in rows. table structure is something as follows
    CREATE TABLE #OutputResultSet                                                                    
       (
        value     INT
        ,dataitemuniverseid INT
        ,startdate    DATETIME
        ,Pubsourceid VARCHAR(50)
        ,PubsourceName Varchar(50)
       )

    data in the table is as follows
    0    32833    2017-08-07 11:00:00.000    147942    EL
    0    32835    2017-08-08 11:00:00.000    147942    EL
    230    32835    2017-08-11 14:00:00.000    147942    EL
    230    32833    2017-08-12 14:00:00.000    147942    EL
    158    32832    2016-08-01 23:00:00.000    280159    ENT
    0    32832    2016-08-03 11:00:00.000    280159    ENT
    158    32832    2016-08-05 15:00:00.000    280159    ENT
    0    32830    2016-08-07 23:00:00.000    280159    ENT
    158    32830    2016-08-09 23:00:00.000    280159    ENT

    desired output should be
    0    32833    2017-08-07 11:00:00.000    147942    EL
    0    32833    2017-08-08 11:00:00.000    147942    EL
    0    32835    2017-08-09 11:00:00.000    147942    EL
    0    32835    2017-08-10 11:00:00.000    147942    EL
    230    32835    2017-08-11 14:00:00.000    147942    EL
    230    32833    2017-08-12 14:00:00.000    147942    EL
    158    32832    2016-08-01 23:00:00.000    280159    ENT
    158    32832    2016-08-02 23:00:00.000    280159    ENT
    0    32832    2016-08-03 11:00:00.000    280159    ENT
    0    32832    2016-08-04 11:00:00.000    280159    ENT
    158    32832    2016-08-05 15:00:00.000    280159    ENT
    158    32832    2016-08-06 15:00:00.000    280159    ENT
    0    32830    2016-08-07 23:00:00.000    280159    ENT
    0    32830    2016-08-08 23:00:00.000    280159    ENT
    158    32830    2016-08-09 23:00:00.000    280159    ENT

    PLEASE notice filler rows. if date is missing, copy previous row with day + 1

    I am super stuck because of this. please help. I need very efficient solution as the date ranges across many months.

  • August 31, 2017 at 7:02 am

    #1957638

    Take a close look at your expected output for August 8th, and then tell me how and why it's supposed to be that way, considering the values you use for August 9th.

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Rent Servers for Income (picks and shovels strategy)

  • August 31, 2017 at 7:50 am

    #1957655

    technically, if Aug 8 is missing, keep using the value of previous day i.e. Aug 7, if no value in 7, use value of Aug 6 and so on.
    So for eq. pubsourcename = EL i want to repeat these duplication process for all startdate.
    then for next pubSourceName and then for the next.

  • August 31, 2017 at 8:15 am

    #1957664

    ekant_alone - Thursday, August 31, 2017 7:50 AM

    technically, if Aug 8 is missing, keep using the value of previous day i.e. Aug 7, if no value in 7, use value of Aug 6 and so on.
    So for eq. pubsourcename = EL i want to repeat these duplication process for all startdate.
    then for next pubSourceName and then for the next.

    That part I get, but look at your data for August 8th.  There IS a record for that date, and it's values did NOT get preserved in your expected output.   Please clarify if that was just a typo, and if not, what rule one would follow to derive that change.

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Rent Servers for Income (picks and shovels strategy)

  • August 31, 2017 at 8:38 am

    #1957670

    that is just a typo. Please excuse me for that.

  • August 31, 2017 at 8:40 am

    #1957671

    0 32833 2017-08-07 11:00:00.000 147942 EL
    0 32835 2017-08-08 11:00:00.000 147942 EL
    230 32835 2017-08-11 14:00:00.000 147942 EL

    it should be
    0 32833 2017-08-07 11:00:00.000 147942 EL
    0 32835 2017-08-08 11:00:00.000 147942 EL
    0 32835 2017-08-09 11:00:00.000 147942 EL
    0 32835 2017-08-10 11:00:00.000 147942 EL
    230 32835 2017-08-11 14:00:00.000 147942 EL

  • August 31, 2017 at 10:02 am

    #1957694

    First, sample data should be provided as an INSERT statement into your sample table.

    The solution below uses a tally table.  There is a blazing fast inline table-valued function that takes a value and produces that many rows, but I haven't included that here, because not everyone can create UDFs.  Instead, I'm using a table variable for the tally table.

    Tally table setup.
    DECLARE @Tally TABLE( n INT)

    /* This uses a zero-based Tally table instead of the typical one-based tally. */
    INSERT @Tally(n)
    VALUES (0), (1), (2), (3), (4), (5)

    Sample data (the correct way)
    CREATE TABLE #OutputResultSet 
    ( 
    value INT
    ,dataitemuniverseid INT
    ,startdate DATETIME 
    ,Pubsourceid VARCHAR(50)
    ,PubsourceName Varchar(50)
    ) 

    INSERT #OutputResultSet(value, dataitemuniverseid, startdate, Pubsourceid, PubsourceName)
    VALUES
    (0, 32833, '2017-08-07 11:00:00.000', 147942, 'EL'),
    (0, 32835, '2017-08-08 11:00:00.000', 147942, 'EL'),
    (230, 32835, '2017-08-11 14:00:00.000', 147942, 'EL'),
    (230, 32833, '2017-08-12 14:00:00.000', 147942, 'EL'),
    (158, 32832, '2016-08-01 23:00:00.000', 280159, 'ENT'),
    (0, 32832, '2016-08-03 11:00:00.000', 280159, 'ENT'),
    (158, 32832, '2016-08-05 15:00:00.000', 280159, 'ENT'),
    (0, 32830, '2016-08-07 23:00:00.000', 280159, 'ENT'),
    (158, 32830, '2016-08-09 23:00:00.000', 280159, 'ENT')

    The solution
    ;
    WITH PubDateRanges AS
    (
        SELECT *, DATEDIFF(DAY, startdate, LEAD(startdate, 1, DATEADD(DAY, 1, startdate)) OVER(PARTITION BY PubSourceID ORDER BY startdate)) - 1 as missingdays
        FROM #OutputResultSet
    )
    SELECT value, dataitemuniverseid, DATEADD(DAY, n, startdate) AS startdate, Pubsourceid, PubsourceName
    FROM PubDateRanges
    INNER JOIN @Tally
        ON missingdays >= n
    ORDER BY Pubsourceid, startdate

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 31, 2017 at 11:50 am

    #1957709

    thanks a ton Drew, however i am still having trouble 

    see there is a gap  between 2016-08-05 rand 2017-01-10. how can i fix this?  Can you please help?

  • August 31, 2017 at 11:56 am

    #1957711

    Hello Drew, i used the below mentioned SQL to get pass through the above issue., does it make sense to you?
    THANK YOU for helping me here. i was stuck since last 2 days on this. 

    declare @n int
    set @n = ( select DATEDIFF(hh, @StartDate, @EndDate))
    declare @cntr int = 0

    DECLARE @Tally TABLE( n INT)
    /* This uses a zero-based Tally table instead of the typical one-based tally. */

    while @cntr < @n
    begin
    INSERT @Tally(n)
    select @cntr

    set @cntr = @cntr + 1
    end

  • August 31, 2017 at 12:48 pm

    #1957723

    Hello Drew, i used the below mentioned SQL to get pass through the above issue., does it make sense to you? 
    THANK YOU for helping me here. i was stuck since last 2 days on this. 

    declare @n int
    set @n = ( select DATEDIFF(hh, @StartDate, @EndDate))
    declare @cntr int = 0

    DECLARE @Tally TABLE( n INT)
    /* This uses a zero-based Tally table instead of the typical one-based tally. */

    while @cntr < @n
    begin
    INSERT @Tally(n)
    select @cntr

    set @cntr = @cntr + 1
    end

    Loops are horribly slow.  It is much better to use a set-based approach.  You can modify the below by adding additional records to the VALUES clause and/or additional CROSS JOINs. 

    DECLARE @Tally TABLE( n INT)

    /* This uses a zero-based Tally table instead of the typical one-based tally. */
    ; WITH CTE AS
    (
        SELECT n
        FROM (VALUES (1), (1), (1), (1), (1), (1)) t(n)
    )
    INSERT @Tally(n)
    SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) - 1
    FROM CTE AS a
    CROSS JOIN CTE AS b
    CROSS JOIN CTE AS c

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 31, 2017 at 3:03 pm

    #1957749

    ekant_alone - Thursday, August 31, 2017 11:56 AM

    Hello Drew, i used the below mentioned SQL to get pass through the above issue., does it make sense to you?
    THANK YOU for helping me here. i was stuck since last 2 days on this. 

    declare @n int
    set @n = ( select DATEDIFF(hh, @StartDate, @EndDate))
    declare @cntr int = 0

    DECLARE @Tally TABLE( n INT)
    /* This uses a zero-based Tally table instead of the typical one-based tally. */

    while @cntr < @n
    begin
    INSERT @Tally(n)
    select @cntr

    set @cntr = @cntr + 1
    end

    The only way you'll stop writing loops is if you just up and stop writing them.  😉  Are you allowed to use inline Table Valued Functions in your databases?

    --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

  • August 31, 2017 at 3:09 pm

    #1957751

    Hello Drew
    cant thank you enough,
    I just met another issue. 

    SELECT value, dataitemuniverseid, DATEADD(DAY, n, startdate) AS startdate, Pubsourceid, PubsourceName
    FROM PubDateRanges
    INNER JOIN @Tally
      ON missingdays >= n
    ORDER BY Pubsourceid, startdate

    I have a dataitemuniverseid where all i have are 2 values. the missingdays value is > 367 days. It is skipping the dates. 
    as shown in the picture below

  • August 31, 2017 at 3:11 pm

    #1957753

    Also change in requirement - i want hourly increment and not daily increment. thus there is an hour part included.

  • August 31, 2017 at 3:49 pm

    #1957761

    ekant_alone - Thursday, August 31, 2017 3:11 PM

    Also change in requirement - i want hourly increment and not daily increment. thus there is an hour part included.

    So answer my question. 😉  Are you allowed to use Inline Table Valued Functions or not?

    --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

  • August 31, 2017 at 4:07 pm

    #1957762

    yes i am allowed to. But i am confused how i can help my case using IVTF here. Can you please help me. I am super stuck.  thank you

Viewing 15 posts - 1 through 15 (of 26 total)

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