Asked by:
Split the records based on payment date

Question
-
Hi Team,
I have below requirement to split records into multiple records based on payment date start and end date.
declare @table table ( accountid bigint, end_date datetime, paymentamnt decimal(19,2) ) insert into @table select 1,'2002-04-05',100.23 union all select 1,'2003-05-02',123.23 union all select 1,'2006-03-22',12.22 declare @startdate table ( accountid bigint, startdate datetime ) insert into @startdate select 1,'2001-02-05' --Expected output select 1 AccountID,'2001-02-05' StartDate,'2001-12-31' EndDate,0 PaymentAmnt union all select 1,'2002-01-01','2002-04-04',0 union all select 1,'2002-04-05','2002-12-31',100.23 union all select 1,'2003-01-01','2003-05-01',0 union all select 1,'2003-05-02','2003-12-31',123.23 union all select 1,'2004-01-01','2004-12-31',0 union all select 1,'2005-01-01','2005-12-31',0 union all select 1,'2006-01-01','2006-03-22',12.22
Start date is coming from a table.
In above example start date is 2001-02-05 and there is no payment received till 2002-04-05 so the records should be split into two
- From startdate of the table till end date of that year (2001-02-05 -> 2001-12-31)
- From 2002-01-01 till 2002-04-04 (payment date - 1)
The split is based on number of years difference between last payment date till next payment.
There is three years difference between 2nd and 3rd payments so the records should be split into 4 records.
Appreciate your inputs.
Thanks,
Ram.
Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.
Friday, July 22, 2016 10:16 AM
All replies
-
It is unclear what you are trying to achieve, looks like you need a calendar table to reference to the both tables
It is unclear why it should two rows if the dates are not match, is it quarter requirement?
select * from @startdate s
cross apply (select * from @table t where s.startdate <=t.end_date) as der
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
- Edited by Uri DimantMVP, Editor Friday, July 22, 2016 12:57 PM
Friday, July 22, 2016 12:57 PMAnswerer -
>> I have below requirement to split records [sic] into multiple records [sic] based on payment date start and end date.<<
Did you know that rows are not records and the table must have a key? An identifier such as an account number, cannot be a numeric data type; but just go ahead and ignore this design error.
Which a real design error is not knowing anything about temporal data in SQL. Download a free copy (PDF) of the Rick Snodgrass book on temporal queries in SQL. You have to use (start_timestamp, end_timestamp) pairs in your table to have a valid design. It looks like you are trying to kludge your initial mistake and it is going to hurt..
You also failed to use ANSI/ISO standard insertion statements. We have not used that old Sybase syntax you had for a few decades now.
CREATE TABLE Foobar
(account_id BIGINT NOT NULL,
end_date DATE NOT NULL,
PRIMARY KEY (account_id, end_date),
payment_amnt DECIMAL (19,2) NOT NULL);
INSERT INTO Foobar
VALUES
(1, '2002-04-05', 100.23),
(1, '2003-05-02', 123.23),
(1, '2006-03-22', 12.22);
CREATE TABLE Date_List
(account_id BIGINT NOT NULL PRIMARY KEY,
start_date DATE NOT NULL);
INSERT INTO Date_list
VALUES
(1, '2001-02-05');
I am with Uri; I cannot figure out what you are trying to do. My best guess is that you need a report periods table of some kind. But I am quite sure you need to get the start and end dates in one table.--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
Friday, July 22, 2016 7:41 PM -
HI Uri,
Thanks for the response.
Below is my query have added more data with explanation ideally the start date and end date of each record should be start date from the UDP/payment date to next payment date -1 unless the difference between two dates is not in the same year. If the difference between two dates is not in the same year then it should be splited based on years and payment amount should be 0 for that combination:
In below example the query start date is coming from @startdate table 2001-02-05 and the first payment is received on 2002-04-05 since it is not the same year it should be split into two records 2001-02-05 to 2001-12-31 and 2002-01-01 to 2002-04-04 (first payment date - 1). Second payment happened on 2002-05-21 so third record will be 2002-04-05 to 2002-05-20(second payment date -1)...
Third payment happened on 2003-05-21 so the records should be split into 2002-05-21 to 2002-12-31 (as the payment is not happened in 2002) and 2003-01-01 to 2003-05-01.
Fourth payment happened on 2006-03-22 so the split between third and fourth payments will be based on no of years difference.
2003-05-02 to 2003-12-31, 2004-01-01 to 2004-12-31, 2005-01-01 to 2005-12-31 and 2006-01-01 to 2006-03-21.
declare @table table ( accountid bigint, end_date datetime, paymentamnt decimal(19,2) ) insert into @table select 1,'2002-04-05',100.23 union all select 1,'2002-05-21',13.23 union all select 1,'2003-05-02',123.23 union all select 1,'2006-03-22',12.22 declare @startdate table ( accountid bigint, startdate datetime ) insert into @startdate select 1,'2001-02-05'
Hope that give more clarification.
Appreciate your inputs.
Regards,
Ram.
- Edited by Eswararao C Sunday, July 24, 2016 6:35 AM
Sunday, July 24, 2016 6:34 AM -
Uri,
Any inputs?
Regards,
Eshwar.
Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.
Monday, July 25, 2016 4:44 AM -
Start with this idea
select startdate,enddt,case when startdate=enddt2 then paymentamnt else 0 end paymentamnt from
(
select
case when year(dt)<year(enddt) then startdate else case when dt > enddt then enddt2 else dt end end startdate,
case when year(dt) <year(enddt) then dt else case when dt>enddt then dt else enddt end end enddt,enddt2,
paymentamnt
from @startdate s
cross apply (select * from @table t where s.startdate <t.end_date and end_date<'20030101') as der
cross apply (select * from @calendar) as der1
cross apply (values(dateadd(d,-1,der.end_date))) as der2 (enddt)
cross apply (values(dateadd(d,0,der.end_date))) as der3 (enddt2)
) as der
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Monday, July 25, 2016 6:48 AMAnswerer