June 20, 2014 at 4:59 am
Hi,
I need the script for the following schedule.
if the repayment start date = 05-01-2016 and installment=5 then result will be
05-01-2016
05-02-2016
05-03-2016
05-04-2016
05-05-2016
if the repayment start date = 30-01-2016 and installment=5 then result will be
30-01-2016
29-02-2016
30-03-2016
30-04-2016
30-05-2016
if the repayment start date = 31-01-2016 and installment=5 then result will be
31-01-2016
29-02-2016
31-03-2016
30-04-2016
31-05-2016
if the repayment start date = 29-02-2016 and installment=5 then result will be
29-02-2016
31-03-2016
30-04-2016
31-05-2016
30-06-2016
if the repayment start date = 28-02-2015 and installment=5 then result will be
28-02-2015
31-03-2015
30-04-2015
31-05-2015
30-06-2015
I have find some ways but unable to make it.
Please help me for this.
June 20, 2014 at 7:05 am
please provide same Temp table and sample data.
clear give the exactly your expected values.
June 20, 2014 at 8:07 pm
panneermca35 (6/20/2014)
Hi,I need the script for the following schedule.
if the repayment start date = 05-01-2016 and installment=5 then result will be
05-01-2016
05-02-2016
05-03-2016
05-04-2016
05-05-2016
if the repayment start date = 30-01-2016 and installment=5 then result will be
30-01-2016
29-02-2016
30-03-2016
30-04-2016
30-05-2016
if the repayment start date = 31-01-2016 and installment=5 then result will be
31-01-2016
29-02-2016
31-03-2016
30-04-2016
31-05-2016
if the repayment start date = 29-02-2016 and installment=5 then result will be
29-02-2016
31-03-2016
30-04-2016
31-05-2016
30-06-2016
if the repayment start date = 28-02-2015 and installment=5 then result will be
28-02-2015
31-03-2015
30-04-2015
31-05-2015
30-06-2015
I have find some ways but unable to make it.
Please help me for this.
I believe the last two examples are incorrect (especially based on your 2nd example) and didn't code for them. But, you're the one with the need. If the following doesn't do it for you, let me know and we'll swing a change.
As usual, details are in the comments in the code.
CREATE FUNCTION dbo.MonthlyInstallmentSchedule
/**********************************************************************************************************************
Purpose:
Given a start date and the number of monthly installments to make where the start date is the date of the first
monthly installment, return a list of monthly installment dates and the installment number up to 10,000 installments.
Programmer Notes:
1. Suitable for use with SQL Server 2005 and up.
2. @pInstallments must be >= 0 or a TOP error will occur.
3. Times will be stripped from the @pStartDate.
4. Works with SET DATEFORMAT but is totally optional.
5. This is a high-performance iTVF and won't bog a system down like a scalar or mTVF funtion will.
Usage Examples:
--===== Basic syntax
SELECT InstallmentNumber
,InstallmentDate
FROM dbo.MonthlyInstallmentSchedule(@pStartDate,@pInstallments)
;
--===== Working example (with dmy format)
SET DATEFORMAT dmy --Not required if dmy is the default
;
SELECT InstallmentNumber
,InstallmentDate
FROM dbo.MonthlyInstallmentSchedule('31-01-2016',5)
;
--===== Working example (with mdy format)
SET DATEFORMAT mdy --Not required if mdy is the default
;
SELECT InstallmentNumber
,InstallmentDate
FROM dbo.MonthlyInstallmentSchedule('01-31-2016',5)
;
-----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 20 Jun 2014 - Jeff Moden - Initial creation and unit test.
**********************************************************************************************************************/
--===== Declare the I/O for the function
(
@pStartDate DATETIME
,@pInstallments INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
WholeDate AS (SELECT StartDate = DATEADD(dd,DATEDIFF(dd,'17530101',@pStartDate),'17530101')) --Convert to whole date
,E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) --10 rows
,E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10 thousand rows
,Tally(N) AS (SELECT TOP(@pInstallments) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4) --Create number sequence
SELECT InstallmentNumber = t.N
,InstallmentDate = DATEADD(mm,t.N-1,d.StartDate)
FROM WholeDate d
CROSS JOIN Tally t
;
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2014 at 8:13 pm
kbhanu15 (6/20/2014)
please provide same Temp table and sample data.clear give the exactly your expected values.
Your heart is definitely in the right place but look back at the problem... The OP gave exactly what the inputs are and what the outputs should be for each of the simple inputs. Since this is supposed to generate data from the inputs, there's no need for readily consumable test data.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2014 at 8:24 pm
Ah... looking back at the requirements, I get it now. The last two examples are based on the end of the month and that's what you're trying to return for those.
I'll see what I can do without slowing this code down too much.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2014 at 9:49 pm
I believe this will do it but you should test a broader range of dates to make sure it works. Look for "Rev 01" to see the changes I made.
drop function dbo.MonthlyInstallmentSchedule
go
CREATE FUNCTION dbo.MonthlyInstallmentSchedule
/**********************************************************************************************************************
Purpose:
Given a start date and the number of monthly installments to make where the start date is the date of the first
monthly installment, return a list of monthly installment dates and the installment number up to 10,000 installments.
If the start date is the end of the month, then all dates returned will be the end of the month. (Rev 01)
Programmer Notes:
1. Suitable for use with SQL Server 2005 and up.
2. @pInstallments must be >= 0 or a TOP error will occur.
3. Times will be stripped from the @pStartDate.
4. Works with SET DATEFORMAT but is totally optional.
5. This is a high-performance iTVF and won't bog a system down like a scalar or mTVF funtion will.
Usage Example:
--===== Basic syntax
SELECT InstallmentNumber
,InstallmentDate
FROM dbo.MonthlyInstallmentSchedule(@pStartDate,@pInstallments)
;
--===== Working example (with dmy format)
SET DATEFORMAT dmy --Not required if dmy is the default
;
SELECT InstallmentNumber
,InstallmentDate
FROM dbo.MonthlyInstallmentSchedule('31-01-2016',5)
;
--===== Working example (with mdy format)
SET DATEFORMAT mdy --Not required if mdy is the default
;
SELECT InstallmentNumber
,InstallmentDate
FROM dbo.MonthlyInstallmentSchedule('02-29-2016',5)
;
-----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 20 Jun 2014 - Jeff Moden - Initial creation and unit test.
Rev 01 - 20 Jun 2014 - Jeff Moden - Add end-of-month sensitivity.
**********************************************************************************************************************/
--===== Declare the I/O for the function
(
@pStartDate DATETIME
,@pInstallments INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
WholeDate AS (SELECT StartDate = DATEADD(dd,DATEDIFF(dd,'1753',@pStartDate),'1753')) --Convert to whole date
,DateInfo AS (SELECT StartDate
,IsMonthEnd = CASE --(Rev 01)
WHEN DATEADD(dd,1,StartDate)=DATEADD(mm,DATEDIFF(mm,'1753',StartDate)+1,'1753')
THEN 1
ELSE 0
END
FROM WholeDate)
,E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) --10 rows
,E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10 thousand rows
,Tally(N) AS (SELECT TOP(@pInstallments) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4) --Create number sequence
SELECT InstallmentNumber = t.N
,InstallmentDate =
CASE --If start date is end of month, always use end of month else use exact or closest date. (Rev 01)
WHEN IsMonthEnd = 1
THEN DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,'1753',d.StartDate)+t.N,'1753'))
ELSE DATEADD(mm,t.N-1,d.StartDate)
END
FROM DateInfo d
CROSS JOIN Tally t
;
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2014 at 4:23 am
Dear Jeff Moden,
Thank u so much for your solution. i have shared this with my team they also surprised for your solution. Really thank you so much. :Wow:
June 27, 2014 at 8:33 am
Dear Jeff,
Thanks for solution.
I need one more modification.
Now we prepared the schedule for monthly basis. if i want to generate the schedule in quarterly basis.
Example: 1
No of months:5
Period : Monthly
Start Date : 01-05-2015
Schedule :
01-05-2015
01-06-2015
01-07-2015
01-08-2015
01-09-2015
Example: 2
No of months:5
Period : Quarterly
Start Date : 01-05-2015
Schedule :
01-05-2015
01-08-2015
01-11-2015
01-02-2016
01-05-2016
Kindly help me.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply