ROLLING TOTAL QUERY

[LloydDobler]

I need to create a query that helps satisfy this:
I need to calculate a monthly savings...easy enough, just divide annual savings by 12. But I need to calculate the annual savings based on the month the project was implemented (DATE field). so, my annual savings for P#1 would be 12,000, whereas my annual savings for P#2 would only be 10,000. The leftover 2,000 would carry forward to 2008. That brings in my second part. I need to be able to calculate a rolling 12 month YTD savings (realized). So, need to add all P# est monthly savings for past 12 months.

Table (I've used 12,000 for all projects to make things simpler for calculating)

P# DATE annual SAVINGS
1 01/01/07 12,000
2 03/03/07 12,000
3 07/07/07 12,000
4 10/10/07 12,000
5 02/02/08 12,000
6 03/03/08 12,000


Example:

2007 est annual savings would be 12,000 (P#1) + 10,000 (P#2) + 6,000(P#3) + 3,000(P#4)= 28,000

2008 est annual savings would be 2,000 (P#2 carry over) + 6,000 (P#3 carry over) + 9,000 (P#4 carry over) + 11,000 (P#5) + 10,000 (P#6) = 38,000

YTD realized savings would be 9,000 (P#3->6 months of 07 + 3 months of 08) + 6,000 (P#4) + 2,000 (P#5) + 1,000 (P#6) = 18,000

Now...I haven't a clue where to begin to write this! I hope it makes sense! Would love some guidance or a point in the right direction. please and thanks.


"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[ESquared]

Untested:

DECLARE CalcSavings @Yr datetime
AS
SET @Yr = dateadd(yy, '19000101', Year(@Yr) - 1900)
SELECT
   AnnualSavings * 1.0000
   * CASE WHEN Date < @Yr THEN DateDiff(dd, @Yr, DateAdd(yy, 1, Date)) ELSE DateDiff(dd, Date, DateAdd(yy, 1, @Yr)) END
   / DateDiff(@Yr, DateAdd(yy, 1, @Yr) - 1)
FROM
   YearlySavings
WHERE
   Date > DateAdd(yy, -1, @Yr)
   AND Date < DateAdd(yy, 1, @Yr)

Leap years present an interesting problem because the one-day value of the annual total will be different in a leap year from a regular year. After you decide how they should be calculated for day-interval precision, you could allow for them by, say, calculating the sums as if the days for the annual total are that of the year the date of the total is in.

This query I gave you might take 183/365ths + 183/366ths of the total annual amount, which will not total up to the exact value (it will be off by 1/730th of the annual value).

 

[LloydDobler]

keep getting errors when I run stating DATEDIFF needs 3 arguments and if I take all that out, I get 'datatype NVARCHAR is invalid for argument 2 of DATEADD function'.

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[ESquared]

Yeah, some mistakes in my untested code.

CREATE TABLE #YearlySavings (
   P int identity(1,1) NOT NULL,
   Dt datetime,
   Savings decimal(12,2)
)

SET NOCOUNT ON
INSERT #YearlySavings
SELECT '01/01/07', 12000
UNION ALL SELECT '03/03/07', 12000
UNION ALL SELECT '07/07/07', 12000
UNION ALL SELECT '10/10/07', 12000
UNION ALL SELECT '02/02/08', 12000
UNION ALL SELECT '03/03/08', 12000
UNION ALL SELECT '1/1/09', 12000

If Object_id(N'tempdb..#CalcSavings', 'p') is not null drop procedure #CalcSavings
GO
CREATE PROCEDURE #CalcSavings @Yr datetime
AS
SET @Yr = dateadd(yy, Year(@Yr) - 1900, '19000101')

SELECT
   Dt,
   CASE WHEN Dt <= @Yr THEN 0 ELSE Savings END
   + CASE WHEN Dt <= @Yr THEN 1 ELSE -1 END
   * Convert(decimal(12,2), Round(Savings * DateDiff(dd, Dt, DateAdd(yy, DateDiff(yy, 0, Dt) + 1, 0))
   / DateDiff(dd, Dt, DateAdd(yy, 1, Dt)), 2))
FROM
   #YearlySavings
WHERE
   Dt > DateAdd(yy, -1, @Yr)
   AND Dt < DateAdd(yy, 1, @Yr)
GO
SET NOCOUNT OFF
EXEC #CalcSavings '20070101'
EXEC #CalcSavings '20080101'
EXEC #CalcSavings '20090101'
GO
DROP TABLE #YearlySavings
DROP PROC #CalcSavings

There. Working, tested code. Notice the method of calculation used so that the sum of the two values always adds up to exactly the right amount. Using the other method there was a possibility for a 1-cent difference: Round(6000.005, 2) + Round(5999.995, 2) = 12000.01

This code could be extended to handle arbitrary lengths of valuation. Here the assumption is that the duration of value is one year (thus spans crossing a leap day will be a slightly lower value per day). But you could use years, or days, or weeks, anything you wanted, if necessary.

And you could also ask the question for the "prorated" value for different time periods as well, rather than just whole-year periods.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]