CODEWITHSUNDEEP
sql-server-2008t-sql
user1745767
user1745767

Reputation: 1071

T-SQL select sum of each year, and forecast next year

I've got a pretty simple table with:

Date, kWh

It's got about 4 years of data.

How can I produce a result set like:

Year, Result
2010, 123211
2011, 123213
2012, 123211
2013, xxxxxx

Where xxxx is a forecast for the year.

The forecast would combine the sum of the dates in 2013 so far, added to an average kWh multiplied by the days remaining in 2013.

Upvotes: 2

Views: 1306

Answers (3)

valex
valex

Reputation: 24144

Yous should make two queries. First one is a simple group by YEAR and the second is one line information query about the last year, days left, average kWh. Then juts LEFT JOIN these tables and you will get what you want.

SQLFiddle demo

with t1 as
(
select 
       DATEPART(YEAR,[Date]) as Year,
       sum(kWh) SumKWH
from t
group by 
DATEPART(YEAR,[Date])
),
t2 as
(
select max(DATEPART(YEAR,[Date])) as MaxYear,
       AVG(kWH) as AverageKWH,
       DATEDIFF(DAY,max([Date]),
       CAST( CAST(max(DATEPART(YEAR,[Date])) as varchar(4))+'-12-31' as datetime)
                ) DaysLeft

from t
  )
select t1.YEAR, 
t1.SumKWH+ISNULL(AVERAGEKWH*DAYSLEFT,0)
from t1 
left join t2 on (t1.Year=t2.MaxYear)
order by t1.YEAR;

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

This will calculate projected use correctly with leap years. As you can see, the leap year calculation takes more logic than the rest. Since the projection comes closer as the year progresses, then the whole year has passed, "Projected" shows actual use.

SELECT DATEPART(YEAR, [Date]) year, SUM(kWh) * 
  (365 + ISDATE(CAST(DATEPART(YEAR, [DATE]) AS char(4)) + '0229')) / 
  COUNT(kWh) Projected
FROM readings
GROUP BY DATEPART(YEAR, [Date])

A simple SQLfiddle to test with.

To make the average count over multiple years, the query needs to be modified a little;

WITH average AS (SELECT AVG(kWh) kWh FROM readings)
SELECT DATEPART(YEAR, a.[Date]) year, SUM(a.kWh) + AVG(b.kWh) *
  ((365 + ISDATE(CAST(DATEPART(YEAR, a.[DATE]) AS char(4)) + '0229')) -
  COUNT(a.kWh)) Projected
FROM readings a, average b
GROUP BY DATEPART(YEAR, a.[Date])

This uses a cte to calculate the average to use it later in the query.

Another SQLfiddle.

Upvotes: 1

ljh
ljh

Reputation: 2604

Assume every year has 365 days, certainly not true...

select Datepart(YEAR,[Date]) as [Year],
           case when Datepart(YEAR,[Date]) = Datepart(YEAR, getdate()) 
        then  365*Avg(Kwh)
        else  SUM(Kwh) end as TotalKwh
    from table_1
    group by Datepart(YEAR,[Date])

Upvotes: 0

Related Questions