Fill in gaps between 2 dates

  • Hi  All,
    Need some help in filling up gaps between 2 dates.
    Scenario :  I have  Product Location balance  snapshot for specific dates  as shown below and want to produce query to show  carry forward the balance between 2 dates
    Example :  Product A  Location 2 balance on  2018-03-01  was 25    and there is not activity between  2018-03-01 - 2018-03-06  so the balance between these 2 dates should remain 25 and  on  2018-03-06  it will change to 50 and then again after that it carry forward balance .

    --- Current balance snapshot
    Product    Location    TransDate       TransBal
        A               1            2018-02-01       50
        A               1           2018-02-15        75
        A               2           2018-03-01        25
        A               2           2018-03-06        50
        B              1            2018-03-01        25
    ----- Output for  Product A  Location 2 -------
    Product    Location    TransDate       TransBal
        A               2           2018-03-01        25
        A               2           2018-03-02        25
        A               2           2018-03-03        25
        A               2           2018-03-04        25
        A               2           2018-03-05        25
        A               2           2018-03-06        50
       ....

    ----------------   Script to load test date--------------------
    DECLARE @StartDate DATE='01-01-2018'
    DECLARE @EndDate DATE=CONVERT(DATE,GETDATE())

    IF OBJECT_ID('tempdb..#Dates', 'U') IS NOT NULL
    DROP TABLE #Dates;

    CREATE TABLE #Dates (
     FullDate DATE
     );

    WHILE @StartDate <= @EndDate
    BEGIN
    INSERT #Dates (FullDate) VALUES (@StartDate)

    SET @StartDate = DATEADD(day,1,@StartDate)
    END

    IF OBJECT_ID('tempdb..#Product', 'U') IS NOT NULL
    DROP TABLE #Product;

    CREATE TABLE #Product (
     Product CHAR(1),
     Location TINYINT,
     TransDate DATE,
     TransBal INT
     );
    INSERT #Product (Product,Location,TransDate,TransBal) VALUES
     ('A', 1, '02/01/2018', 50),
     ('A', 1, '02/15/2018', 75),
     ('A', 2, '03/01/2018', 25),
     ('A', 2, '03/06/2018', 50),
     ('B', 1, '03/01/2018', 25);

    Thanks

  • --I've used something like this to generate date lists...
    --First, you need a way to generate rows:
    create function [dbo].[GenerateRows](@MaxRows int) returns table
    as
        return (
            with N1( C) as (select 0 union all select 0) -- 2 rows
            ,N2( C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
            ,N3( C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
            ,N4( C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
            ,N5( C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536
            ,N6( C) as (select 0 from N3 as T1 cross join N5 as T2) -- 1,048,576
            ,N7( C) as (select 0 from N3 as T1 cross join N6 as T2) -- 16,777,216
            ,Rowz(RowNum) as (select row_number() over (order by (select null)) from N7)
            select RowNum from Rowz
            where RowNum <= @MaxRows);
    GO

    --Then you can use that to generate dates...

    declare @firstDate date = '2018-01-01', @secondDate date= getdate();

    select dateadd(dd, r.RowNum-1, @firstDate) as FillDate
    from
    GenerateRows(datediff(dd, @firstDate, @secondDate)) as r;

  • adhikari707 - Monday, March 5, 2018 11:34 AM

    Hi  All,
    Need some help in filling up gaps between 2 dates.
    Scenario :  I have  Product Location balance  snapshot for specific dates  as shown below and want to produce query to show  carry forward the balance between 2 dates
    Example :  Product A  Location 2 balance on  2018-03-01  was 25    and there is not activity between  2018-03-01 - 2018-03-06  so the balance between these 2 dates should remain 25 and  on  2018-03-06  it will change to 50 and then again after that it carry forward balance .

    --- Current balance snapshot
    Product    Location    TransDate       TransBal
        A               1            2018-02-01       50
        A               1           2018-02-15        75
        A               2           2018-03-01        25
        A               2           2018-03-06        50
        B              1            2018-03-01        25
    ----- Output for  Product A  Location 2 -------
    Product    Location    TransDate       TransBal
        A               2           2018-03-01        25
        A               2           2018-03-02        25
        A               2           2018-03-03        25
        A               2           2018-03-04        25
        A               2           2018-03-05        25
        A               2           2018-03-06        50
       ....

    ----------------   Script to load test date--------------------
    DECLARE @StartDate DATE='01-01-2018'
    DECLARE @EndDate DATE=CONVERT(DATE,GETDATE())

    IF OBJECT_ID('tempdb..#Dates', 'U') IS NOT NULL
    DROP TABLE #Dates;

    CREATE TABLE #Dates (
     FullDate DATE
     );

    WHILE @StartDate <= @EndDate
    BEGIN
    INSERT #Dates (FullDate) VALUES (@StartDate)

    SET @StartDate = DATEADD(day,1,@StartDate)
    END

    IF OBJECT_ID('tempdb..#Product', 'U') IS NOT NULL
    DROP TABLE #Product;

    CREATE TABLE #Product (
     Product CHAR(1),
     Location TINYINT,
     TransDate DATE,
     TransBal INT
     );
    INSERT #Product (Product,Location,TransDate,TransBal) VALUES
     ('A', 1, '02/01/2018', 50),
     ('A', 1, '02/15/2018', 75),
     ('A', 2, '03/01/2018', 25),
     ('A', 2, '03/06/2018', 50),
     ('B', 1, '03/01/2018', 25);

    Thanks

    >> Need some help in filling up gaps between 2 dates.
    Scenario: I have (product_name, location_code, balance) snapshot for specific dates as shown below and want to produce query to show carry forward the balance between two dates. <<

    Your attempted DDL had a lot of problems. By definition, a table must have a key. You also don’t know that there’s only one standard display format allowed in ANSI/ISO standard SQL. So first thing let’s go ahead and correct what you did post:

    CREATE TABLE Product_History
    (product_name CHAR(1) NOT NULL CHECK (product_name LIKE ‘[A-Z]’),
    location_code CHAR(1) NOT NULL CHECK (location_code LIKE ‘[0-9]’)
    trans_date DATE,
    trans_amt DECIMAL (5,2) NOT NULL,
    PRIMARY KEY (product_name, location_code, trans_date));

    you might want to take some time to read the ISO 11179 naming rules.

    Example: product_name, a location_code and a balance on 2018-03-01 was 25 and there is not activity between 2018-03-01 and 2018-03-06 so the balance between these 2 dates should remain 25 and on 2018-03-06 it will change to 50 and then again after that it carry forward balance.

    But more than that, you don’t seem to appreciate the fact that, SQL is a declarative language, and you not supposed to be writing procedural code in it. We hate loops. We hate if-then-else statements. We hate local variables. We treat virtual tables (views, derived tables, etc.) as equal to materialized base tables. In fact, they have an advantage in that since they are recomputed each time there materialized, were sure that there’d information is current.

    I’m also curious; why did you have an integer for a balance?

    CREATE TABLE Product_History
    (product_name CHAR(1) NOT NULL CHECK (product_name LIKE ‘[A-Z’]),
    location_code CHAR(1) NOT NULL CHECK (location_code LIKE ‘[0-9]’)
    trans_date DATE,
    trans_balance_amt DECIMAL (5,2) NOT NULL,
    PRIMARY KEY (product_name, location_code, trans_date));

    INSERT INTO Product_History
    VALUES
    ('A', ‘1’, '2018-02-01', 50.00),
    ('A', ‘1’, '2018-02-15', 75.00),
    ('A', ‘2’, '2018-03-01', 25.00),
    ('A', ‘2’, '2018-03-06', 50.00),
    ('B', ‘1’, '2018-03-01', 25.00);

    I’d like you to stop and take some time to Google what a “calendar table†is and how to use it. It’s basically just what the name says; a list of dates, that uses a calendar date as its primary key and might include other information (we don’t care about the other stuff for now).

    What we want to do is get all of the dates in the calendar table in the range that comes in his parameters. From this, if a particular calendar date has a balance in the history, then replicate the amount forward within partitions.

    Here is a deliberately bad answer. Look at the results, make some corrections and get it to work (this is really a good exercise and will get you away from thinking in terms of loops).

    CREATE PROCEDURE Daily_Product_Balances
     (@in_start_date DATE, @in_end_date DATE)
    AS
    SELECT H.product_name, H.location_code, C.cal_date,
       FIRST_VALUE(trans_balance_amt)
       OVER (PARTITION BY H.product_name, H.location_code
         ORDER BY C.cal_date)
    FROM Calendar AS C
       LEFT OUTER JOIN
       Product_History AS H
       ON C.cal_date BETWEEN @in_start_date AND @in_end_date
     AND H.trans_date BETWEEN @in_start_date AND @in_end_date;

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • I use something like this to generate dates:


    Declare @minDate date = '2018-03-01'
           , @maxDate date = '2018-03-06';

       With dates
         As (
    Select n.id
          , DateValue = dateadd(day, n.id, @minDate)
       From (Select row_number() over(Order By ac.object_id) - 1 As id
               From sys.all_columns ac) As n
            )
    Select *
       From dates d
    Where d.DateValue <= @maxDate;

    Now - you just need to get the row that applies for that date - and to do that we can use a CROSS APPLY:


    Declare @product Table (
       Product char(1)
      , Location tinyint
      , TransDate DATE
      , TransBal int
       );

    Insert @product (
       Product
      , Location
      , TransDate
      , TransBal
       )
    Values ('A', 1, '02/01/2018', 50)
      , ('A', 1, '02/15/2018', 75)
      , ('A', 2, '03/01/2018', 25)
      , ('A', 2, '03/06/2018', 50)
      , ('B', 1, '03/01/2018', 25);

    Select * From @product;

    Declare @minDate date = (Select min(p.TransDate) From @product p)
      , @maxDate date = (Select max(p.TransDate) From @product p);

     With dates
      As (
    Select n.id
      , DateValue = dateadd(day, n.id, @minDate)
     From (Select row_number() over(Order By ac.object_id) - 1 As id
        From sys.all_columns ac) As n
       )
    Select pd.Product
      , pd.Location
      , d.DateValue As TransDate
      , pd.TransBal
     From dates    d
    Cross Apply (Select Top 1
            *
          From @product p
          Where p.TransDate <= d.DateValue
          Order By
            p.TransDate desc) As pd
    Where d.DateValue <= @maxDate;

    If you already have a number or tally table - you can replace the derived table with the row_number function and use your number or tally table directly.

    Edit: I missed part of the relationship in the cross apply - if multiple products can have the same transdate then you need to include that.  To do that you would change the query to:


    With dates
    As (
    Select n.id
    , DateValue = dateadd(day, n.id, @minDate)
    From (Select row_number() over(Order By ac.object_id) - 1 As id
      From sys.all_columns ac) As n
     )
    Select Distinct pd.Product
    , pd.Location
    , d.DateValue As TransDate
    , pd.TransBal
    From dates  d
    Cross Join @product p
    Cross Apply (Select Top 1
       *
      From @product p1
      Where p1.Product = p.Product And p1.TransDate <= d.DateValue
      Order By
       p1.TransDate desc) As pd
    Where d.DateValue <= @maxDate;

    The CROSS JOIN creates a row for each date - the WHERE in the CROSS APPLY then limits the value to the preceding product value - and the DISTINCT eliminates any duplicates generated by the cross join.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Just for the fun of it...

    IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
    BEGIN DROP TABLE #TestData; END;

    CREATE TABLE #TestData (
        SomeDate DATE NOT NULL PRIMARY KEY CLUSTERED,    --<= this will function as a "POC" index for the query.
        SomeValue INT NOT NULL
        );
    INSERT #TestData (SomeDate, SomeValue) VALUES
        ('2018-01-01', 25),
        ('2018-01-05', 22),
        ('2018-01-22', 13),
        ('2018-02-02', 20),
        ('2018-02-05', 30),
        ('2018-02-15', 34),
        ('2018-02-24', 42),
        ('2018-02-27', 9),
        ('2018-03-01', 15),
        ('2018-03-11', 4),
        ('2018-03-15', 25),
        ('2018-03-20', 60),
        ('2018-04-09', 12),
        ('2018-04-12', 32),
        ('2018-04-21', 34),
        ('2018-04-24', 61),
        ('2018-05-15', 4),
        ('2018-05-23', 33);

    --====================================================

    WITH
        cte_get_lead_date AS (    --<= the CTE is just an easy option for making the "lead_date" available to the main query.
            SELECT
                td.SomeDate,
                td.SomeValue,
                lead_date = LEAD(td.SomeDate, 1, td.SomeDate) OVER (ORDER BY td.SomeDate)    --<= the use of a "POC" index prevents the windowing function from causing a sort operation in the execution plan.
            FROM
                #TestData td
            )
    SELECT
        SomeDate = DATEADD(DAY, atd.add_to_date, gld.SomeDate),
        gld.SomeValue
    FROM
        cte_get_lead_date gld
        CROSS APPLY (
            SELECT TOP (ISNULL(NULLIF(DATEDIFF(DAY, gld.SomeDate, gld.lead_date), 0), 1))
                ROW_NUMBER() OVER (ORDER BY n1.n) - 1
            FROM    --<= just showing an alternate means of creating an inline tally table.
                ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n1 (n)                --<= 10 rows
                CROSS APPLY ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n2 (n)    --<= 100 rows
                CROSS APPLY ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n3 (n)    --<= 1000 rows (seems like 3 years should be enough "head room").
            ) atd (add_to_date);

    The results...
    SomeDate SomeValue
    ---------- -----------
    2018-01-01 25
    2018-01-02 25
    2018-01-03 25
    2018-01-04 25
    2018-01-05 22
    2018-01-06 22
    2018-01-07 22
    2018-01-08 22
    2018-01-09 22
    2018-01-10 22
    2018-01-11 22
    2018-01-12 22
    2018-01-13 22
    2018-01-14 22
    2018-01-15 22
    2018-01-16 22
    2018-01-17 22
    2018-01-18 22
    2018-01-19 22
    2018-01-20 22
    2018-01-21 22
    2018-01-22 13
    2018-01-23 13
    2018-01-24 13
    2018-01-25 13
    2018-01-26 13
    2018-01-27 13
    2018-01-28 13
    2018-01-29 13
    2018-01-30 13
    2018-01-31 13
    2018-02-01 13
    2018-02-02 20
    2018-02-03 20
    2018-02-04 20
    2018-02-05 30
    2018-02-06 30
    2018-02-07 30
    2018-02-08 30
    2018-02-09 30
    2018-02-10 30
    2018-02-11 30
    2018-02-12 30
    2018-02-13 30
    2018-02-14 30
    2018-02-15 34
    2018-02-16 34
    2018-02-17 34
    2018-02-18 34
    2018-02-19 34
    2018-02-20 34
    2018-02-21 34
    2018-02-22 34
    2018-02-23 34
    2018-02-24 42
    2018-02-25 42
    2018-02-26 42
    2018-02-27 9
    2018-02-28 9
    2018-03-01 15
    2018-03-02 15
    2018-03-03 15
    2018-03-04 15
    2018-03-05 15
    2018-03-06 15
    2018-03-07 15
    2018-03-08 15
    2018-03-09 15
    2018-03-10 15
    2018-03-11 4
    2018-03-12 4
    2018-03-13 4
    2018-03-14 4
    2018-03-15 25
    2018-03-16 25
    2018-03-17 25
    2018-03-18 25
    2018-03-19 25
    2018-03-20 60
    2018-03-21 60
    2018-03-22 60
    2018-03-23 60
    2018-03-24 60
    2018-03-25 60
    2018-03-26 60
    2018-03-27 60
    2018-03-28 60
    2018-03-29 60
    2018-03-30 60
    2018-03-31 60
    2018-04-01 60
    2018-04-02 60
    2018-04-03 60
    2018-04-04 60
    2018-04-05 60
    2018-04-06 60
    2018-04-07 60
    2018-04-08 60
    2018-04-09 12
    2018-04-10 12
    2018-04-11 12
    2018-04-12 32
    2018-04-13 32
    2018-04-14 32
    2018-04-15 32
    2018-04-16 32
    2018-04-17 32
    2018-04-18 32
    2018-04-19 32
    2018-04-20 32
    2018-04-21 34
    2018-04-22 34
    2018-04-23 34
    2018-04-24 61
    2018-04-25 61
    2018-04-26 61
    2018-04-27 61
    2018-04-28 61
    2018-04-29 61
    2018-04-30 61
    2018-05-01 61
    2018-05-02 61
    2018-05-03 61
    2018-05-04 61
    2018-05-05 61
    2018-05-06 61
    2018-05-07 61
    2018-05-08 61
    2018-05-09 61
    2018-05-10 61
    2018-05-11 61
    2018-05-12 61
    2018-05-13 61
    2018-05-14 61
    2018-05-15 4
    2018-05-16 4
    2018-05-17 4
    2018-05-18 4
    2018-05-19 4
    2018-05-20 4
    2018-05-21 4
    2018-05-22 4
    2018-05-23 33

  • jcelko212 32090 - Monday, March 5, 2018 6:34 PM

    You also don’t know that there’s only one standard display format allowed in ANSI/ISO standard SQL. So first thing let’s go ahead and correct what you did post:

    Apparently, you've never actually read the standards, which allow for two formats. YYYYMMDD as the primary/preferred format and YYYY-MM-DD as a secondary format, which silently screws you in SQL Server depending on the default language.  For example, if the French language is the default, SQL Server will actually use or consume what you think is YYYY-MM-DD as YYYY-DD-MM and will only report an error if you violate the allowed range for any of the parts.

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

  • Jeff Moden - Wednesday, March 7, 2018 2:12 PM

    jcelko212 32090 - Monday, March 5, 2018 6:34 PM

    You also don’t know that there’s only one standard display format allowed in ANSI/ISO standard SQL. So first thing let’s go ahead and correct what you did post:

    Apparently, you've never actually read the standards, which allow for two formats. YYYYMMDD as the primary/preferred format and YYYY-MM-DD as a secondary format, which silently screws you in SQL Server depending on the default language.  For example, if the French language is the default, SQL Server will actually use or consume what you think is YYYY-MM-DD as YYYY-DD-MM and will only report an error if you violate the allowed range for any of the parts.

    No, Jeff, ISO-8601 has several display formats, but we voted to allow only the "yyyy-mm-dd" in ANSI/ISO Standard SQL. I would have like the version with a "T" between the date and time instead of a space. A solid string prevents "white space" problems.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Jeffrey Williams 3188 - Tuesday, March 6, 2018 4:13 PM

    I use something like this to generate dates:


    Declare @minDate date = '2018-03-01'
           , @maxDate date = '2018-03-06';

       With dates
         As (
    Select n.id
          , DateValue = dateadd(day, n.id, @minDate)
       From (Select row_number() over(Order By ac.object_id) - 1 As id
               From sys.all_columns ac) As n
            )
    Select *
       From dates d
    Where d.DateValue <= @maxDate;

    Now - you just need to get the row that applies for that date - and to do that we can use a CROSS APPLY:


    Declare @product Table (
       Product char(1)
      , Location tinyint
      , TransDate DATE
      , TransBal int
       );

    Insert @product (
       Product
      , Location
      , TransDate
      , TransBal
       )
    Values ('A', 1, '02/01/2018', 50)
      , ('A', 1, '02/15/2018', 75)
      , ('A', 2, '03/01/2018', 25)
      , ('A', 2, '03/06/2018', 50)
      , ('B', 1, '03/01/2018', 25);

    Select * From @product;

    Declare @minDate date = (Select min(p.TransDate) From @product p)
      , @maxDate date = (Select max(p.TransDate) From @product p);

     With dates
      As (
    Select n.id
      , DateValue = dateadd(day, n.id, @minDate)
     From (Select row_number() over(Order By ac.object_id) - 1 As id
        From sys.all_columns ac) As n
       )
    Select pd.Product
      , pd.Location
      , d.DateValue As TransDate
      , pd.TransBal
     From dates    d
    Cross Apply (Select Top 1
            *
          From @product p
          Where p.TransDate <= d.DateValue
          Order By
            p.TransDate desc) As pd
    Where d.DateValue <= @maxDate;

    If you already have a number or tally table - you can replace the derived table with the row_number function and use your number or tally table directly.

    Edit: I missed part of the relationship in the cross apply - if multiple products can have the same transdate then you need to include that.  To do that you would change the query to:


    With dates
    As (
    Select n.id
    , DateValue = dateadd(day, n.id, @minDate)
    From (Select row_number() over(Order By ac.object_id) - 1 As id
      From sys.all_columns ac) As n
     )
    Select Distinct pd.Product
    , pd.Location
    , d.DateValue As TransDate
    , pd.TransBal
    From dates  d
    Cross Join @product p
    Cross Apply (Select Top 1
       *
      From @product p1
      Where p1.Product = p.Product And p1.TransDate <= d.DateValue
      Order By
       p1.TransDate desc) As pd
    Where d.DateValue <= @maxDate;

    The CROSS JOIN creates a row for each date - the WHERE in the CROSS APPLY then limits the value to the preceding product value - and the DISTINCT eliminates any duplicates generated by the cross join.

    Hi thanks for giving the idea about the logic to use , will try this approach and update you the results that i'll get.

  • Jason A. Long - Wednesday, March 7, 2018 12:45 PM

    Just for the fun of it...

    IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
    BEGIN DROP TABLE #TestData; END;

    CREATE TABLE #TestData (
        SomeDate DATE NOT NULL PRIMARY KEY CLUSTERED,    --<= this will function as a "POC" index for the query.
        SomeValue INT NOT NULL
        );
    INSERT #TestData (SomeDate, SomeValue) VALUES
        ('2018-01-01', 25),
        ('2018-01-05', 22),
        ('2018-01-22', 13),
        ('2018-02-02', 20),
        ('2018-02-05', 30),
        ('2018-02-15', 34),
        ('2018-02-24', 42),
        ('2018-02-27', 9),
        ('2018-03-01', 15),
        ('2018-03-11', 4),
        ('2018-03-15', 25),
        ('2018-03-20', 60),
        ('2018-04-09', 12),
        ('2018-04-12', 32),
        ('2018-04-21', 34),
        ('2018-04-24', 61),
        ('2018-05-15', 4),
        ('2018-05-23', 33);

    --====================================================

    WITH
        cte_get_lead_date AS (    --<= the CTE is just an easy option for making the "lead_date" available to the main query.
            SELECT
                td.SomeDate,
                td.SomeValue,
                lead_date = LEAD(td.SomeDate, 1, td.SomeDate) OVER (ORDER BY td.SomeDate)    --<= the use of a "POC" index prevents the windowing function from causing a sort operation in the execution plan.
            FROM
                #TestData td
            )
    SELECT
        SomeDate = DATEADD(DAY, atd.add_to_date, gld.SomeDate),
        gld.SomeValue
    FROM
        cte_get_lead_date gld
        CROSS APPLY (
            SELECT TOP (ISNULL(NULLIF(DATEDIFF(DAY, gld.SomeDate, gld.lead_date), 0), 1))
                ROW_NUMBER() OVER (ORDER BY n1.n) - 1
            FROM    --<= just showing an alternate means of creating an inline tally table.
                ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n1 (n)                --<= 10 rows
                CROSS APPLY ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n2 (n)    --<= 100 rows
                CROSS APPLY ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n3 (n)    --<= 1000 rows (seems like 3 years should be enough "head room").
            ) atd (add_to_date);

    The results...
    SomeDate SomeValue
    ---------- -----------
    2018-01-01 25
    2018-01-02 25
    2018-01-03 25
    2018-01-04 25
    2018-01-05 22
    2018-01-06 22
    2018-01-07 22
    2018-01-08 22
    2018-01-09 22
    2018-01-10 22
    2018-01-11 22
    2018-01-12 22
    2018-01-13 22
    2018-01-14 22
    2018-01-15 22
    2018-01-16 22
    2018-01-17 22
    2018-01-18 22
    2018-01-19 22
    2018-01-20 22
    2018-01-21 22
    2018-01-22 13
    2018-01-23 13
    2018-01-24 13
    2018-01-25 13
    2018-01-26 13
    2018-01-27 13
    2018-01-28 13
    2018-01-29 13
    2018-01-30 13
    2018-01-31 13
    2018-02-01 13
    2018-02-02 20
    2018-02-03 20
    2018-02-04 20
    2018-02-05 30
    2018-02-06 30
    2018-02-07 30
    2018-02-08 30
    2018-02-09 30
    2018-02-10 30
    2018-02-11 30
    2018-02-12 30
    2018-02-13 30
    2018-02-14 30
    2018-02-15 34
    2018-02-16 34
    2018-02-17 34
    2018-02-18 34
    2018-02-19 34
    2018-02-20 34
    2018-02-21 34
    2018-02-22 34
    2018-02-23 34
    2018-02-24 42
    2018-02-25 42
    2018-02-26 42
    2018-02-27 9
    2018-02-28 9
    2018-03-01 15
    2018-03-02 15
    2018-03-03 15
    2018-03-04 15
    2018-03-05 15
    2018-03-06 15
    2018-03-07 15
    2018-03-08 15
    2018-03-09 15
    2018-03-10 15
    2018-03-11 4
    2018-03-12 4
    2018-03-13 4
    2018-03-14 4
    2018-03-15 25
    2018-03-16 25
    2018-03-17 25
    2018-03-18 25
    2018-03-19 25
    2018-03-20 60
    2018-03-21 60
    2018-03-22 60
    2018-03-23 60
    2018-03-24 60
    2018-03-25 60
    2018-03-26 60
    2018-03-27 60
    2018-03-28 60
    2018-03-29 60
    2018-03-30 60
    2018-03-31 60
    2018-04-01 60
    2018-04-02 60
    2018-04-03 60
    2018-04-04 60
    2018-04-05 60
    2018-04-06 60
    2018-04-07 60
    2018-04-08 60
    2018-04-09 12
    2018-04-10 12
    2018-04-11 12
    2018-04-12 32
    2018-04-13 32
    2018-04-14 32
    2018-04-15 32
    2018-04-16 32
    2018-04-17 32
    2018-04-18 32
    2018-04-19 32
    2018-04-20 32
    2018-04-21 34
    2018-04-22 34
    2018-04-23 34
    2018-04-24 61
    2018-04-25 61
    2018-04-26 61
    2018-04-27 61
    2018-04-28 61
    2018-04-29 61
    2018-04-30 61
    2018-05-01 61
    2018-05-02 61
    2018-05-03 61
    2018-05-04 61
    2018-05-05 61
    2018-05-06 61
    2018-05-07 61
    2018-05-08 61
    2018-05-09 61
    2018-05-10 61
    2018-05-11 61
    2018-05-12 61
    2018-05-13 61
    2018-05-14 61
    2018-05-15 4
    2018-05-16 4
    2018-05-17 4
    2018-05-18 4
    2018-05-19 4
    2018-05-20 4
    2018-05-21 4
    2018-05-22 4
    2018-05-23 33

    Hi  thanks for providing the logic and  i am also using something like this where i created  date range for transaction and then  finally use that date range to populate the missing values , will update the results.
    thanks for the help

  • I realized there is a much simpler way to get the results:


    Declare @product Table (
       Product char(1)
      , Location tinyint
      , TransDate DATE
      , TransBal int
       );

    Insert @product (
       Product
      , Location
      , TransDate
      , TransBal
       )
    Values ('A', 1, '02/01/2018', 50)
      , ('A', 1, '02/15/2018', 75)
      , ('A', 2, '03/01/2018', 25)
      , ('A', 2, '03/06/2018', 50)
      , ('B', 1, '03/01/2018', 25);

    Select * From @product;

    Declare @minDate date = (Select min(p.TransDate) From @product p)
      , @maxDate date = dateadd(day, 1, (Select max(p.TransDate) From @product p));

     With dates
      As (
    Select n.id
      , DateValue = dateadd(day, n.id, @minDate)
     From (Select row_number() over(Order By ac.object_id) - 1 As id From sys.all_columns ac) As n
       )
      , products
      As (
    Select *
      , EndTransDate = lead(p.TransDate, 1, @maxDate) over(Partition By p.Product, p.Location Order By p.TransDate)
     From @product   p
       )
    Select ps.Product
      , ps.Location
      , TransDate = dt.DateValue
      , ps.TransBal
     From products   ps
    Inner Join dates  dt On dt.DateValue >= ps.TransDate
             And dt.DateValue < ps.EndTransDate
    Order By
       Product
      , Location
      , TransDate;

    With this one - it will generate rows through the max end date for any product/location that does not have a following row. If there is a next date for the product/location it will use the values for that row...

    Increase the variable @maxDate to include rows to the end date you want...for example, if you change @maxDate to: dateadd(day, 1, getdate()) you will get rows for each product/location through current date.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams 3188 - Thursday, March 8, 2018 11:16 AM

    I realized there is a much simpler way to get the results:


    Declare @product Table (
       Product char(1)
      , Location tinyint
      , TransDate DATE
      , TransBal int
       );

    Insert @product (
       Product
      , Location
      , TransDate
      , TransBal
       )
    Values ('A', 1, '02/01/2018', 50)
      , ('A', 1, '02/15/2018', 75)
      , ('A', 2, '03/01/2018', 25)
      , ('A', 2, '03/06/2018', 50)
      , ('B', 1, '03/01/2018', 25);

    Select * From @product;

    Declare @minDate date = (Select min(p.TransDate) From @product p)
      , @maxDate date = dateadd(day, 1, (Select max(p.TransDate) From @product p));

     With dates
      As (
    Select n.id
      , DateValue = dateadd(day, n.id, @minDate)
     From (Select row_number() over(Order By ac.object_id) - 1 As id From sys.all_columns ac) As n
       )
      , products
      As (
    Select *
      , EndTransDate = lead(p.TransDate, 1, @maxDate) over(Partition By p.Product, p.Location Order By p.TransDate)
     From @product   p
       )
    Select ps.Product
      , ps.Location
      , TransDate = dt.DateValue
      , ps.TransBal
     From products   ps
    Inner Join dates  dt On dt.DateValue >= ps.TransDate
             And dt.DateValue < ps.EndTransDate
    Order By
       Product
      , Location
      , TransDate;

    With this one - it will generate rows through the max end date for any product/location that does not have a following row. If there is a next date for the product/location it will use the values for that row...

    Increase the variable @maxDate to include rows to the end date you want...for example, if you change @maxDate to: dateadd(day, 1, getdate()) you will get rows for each product/location through current date.

    Yes this is exactly i am doing currently now , generating  end date for each row  based on  next transaction date   for each product and location.
    so far results are coming out good.

  • jcelko212 32090 - Thursday, March 8, 2018 7:18 AM

    Jeff Moden - Wednesday, March 7, 2018 2:12 PM

    jcelko212 32090 - Monday, March 5, 2018 6:34 PM

    You also don’t know that there’s only one standard display format allowed in ANSI/ISO standard SQL. So first thing let’s go ahead and correct what you did post:

    Apparently, you've never actually read the standards, which allow for two formats. YYYYMMDD as the primary/preferred format and YYYY-MM-DD as a secondary format, which silently screws you in SQL Server depending on the default language.  For example, if the French language is the default, SQL Server will actually use or consume what you think is YYYY-MM-DD as YYYY-DD-MM and will only report an error if you violate the allowed range for any of the parts.

    No, Jeff, ISO-8601 has several display formats, but we voted to allow only the "yyyy-mm-dd" in ANSI/ISO Standard SQL. I would have like the version with a "T" between the date and time instead of a space. A solid string prevents "white space" problems.

    Please post the document number for this "ANSI/ISO Standard SQL" that you speak of.  If it is not ISO-8601, you really need to stop telling people to adhere to ISO-8601.

    And, yes, despite the claim that YYYY-MM-DD is part of some ANSI/ISO Standard SQL, the YYYY-MM-DD format in SQL Server might actually be YYYY-DD-MM depending on the language in effect for the given SQL statement.

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

  • Jeff Moden - Thursday, March 8, 2018 9:57 PM

    And, yes, despite the claim that YYYY-MM-DD is part of some ANSI/ISO Standard SQL, the YYYY-MM-DD format in SQL Server might actually be YYYY-DD-MM depending on the language in effect for the given SQL statement.

    Jeff, I don't think that's right, unless it's possible to install some language pack that doesn't ship with SQL Server.

    SELECT DISTINCT dateformat
    FROM sys.syslanguages

    -- Results
    dateformat
    ----------
    dmy
    mdy
    ymd

    John

  • John Mitchell-245523 - Friday, March 9, 2018 2:56 AM

    Jeff Moden - Thursday, March 8, 2018 9:57 PM

    And, yes, despite the claim that YYYY-MM-DD is part of some ANSI/ISO Standard SQL, the YYYY-MM-DD format in SQL Server might actually be YYYY-DD-MM depending on the language in effect for the given SQL statement.

    Jeff, I don't think that's right, unless it's possible to install some language pack that doesn't ship with SQL Server.

    SELECT DISTINCT dateformat
    FROM sys.syslanguages

    -- Results
    dateformat
    ----------
    dmy
    mdy
    ymd

    John

    Run the following code in the "Return results as text" mode and see what I'm talking about, John.  The dashed supposed "ANSI/ISO SQL Standard" that Joe is talking about fails silently and miserably in SQL Server whereas the Primary Standard for the ISO 8601 standard does not.

    --===== Presets
        SET NOCOUNT ON;
    DECLARE @StringDate CHAR(10)
    ;
    --===== Using the supposed "only" SQL Standard date format allowed according to Joe Celko.
     SELECT @StringDate = '2018-05-06'
    ;
      PRINT REPLICATE('=',80);
      PRINT '========== Using a DASHED "Ansi" representation of date ==========';
      PRINT 'According to the "Only" ANSI/ISO Standard that Joe speaks of,';
      PRINT 'the input character based variable is in the YYYY-MM-DD format'
      PRINT 'but the conversion for FRENCH causes a swap in Month and Day.'
    ;
        SET LANGUAGE FRENCH;
     SELECT  [DASHED FRENCH CAST]   = CAST(@StringDate AS DATETIME)
            ,[MONTH]                = DATEPART(mm,CAST(@StringDate AS DATETIME))
            ,[DAY]                  = DATEPART(dd,CAST(@StringDate AS DATETIME))
    ;
        SET LANGUAGE ENGLISH;
     SELECT  [DASHED ENGLISH CAST]  = CAST(@StringDate AS DATETIME)
            ,[MONTH]                = DATEPART(mm,CAST(@StringDate AS DATETIME))
            ,[DAY]                  = DATEPART(dd,CAST(@StringDate AS DATETIME))
    ;
    --===== Using something that ALWAYS works correctly, according to the PRIMARY ANSI Standard.
     SELECT @StringDate = '20180506'
    ;
      PRINT REPLICATE('=',80);
      PRINT '========== Using the PRIMARY ANSI/ISO representation of date ==========';
      PRINT 'Haven''t tested all the languages but it''s interpreted correctly for';
      PRINT 'for both FRENCH and ENGLISH.  One failure is good enough for me to ';
      PRINT 'to say the "Only" standard that Joe speaks of fails in SQL Server.'
    ;
        SET LANGUAGE FRENCH;
     SELECT  [ISO FRENCH CAST]  = CAST(@StringDate AS DATETIME)
            ,[MONTH]            = DATEPART(mm,CAST(@StringDate AS DATETIME))
            ,[DAY]              = DATEPART(dd,CAST(@StringDate AS DATETIME))
    ;
        SET LANGUAGE ENGLISH;
     SELECT  [ISO ENGLISH CAST] = CAST(@StringDate AS DATETIME)
            ,[MONTH]            = DATEPART(mm,CAST(@StringDate AS DATETIME))
            ,[DAY]              = DATEPART(dd,CAST(@StringDate AS DATETIME))
    ;

    I'd post the results here but the forum software makes a mess of it no matter what I've tried to do to make it properly spaced and readable.

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

  • Jeff Moden - Friday, March 9, 2018 7:15 AM

    Run the following code in the "Return results as text" mode and see what I'm talking about, John.  The dashed supposed "ANSI/ISO SQL Standard" that Joe is talking about fails silently and miserably in SQL Server whereas the Primary Standard for the ISO 8601 standard does not.

    --===== Presets
        SET NOCOUNT ON;
    DECLARE @StringDate CHAR(10)
    ;
    --===== Using the supposed "only" SQL Standard date format allowed according to Joe Celko.
     SELECT @StringDate = '2018-05-06'
    ;
      PRINT REPLICATE('=',80);
      PRINT '========== Using a DASHED "Ansi" representation of date ==========';
      PRINT 'According to the "Only" ANSI/ISO Standard that Joe speaks of,';
      PRINT 'the input character based variable is in the YYYY-MM-DD format'
      PRINT 'but the conversion for FRENCH causes a swap in Month and Day.'
    ;
        SET LANGUAGE FRENCH;
     SELECT  [DASHED FRENCH CAST]   = CAST(@StringDate AS DATETIME)
            ,[MONTH]                = DATEPART(mm,CAST(@StringDate AS DATETIME))
            ,[DAY]                  = DATEPART(dd,CAST(@StringDate AS DATETIME))
    ;
        SET LANGUAGE ENGLISH;
     SELECT  [DASHED ENGLISH CAST]  = CAST(@StringDate AS DATETIME)
            ,[MONTH]                = DATEPART(mm,CAST(@StringDate AS DATETIME))
            ,[DAY]                  = DATEPART(dd,CAST(@StringDate AS DATETIME))
    ;
    --===== Using something that ALWAYS works correctly, according to the PRIMARY ANSI Standard.
     SELECT @StringDate = '20180506'
    ;
      PRINT REPLICATE('=',80);
      PRINT '========== Using the PRIMARY ANSI/ISO representation of date ==========';
      PRINT 'Haven''t tested all the languages but it''s interpreted correctly for';
      PRINT 'for both FRENCH and ENGLISH.  One failure is good enough for me to ';
      PRINT 'to say the "Only" standard that Joe speaks of failes in SQL Server.'
    ;
        SET LANGUAGE FRENCH;
     SELECT  [ISO FRENCH CAST]  = CAST(@StringDate AS DATETIME)
            ,[MONTH]            = DATEPART(mm,CAST(@StringDate AS DATETIME))
            ,[DAY]              = DATEPART(dd,CAST(@StringDate AS DATETIME))
    ;
        SET LANGUAGE ENGLISH;
     SELECT  [ISO ENGLISH CAST] = CAST(@StringDate AS DATETIME)
            ,[MONTH]            = DATEPART(mm,CAST(@StringDate AS DATETIME))
            ,[DAY]              = DATEPART(dd,CAST(@StringDate AS DATETIME))
    ;

    I'd post the results here but the forum software makes a mess of it no matter what I've tried to do to make it properly spaced and readable.

    Ouch!  That's nasty.  I see what you mean now.

    John

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

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