Anmelden
 
HomeLibraryDownloadsSupportWebcasts & EventsCommunityForenBlogs
Ressourcen für IT-Professionals >
> 
> 
Data Uploading Query - Data Warehouse

Answered Data Uploading Query - Data Warehouse

  • Donnerstag, 21. Februar 2013 01:10
     
     
    Anmelden
    0

    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

     

Alle Antworten

  • Donnerstag, 21. Februar 2013 09:56
     
     Beantwortet Enthält Code
    Anmelden
    0

    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
	,dateadd(m,-t.n,fm.EndDate) as PaidMonth
	,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;


     
  • Freitag, 22. Februar 2013 04:03
     
     
    Anmelden
    0

    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