Generate Monthly Transactions between Start and End Dates

  • Hello,

    I am looking for help to understand how to generate multiple rows based on Start and End Dates.  What I have is customers enrolled into a monthly fee with a StartDt and EndDt.  I would like to be able to create a single transaction per month, is this possible/

    Here is my sample code:

    -- DROP TABLE #t
    CREATE TABLE #t (CustNo int, Net money, StartDt datetime, EndDt datetime)
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (2020, 12.85, '08/22/2018' , '03/22/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (2020, 4.24, '08/22/2018' , '03/22/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (2020, 17.54, '08/22/2018' , '03/22/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (2020, 51.10, '08/22/2018' , '03/22/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (14014, 2.85, '08/22/2018' , '03/22/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (14014, 86.40, '08/22/2018' , '03/22/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (17747, 52.74, '12/13/2018' , '11/13/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (17747, 54.38, '12/13/2018' , '11/13/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (17749, 190.80, '04/23/2021' , '07/23/2021')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (17813, 322.61, '10/12/2018' , '07/12/2019')
    SELECT * FROM #t

    My desired results for the first record would outcome like this:

    CustNoNetStartDtEndDt
    2020 $12.85 8/22/20189/22/2018
    2020 $12.85 9/22/201810/22/2018
    2020 $12.85 10/22/201811/22/2018
    2020 $12.85 11/22/201812/22/2018
    2020 $12.85 12/22/20181/22/2019
    2020 $12.85 1/22/20192/22/2019
    2020 $12.85 2/22/20193/22/2019

    Many thanks in advance for you help!

  • The easiest way is to use a Calendar table and explode the join

    -- DROP TABLE #t
    CREATE TABLE #t (CustNo int, Net money, StartDt datetime, EndDt datetime)
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (2020, 12.85, '08/22/2018' , '03/22/2019');
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (2020, 4.24, '08/22/2018' , '03/22/2019');

    /* Calendar(ish) Table */
    CREATE TABLE #MonthList (MonthNo int PRIMARY KEY);
    INSERT INTO #Monthlist(MonthNo) VALUES (1),(2),(3),(4),(5),(6),(7),(8);

    SELECT CustNo, Net, StartDt, EndDt,ml.MonthNo,DATEADD(month,ml.MonthNo-1,StartDt)
    FROM
    (SELECT CustNo, Net, StartDt, EndDt, Duration = DATEDIFF(month,StartDt,EndDt)
    FROM #t) spans
    CROSS JOIN #MonthList ml
    WHERE ml.MonthNo-1<=spans.Duration;

    • This reply was modified 2 weeks, 2 days ago by  pietlinden.
  • rjjh78 wrote:

    Hello,

    I am looking for help to understand how to generate multiple rows based on Start and End Dates.  What I have is customers enrolled into a monthly fee with a StartDt and EndDt.  I would like to be able to create a single transaction per month, is this possible/

    Here is my sample code:

    -- DROP TABLE #t
    CREATE TABLE #t (CustNo int, Net money, StartDt datetime, EndDt datetime)
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (2020, 12.85, '08/22/2018' , '03/22/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (2020, 4.24, '08/22/2018' , '03/22/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (2020, 17.54, '08/22/2018' , '03/22/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (2020, 51.10, '08/22/2018' , '03/22/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (14014, 2.85, '08/22/2018' , '03/22/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (14014, 86.40, '08/22/2018' , '03/22/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (17747, 52.74, '12/13/2018' , '11/13/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (17747, 54.38, '12/13/2018' , '11/13/2019')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (17749, 190.80, '04/23/2021' , '07/23/2021')
    INSERT INTO #t (CustNo,Net, StartDt, EndDt) VALUES (17813, 322.61, '10/12/2018' , '07/12/2019')
    SELECT * FROM #t

    My desired results for the first record would outcome like this:

    CustNoNetStartDtEndDt
    2020 $12.85 8/22/20189/22/2018
    2020 $12.85 9/22/201810/22/2018
    2020 $12.85 10/22/201811/22/2018
    2020 $12.85 11/22/201812/22/2018
    2020 $12.85 12/22/20181/22/2019
    2020 $12.85 1/22/20192/22/2019
    2020 $12.85 2/22/20193/22/2019

    Many thanks in advance for you help!

    With the understanding that there is nothing in the source data that well ensure that the row order will be exactly in the order of the source, the following code will work without a date table.

    It DOES however, require an "fnTally" function, which you can get from the similarly named link in my signature line at the bottom of this post.  If you're DBA doesn't allow even for such very high performance functions in databases, we can do a work around but we should have a discussion with your DBA about this function.

    Once you've created the fnTally function (which has thousands of other uses), the following code will do it for you.  As you can see, the use of fnTally can make a whole bunch of stuff really easy and really fast.  This idea of creating multiple rows from a single row is sometimes referred to as "Relational Multiplication" although it's really just the formation of a separate Cartesian Product for each row.  Think of it as a "Set-Based Nested Loop".

    The "N" column from the function is simply a sequence of integers that (in this case) start at 0 and go up to the difference in months for each row in the source table.  For an introduction to the concept of a Tally table or function being used as a high performance  "Pseuodo-Cursor" to replace certain loops (a LOT of different reasons for a loop), please see the following article.  The code I suggest for your problem is after the link.

    https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1

     SELECT  src.CustNo
    ,src.Net
    ,StartDt = CONVERT(DATE,DATEADD(mm,t.N ,src.StartDt))
    ,EndDt = CONVERT(DATE,DATEADD(mm,t.N+1,src.StartDt))
    FROM #t src
    CROSS APPLY fnTally(0,DATEDIFF(mm,StartDt,EndDt))t
    ORDER BY src.CustNo, src.Net, src.StartDt, t.N
    ;

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

  • I've got just the table valued function you need:

    IF OBJECT_ID('[dbo].[DateRange]','IF') IS NULL BEGIN
    PRINT 'CREATE FUNCTION [dbo].[DateRange]'
    EXEC ('CREATE FUNCTION [dbo].[DateRange] () RETURNS TABLE AS RETURN SELECT 1 X')
    END
    GO
    /*-- **********************************************************************
    -- FUNCTION: DateRange
    -- Returns a table of datetime values based on the parameters
    -- Parameters:
    -- @StartDate :Start date of the series
    -- @EndDate :End date of the series
    -- @DatePart :The time unit for @interval
    -- ns : nanoseconds
    -- mcs : microseconds
    -- ms : milliseconds
    -- ss : seconds
    -- mi : minutes
    -- hh : hours
    -- dd : days
    -- ww : weeks
    -- mm : months
    -- qq : quarters
    -- yy : years
    -- @Interval :The number of dateparts between each value returned
    --
    -- Sample Calls:
    -- SELECT * FROM [dbo].[DateRange]('2011-01-01 12:24:35', '2011-02-01 12:24:35', 'ss', 2)
    -- SELECT COUNT(*) FROM [dbo].[DateRange]('2018-01-01 00:00:00', '2018-01-25 20:31:23.646', 'ms', default)
    -- SELECT * FROM [dbo].[DateRange]('2011-01-01', '2012-02-03', default, default)
    -- SELECT * FROM [dbo].[DateRange]('2012-02-03', '2011-01-01', 'dd', 7)
    -- SELECT DATEDIFF(ns,'2018-01-01 00:00:00.000', value),Value,* FROM [dbo].[DateRange]('2018-01-01 00:00:00.000', '2018-01-01 00:00:00.00001', 'ns', 100)
    -- **********************************************************************/
    ALTER FUNCTION [dbo].[DateRange]
    (
    @StartDate datetime2,
    @EndDate datetime2,
    @DatePart nvarchar(3)='dd',
    @Interval int=1
    )
    RETURNS TABLE AS RETURN
    WITH A(A) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) A(A)),
    B(RowNum) AS (SELECT TOP(ABS(CASE @DatePart
    WHEN 'ns' THEN DATEDIFF(ns, @EndDate, @StartDate)/@Interval
    WHEN 'mcs' THEN DATEDIFF(mcs,@EndDate, @StartDate)/@Interval
    WHEN 'ms' THEN DATEDIFF(ms, @EndDate, @StartDate)/@Interval
    WHEN 'ss' THEN DATEDIFF(ss, @EndDate, @StartDate)/@Interval
    WHEN 'mi' THEN DATEDIFF(mi, @EndDate, @StartDate)/@Interval
    WHEN 'hh' THEN DATEDIFF(hh, @EndDate, @StartDate)/@Interval
    WHEN 'dd' THEN DATEDIFF(dd, @EndDate, @StartDate)/@Interval
    WHEN 'ww' THEN DATEDIFF(ww, @EndDate, @StartDate)/@Interval
    WHEN 'mm' THEN DATEDIFF(mm, @EndDate, @StartDate)/@Interval
    WHEN 'qq' THEN DATEDIFF(qq, @EndDate, @StartDate)/@Interval
    WHEN 'yy' THEN DATEDIFF(yy, @EndDate, @StartDate)/@Interval
    ELSE DATEDIFF(dd, IIF(@StartDate < @EndDate, @StartDate, @EndDate), IIF(@StartDate < @EndDate, @EndDate, @StartDate))/@Interval
    END) + 1)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
    FROM A A, A B, A C, A D, A E, A F, A G, A H) -- A maximum of 16^8 (or 2^32) rows could be returned from this inline tally
    SELECT CASE @DatePart
    WHEN 'ns' THEN DATEADD(ns, T.AddAmount, @StartDate)
    WHEN 'mcs' THEN DATEADD(mcs,T.AddAmount, @StartDate)
    WHEN 'ms' THEN DATEADD(ms, T.AddAmount, @StartDate)
    WHEN 'ss' THEN DATEADD(ss, T.AddAmount, @StartDate)
    WHEN 'mi' THEN DATEADD(mi, T.AddAmount, @StartDate)
    WHEN 'hh' THEN DATEADD(hh, T.AddAmount, @StartDate)
    WHEN 'dd' THEN DATEADD(dd, T.AddAmount, @StartDate)
    WHEN 'ww' THEN DATEADD(ww, T.AddAmount, @StartDate)
    WHEN 'mm' THEN DATEADD(mm, T.AddAmount, @StartDate)
    WHEN 'qq' THEN DATEADD(qq, T.AddAmount, @StartDate)
    WHEN 'yy' THEN DATEADD(yy, T.AddAmount, @StartDate)
    ELSE DATEADD(dd, T.AddAmount, @StartDate)
    END [Value]
    FROM B
    CROSS APPLY(VALUES (IIF(@StartDate<@EndDate, @interval*RowNum, @interval*-RowNum))) T(AddAmount)
    GO

    https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

    Install that, the SQL will then need to look something like this:

    SELECT t.CustNo, t.Net, u.Value StartDt, DATEADD(mm, 1, u.Value) DtEndDt
    FROM #t t
    CROSS APPLY [dbo].[DateRange](t.StartDt, DATEADD(mm,-1,t.EndDt), 'mm', 1) u

     

  • Wouldn't it have been nice if MS had made the "datepart" of all these temporal functions so they could have take a variable?  Lordy.

    I do like that code, Jonathan.

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

  • @rjjh78,

    Did any of those solutions help you or is there something else that you need help with on this problem?

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

Viewing 6 posts - 1 through 6 (of 6 total)

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