Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Spreading a value across range of months

Author  Topic 

Jimbob
Starting Member

2 Posts

Posted - 2013-06-13 : 13:34:57
Hi

I have a table of travel pass data, what I want to do is create a new table (or view) in which the costs of each travel pass is spread evenly over the period of the pass with the result having an entry for each month that this pass is valid.

The source table is as follows:

PKID PurchaseDate PurchaseCost PurchasePeriod
1234 2013-04-07 300 3
1235 2012-05-04 100 4

(The purchase period is months)

The resulting table would look something like this:

PKID Month Year Cost
1234 4 2013 100
1234 5 2013 100
1234 6 2013 100
1235 5 2012 25
1235 6 2012 25
1235 7 2012 25
1235 8 2012 25

Thanks in advance for your help

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-13 : 14:53:10
Are you looking for a query to return result as given "resulting table would look something like this" from the source table's data? If so, can you explain the logic in words how the record PKID 1234 of the source table is turning into three rows with cost of 100 and month 4,5,6; and the PKID 1235 of the source table into four records with cost of 25 and months as 5,6,7,8?

Cheers
MIK
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-13 : 15:52:20
[CODE]


DECLARE @Tickets TABLE (PKID INT, PurchaseDate Date, PurchaseCost INT, PurchasePeriod INT)
INSERT INTO @Tickets VALUES
(1234, '2013-04-07', 300, 3),
(1235, '2012-05-04', 100, 4);


WITH CTE AS
(SELECT PKID, DATEPART(month, PurchaseDate) as StartMonth, DATEPART(year, PurchaseDate) as [Year], PurchasePeriod, PurchaseCost from @Tickets)
SELECT PKID, number as [MONTH], [Year], (PurchaseCost/PurchasePeriod) As Cost from CTE T, master..spt_values where type = 'p' and
number between StartMonth and StartMonth + PurchasePeriod - 1;



[/CODE]
Go to Top of Page

Jimbob
Starting Member

2 Posts

Posted - 2013-06-13 : 16:52:23
Brilliant! problem solved, thanks very much for your help!

Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-13 : 19:36:45
Glad to help.
Go to Top of Page
   

- Advertisement -