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.

Answer 1

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

Answer 2

>> 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

Answer 3

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.

Answer 4

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.

Answer 5

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