SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Spreading a value across range of months
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jimbob
Starting Member

2 Posts

Posted - 06/13/2013 :  13:34:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/13/2013 :  14:53:10  Show Profile  Reply with Quote
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

547 Posts

Posted - 06/13/2013 :  15:52:20  Show Profile  Reply with Quote



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;
		 


Go to Top of Page

Jimbob
Starting Member

2 Posts

Posted - 06/13/2013 :  16:52:23  Show Profile  Reply with Quote
Brilliant! problem solved, thanks very much for your help!

Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/13/2013 :  19:36:45  Show Profile  Reply with Quote
Glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000