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.
Author |
Topic |
Jimbob
Starting Member
2 Posts |
Posted - 2013-06-13 : 13:34:57
|
HiI 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 PurchasePeriod1234 2013-04-07 300 31235 2012-05-04 100 4 (The purchase period is months)The resulting table would look something like this:PKID Month Year Cost1234 4 2013 1001234 5 2013 1001234 6 2013 1001235 5 2012 251235 6 2012 251235 7 2012 251235 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?CheersMIK |
|
|
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] |
|
|
Jimbob
Starting Member
2 Posts |
Posted - 2013-06-13 : 16:52:23
|
Brilliant! problem solved, thanks very much for your help! |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-13 : 19:36:45
|
Glad to help. |
|
|
|
|
|
|
|