### Question

• Hi All,

I have a request from business to update a table based on certain business logic, appreciate if someone can help by providing appropriate SQL code to handle the issue please. ( SQL Server 2008 )

I need to update ‘factDetail’ table reading  rows in ‘factMaster’ table.

TABLE  -factMaster

 orderID StartDate EndDate TotalRefund MonthlyRefund 217 2011-11-04 2012-11-04 358 82

Based on above record in ‘factMaster’ table I need to insert rows into ‘factDetail’ table as below:

 OrderID PaidMonth PaidAmount 217 2012-11-04 82 217 2012-10-04 82 217 2012-09-04 82 217 2012-08-04 82 217 2012-07-04 30

General Explanation:

When a factMaster table row has a Total refund ( e.g. 358 ) it has be distributed  to factDetail table , based on MonthlyRefund value ( 82)

This has to be done creating rows on MONTHLY BASIS , starting from  factMaster End Date ( 2012-11-04)

So,

2012-11-04  \$82

2012-10-04  \$82 …….  Until Total Refund is paid off, but when we come up to inserting first 4 records we have come up to 328 ( i.e. 82 * 4 ),, therefore I cannot have another row as MonthlyRefund as 82 , then amount will be 328 + 82 = 410  ( why then I exceed the Total refund of 358 )  so my last record should have 358-328 = 30 ….

PSedocode Explanation  (  as per above information in ‘factMaster’ Table )

Read a row from ‘factMaster’ table

factMaster.endDate = 2012-11-04

factMaster.TotalRefund = 358

factMaster.MonthlyRefund = 82

Variable PP = 0

DO WHILE until factMaster.TotalRefund   IS PAID OFF

Insert rows in ‘factDetail’

factDetail.orderID  = factMaster.OrderID

factDetail.PaidMonth = factMaster.endDate

factDetail.PaidAmount = factMaster.MonthlyRefund

PP = PP  + factMaster.MonthlyRefund

IF  PP < factMaster.TotalRefund  AND  ( PP + factMaster.MonthlyRefund)  <   factMaster.TotalRefund  )

Insert another row

factDetail.orderID  = factMaster.OrderID

factDetail.PaidMonth = factMaster.endDate  -  1 Month

factDetail.PaidAmount = factMaster.MonthlyRefund

PP = PP + factMaster.MonthlyRefund

ELSE

Calculate the balance payment ( in this case \$30 ) and put it as last record.

Appreciate if someone can help, sorry if my explanation is not very clear.

Mira

jeudi 21 février 2013 01:10

### Réponses

• Hi,

The script below covers your requirement, although it's worth pointing out that you need to be careful around the logic used for the payment dates.  I've allowed for differences at the end of each month in this script, but you need to go through your business logic in full and ensure your final solution covers all "tricky" scenarios.

Also, test out the performance before implementing.  This script does not allow for the setup of your environment - for example you will ideally have a pre-built Tally table to use rather than generating one.

The script takes each order, and generates monthly records using the Tally table based on a calculation for the number of months required.  I'm happy to explain further if necessary.

declare @factMaster table
(
OrderID int
,StartDate date
,EndDate date
,TotalRefund money
,MonthlyRefund money
);

declare @factDetail table
(
OrderID int
,PaidMonth date
,PaidAmount money
);

insert into @factMaster
(OrderID, StartDate, EndDate, TotalRefund, MonthlyRefund)
values
(217,'2011-11-04','2012-11-04',358,82),
(218,'2010-01-07','2012-12-31',3580,82);

/*
LOGIC
=====
* NumberOfMonths = FLOOR(TotalRefund/MonthlyRefund)

* If NumberOfMonths > DATEDIFF(m,StartDate,EndDate)
Then Set NumberOfMonths = DATEDIFF(m,StartDate,EndDate)

* FirstPaymentAmount = TotalRefund - ((NumberOfMonths-1)*MonthlyRefund)

* If PaymentDay (e.g. 31st) > last day of month
Then Set PaymentDay = last day of month

*/

-- Easiest way is to use a Tally table for this (the below query generates n as 0-239)
with Tally as (
select top (240) row_number() over(order by a.number)-1 as n
from master.dbo.spt_values a
cross join master.dbo.spt_values b
where a.type = 'P'
and b.type = 'P'
)
insert into @factDetail
(OrderID, PaidMonth, PaidAmount)
select
OrderID
,case t.n
when floor(fm.TotalRefund/fm.MonthlyRefund) then fm.TotalRefund - ((floor(fm.TotalRefund/fm.MonthlyRefund))*fm.MonthlyRefund)
when datediff(m,fm.StartDate,fm.EndDate) then fm.TotalRefund - ((datediff(m,fm.StartDate,fm.EndDate))*fm.MonthlyRefund)
else fm.MonthlyRefund
end as PaidAmount
from @factMaster fm
inner join Tally t
on  floor(fm.TotalRefund/fm.MonthlyRefund) >= t.n
and datediff(m,fm.StartDate,fm.EndDate) >= t.n
order by fm.OrderID, t.n;

select * from @factMaster;
select * from @factDetail;

jeudi 21 février 2013 09:56

### Toutes les réponses

• Hi,

The script below covers your requirement, although it's worth pointing out that you need to be careful around the logic used for the payment dates.  I've allowed for differences at the end of each month in this script, but you need to go through your business logic in full and ensure your final solution covers all "tricky" scenarios.

Also, test out the performance before implementing.  This script does not allow for the setup of your environment - for example you will ideally have a pre-built Tally table to use rather than generating one.

The script takes each order, and generates monthly records using the Tally table based on a calculation for the number of months required.  I'm happy to explain further if necessary.

declare @factMaster table
(
OrderID int
,StartDate date
,EndDate date
,TotalRefund money
,MonthlyRefund money
);

declare @factDetail table
(
OrderID int
,PaidMonth date
,PaidAmount money
);

insert into @factMaster
(OrderID, StartDate, EndDate, TotalRefund, MonthlyRefund)
values
(217,'2011-11-04','2012-11-04',358,82),
(218,'2010-01-07','2012-12-31',3580,82);

/*
LOGIC
=====
* NumberOfMonths = FLOOR(TotalRefund/MonthlyRefund)

* If NumberOfMonths > DATEDIFF(m,StartDate,EndDate)
Then Set NumberOfMonths = DATEDIFF(m,StartDate,EndDate)

* FirstPaymentAmount = TotalRefund - ((NumberOfMonths-1)*MonthlyRefund)

* If PaymentDay (e.g. 31st) > last day of month
Then Set PaymentDay = last day of month

*/

-- Easiest way is to use a Tally table for this (the below query generates n as 0-239)
with Tally as (
select top (240) row_number() over(order by a.number)-1 as n
from master.dbo.spt_values a
cross join master.dbo.spt_values b
where a.type = 'P'
and b.type = 'P'
)
insert into @factDetail
(OrderID, PaidMonth, PaidAmount)
select
OrderID
,case t.n
when floor(fm.TotalRefund/fm.MonthlyRefund) then fm.TotalRefund - ((floor(fm.TotalRefund/fm.MonthlyRefund))*fm.MonthlyRefund)
when datediff(m,fm.StartDate,fm.EndDate) then fm.TotalRefund - ((datediff(m,fm.StartDate,fm.EndDate))*fm.MonthlyRefund)
else fm.MonthlyRefund
end as PaidAmount
from @factMaster fm
inner join Tally t
on  floor(fm.TotalRefund/fm.MonthlyRefund) >= t.n
and datediff(m,fm.StartDate,fm.EndDate) >= t.n
order by fm.OrderID, t.n;

select * from @factMaster;
select * from @factDetail;

jeudi 21 février 2013 09:56
• Hey Mike,,

This is fantastic, I run your code on my Test server , it worked well, Thanks,,, But I need to get my head around about this 'Tally Table' concept, this is the first time I am playing with this type of logic.... I am trying to run this query over and over and try get my head around it, at the same time appreciate if you could provide bit more explanation on this please..

Mira

mirapimbi12@hotmail.com

If you send email , put  ##TALLY TABLE ##  in Subject line please

vendredi 22 février 2013 04:03