December 10, 2018 at 6:38 pm
I have below table with columns date and Number of Payments and Next Payment Date column is what i need to findout (expected output)
Every month payments will be done on 15th and 1st of every month so based on number of payments i have to findout the next payment date, So can you please help me with this requirement, I am New bee .
Date |Number of Payments | Next Payment Date
2018-12-03 |2 | 2019-01-01
2018-09-30 |3 | 2018-11-01
December 10, 2018 at 6:52 pm
use a tally/numbers table and DATEADD. Maybe I should add a link to Jeff Moden's article on Tally tables.
use the numbers table and join your table to it...
SELECT myTable.StartDate, DATEADD(month, myTable.NumMonths, myTable.StartDate) AS DueDate
FROM myTable mt INNER JOIN Tally t ON t.N<=mt.NumMonths
how about...CREATE TABLE #PmtSchedule (
LoanID INT IDENTITY
,StartDate DATE
,NumPayments TINYINT );
GO
INSERT INTO #PmtSchedule (StartDate, NumPayments)
VALUES ('2018-12-03', 2),('2018-09-30', 3);
CREATE TABLE Tally (n INT PRIMARY KEY);
INSERT INTO Tally (n) VALUES (1),(2),(3);
SELECT DATEADD(month, t.N, DATEDIFF(m, 0, GETDATE())) AS DueDate
, LoanID
, StartDate
, NumPayments
, t.N
-- Get first of month
, DATEADD(month, t.N, DATEADD(m, DATEDIFF(m, 0, StartDate), 0)) AS NextDueDate
FROM #PmtSchedule s
INNER JOIN Tally t ON t.N <= s.NumPayments
ORDER BY LoanID, t.N;
...
December 17, 2018 at 1:42 pm
ramrajan - Monday, December 10, 2018 6:38 PMI have below table with columns date and Number of Payments and Next Payment Date column is what i need to findout (expected output)Every month payments will be done on 15th and 1st of every month so based on number of payments i have to findout the next payment date, So can you please help me with this requirement, I am New bee .
Date |Number of Payments | Next Payment Date
2018-12-03 |2 | 2019-01-01
2018-09-30 |3 | 2018-11-01
Just an fyi, but the scenario provided is not at all realistic, as there's not really enough information to properly and accurately solve the problem. What would happen if someone missed 4 payments, but then made 2 payments, and that data was recorded. A solution to this alone without any consideration of existing past due amounts would be woefully inadequate, and not the least bit relevant to the real world for that reason. This looks and sounds like homework. That is something you really should try to do for yourself, and then only post where you got stuck, including details on exactly how far you got and what specifically tripped you up. Just claiming to be a newbie isn't enough to get someone else to do your homework for you.
One additional note: with the payments due on the first or 15th, the one other reply you have is not going to work when the number of payments is one, as is won't correctly calculate the next due date as the 15th. It does provide an example, however, but as it makes no mention of the 15th, nor does it even attempt to code for it, you'll have to figure that stuff out on your own, but you can use pieces of that as a starting point for a solution.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
December 17, 2018 at 5:25 pm
ramrajan - Monday, December 10, 2018 6:38 PMI have below table with columns date and Number of Payments and Next Payment Date column is what i need to findout (expected output)Every month payments will be done on 15th and 1st of every month so based on number of payments i have to findout the next payment date, So can you please help me with this requirement, I am New bee .
Date |Number of Payments | Next Payment Date
2018-12-03 |2 | 2019-01-01
2018-09-30 |3 | 2018-11-01
Ummm... Ok... that confuses the hell out of me... Your example shows payment Dates and Next Payment Dates with dates that are not on the 15th and 1st of every month. Can you provide an example that actually follows your requirements, please?
And, yeah... since you're a newbie, this sounds like a homework problem.
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply