September 11, 2021 at 10:33 am
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!
September 11, 2021 at 2:59 pm
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;
September 12, 2021 at 5:31 pm
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 #tMy 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/2019Many 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.
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
Change is inevitable... Change for the better is not.
September 12, 2021 at 6:07 pm
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)
GOhttps://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
September 13, 2021 at 4:14 am
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
Change is inevitable... Change for the better is not.
September 15, 2021 at 12:12 am
Did any of those solutions help you or is there something else that you need help with on this problem?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply