SQL Apply statement to break down cost data evenly using date range

Refer to my previous post http://wp.me/p54RZl-3e for more information how to return a list of months using function dbo.FN_Utility_GetMonths.

Consider the following script

DECLARE @MonthCost table (item_id int,StartDate DateTime,EndDate DateTime, Cost decimal(25,6))
insert into @MonthCost values (1,'2015-01-01','2015-4-01',1000)
insert into @MonthCost values (2,'2015-01-01','2015-6-01',1000)
insert into @MonthCost values (3,'2015-04-01','2015-06-01',1000)

select M.MonthYear MonthYear
,M.StartDate StartDate
,MC.item_Id
, Mc.Cost / (datediff(month,MC.StartDate,MC.EndDate)+1) Cost
from @MonthCost MC –iterate through each row to get month cost
CROSS APPLY dbo.CPS_FN_Utility_GetMonths ( MC.StartDate,MC.EndDate) M

Result
MonthYear StartDate item_Id Cost
Jan-15 2015-01-01 00:00:00.000 1 250.00000000000000000
Feb-15 2015-02-01 00:00:00.000 1 250.00000000000000000
Mar-15 2015-03-01 00:00:00.000 1 250.00000000000000000
Apr-15 2015-04-01 00:00:00.000 1 250.00000000000000000
Jan-15 2015-01-01 00:00:00.000 2 166.66666666666666666
Feb-15 2015-02-01 00:00:00.000 2 166.66666666666666666
Mar-15 2015-03-01 00:00:00.000 2 166.66666666666666666
Apr-15 2015-04-01 00:00:00.000 2 166.66666666666666666
May-15 2015-05-01 00:00:00.000 2 166.66666666666666666
Jun-15 2015-06-01 00:00:00.000 2 166.66666666666666666
Apr-15 2015-04-01 00:00:00.000 3 333.33333333333333333
May-15 2015-05-01 00:00:00.000 3 333.33333333333333333
Jun-15 2015-06-01 00:00:00.000 3 333.33333333333333333

Using cross apply allows to invoke a table valued function for each row returned by an outer table expression of a query. In the above example the table valued function dbo.FN_Utility_GetMonths is the right input while the outer table expression is the left input @MonthCost

More information regarding how to use Apply can be found in the following link.
https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s