December 22, 2015 at 3:12 am
I have a table showing assets. A payment file is produced every month showing all assets, regardless of if they need to have paid that month. I have the following fields..
'File Month' - The monthly date of the file, so 'December 2015'
'Start Month' - The month the asset started paying
'Frequency' - How often they pay (options are annually, half yearly, quarterly or monthly)
I need to work out the next Payment Month. I can get there easily for yearly (add 12 to the Start Month) and monthly(add 1 to the File Month), but stumped at the moment on how to do the quarterly and half yearly payers. I can't just add the frequency to the start month because it's dependent on what month we are in now..
E.g.
For December, for those who started paying in April and were half year payers, I'd want their next payment month to be April because they would have already paid in October
For December, for those who started paying in August and were quarterly payers, I'd want their next payment month to be Feb cause they would have paid in November.
CREATE TABLE [dbo].[AssetPayments](
[FileMonth] [int] NULL,
[AssetId] [int] NULL,
[StartMonth] [int] NULL,
[PaymentFrequencyInMonths] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)
VALUES (12, 1234, 5, 1)
INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)
VALUES (12, 5678, 10, 3)
INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)
VALUES (12, 1111, 7, 6)
INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)
VALUES (12, 2222, 6, 12)
December 22, 2015 at 3:53 am
-- I've tweaked your table definition so as to use proper dates instead of months numbers
-- Notice the constraints to keep any dodgy dates out of the table
CREATE TABLE dbo.AssetPayments(
FileMonth date NOT NULL CONSTRAINT CK_FileMonth CHECK (DATEPART(DAY,FileMonth) = 1)
,AssetId int NOT NULL
,StartMonth date NOT NULL CONSTRAINT CK_StartMonth CHECK (DATEPART(DAY,StartMonth) = 1)
,PaymentFrequencyInMonths int NOT NULL CONSTRAINT CK_Freq CHECK (PaymentFrequencyInMonths IN (1,3,6,12))
) ON [PRIMARY];
GO
INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)
VALUES ('2015-12-01', 1234, '2015-05-01', 1);
INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)
VALUES ('2015-12-01', 5678, '2015-10-01', 3);
INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)
VALUES ('2015-12-01', 1111, '2015-07-01', 6);
INSERT INTO dbo.AssetPayments(FileMonth, AssetId, StartMonth, PaymentFrequencyInMonths)
VALUES ('2015-12-01', 2222, '2015-06-01', 12);
-- I've used a permanent table here, but you can create it on the fly
-- when you run the query if you prefer
CREATE TABLE Months (
MonthStart date NOT NULL CONSTRAINT CK_MonthStart CHECK (DATEPART(DAY,MonthStart) = 1)
CONSTRAINT PK_Months_MonthStart PRIMARY KEY (MonthStart)
) ON [PRIMARY]
INSERT INTO Months VALUES
('2015-01-01'),('2015-02-01'),('2015-03-01'),('2015-04-01'),
('2015-05-01'),('2015-06-01'),('2015-07-01'),('2015-08-01'),
('2015-09-01'),('2015-10-01'),('2015-11-01'),('2015-12-01'),
('2016-01-01'),('2016-02-01'),('2016-03-01'),('2016-04-01'),
('2016-05-01'),('2016-06-01'),('2016-07-01'),('2016-08-01'),
('2016-09-01'),('2016-10-01'),('2016-11-01'),('2016-12-01')
SELECT
a.AssetID
,MIN(m.MonthStart) NextMonth
FROM AssetPayments a
JOIN Months m
ON DATEDIFF(month,a.StartMonth,m.MonthStart)%a.PaymentFrequencyInMonths = 0
WHERE m.MonthStart > CURRENT_TIMESTAMP
GROUP BY a.AssetId
John
December 22, 2015 at 5:52 am
I can't get that piece of code to work. What is the % sign in the join?
December 22, 2015 at 6:03 am
The % sign returns the remainder of a division. So 10 % 3 = 1
December 22, 2015 at 6:44 am
This will return the data with the current table design. I agree with John on correcting the data types, but I understand that sometimes is not possible. If at all possible, please change them.
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E2
)
SELECT FileMonth,
AssetId,
StartMonth,
MIN( ISNULL(NULLIF( (StartMonth + (n * p.PaymentFrequencyInMonths)) % 12, 0), 12)) PaymentMonths
FROM dbo.AssetPayments p
JOIN cteTally t ON p.PaymentFrequencyInMonths <= 12. / t.n
WHERE StartMonth + (n * p.PaymentFrequencyInMonths) > 12
GROUP BY FileMonth,
AssetId,
StartMonth
ORDER BY AssetId;
To understand what the cteTally is doing, read this: http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/
December 22, 2015 at 9:04 am
Thanks for that, I feel like I'm close with it, however I'm facing a few issues.
I have the following;
Filemonth = 10
StartMonth = 10
Frequency = 1
I'm expecting the NextPaymentMonth to be 11 because it has a frequency of 1 (monthly) and the current month is 10, so it should be 11.
However, the results from your code is showing 1?
December 22, 2015 at 9:11 am
I'm sorry, change the 12 in the where clause to use file month column.
December 22, 2015 at 9:49 am
Still doesn't seem to be working..
Filemonth = 10
StartMonth = 12
Frequency = 6
I'm expecting NextPaymentMonth to be 12, but your code result is showing 6...
December 22, 2015 at 10:08 am
If I understand the requirements correctly, something like this should work:
CREATE TABLE #test (filemonth int, startmonth int, frequency int);
INSERT INTO #test
VALUES (5,10,6),
(5,10,1),
(5,1,2),
(5,2,3),
(5,4,5),
(5,10,3),
(5,4,12),
(10,10,1),
(10,1,2),
(10,2,3),
(10,4,5),
(10,10,3),
(10,12,6);
SELECT *,
next_payment=CASE WHEN (startmonth+(frequency*((((CASE WHEN filemonth>=startmonth THEN filemonth-startmonth ELSE filemonth+12-startmonth END))/frequency)+1)))%12=0
THEN 12
ELSE (startmonth+(frequency*((((CASE WHEN filemonth>=startmonth THEN filemonth-startmonth ELSE filemonth+12-startmonth END))/frequency)+1)))%12
END
FROM #test;
DROP TABLE #test;
Cheers!
EDIT: Re-reading this I noticed I posted my first test attempt, which only worked if filemonth>=startmonth. I had fixed this, but posted the first version anyway. I've corrected that, but Doh! 🙂
January 5, 2016 at 8:32 am
Apologies for the late reply, Christmas holidays!
Jacob, that solution works absolutely perfectly! It performs with no extra overhead too. Thank you so so much. Happy new year!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply