Calculate sum of hours from weekly bases?
I need to find the maximum date for that particular client, that will be treated as latest date (or current date). Then, From there I need to go back 52 weeks to 27 weeks and calculate sum(hours). And 26 Weeks to latest date (or current date) and calculate sum(hours).
ClientID StoreID period hours
1 10 2010-04-19 8.04
1 10 2010-04-20 6.24
1 10 2010-04-21 8.26
1 20 2010-04-22 7.94
1 20 2010-04-23 22.43
1 20 2010-04-24 22.99
2 5 2010-12-19 130.67
2 5 2010-12-26 159.26
2 5 2011-01-02 113.59
2 5 2011-01-09 12.66
2 8 2011-01-16 22.34
2 8 2011-01-23 11.35
Please let me know how to calculate this in SQL 开发者_开发百科to get ouput ? If you have question ask me.
It's simplest to just use two queries for this; the first to get the boundary dates, the second to compute the totals. You could roll it all into one query but I don't think it will make much of a difference.
DECLARE
@BeginDate datetime,
@MidDate datetime
SELECT
@BeginDate = DATEADD(wk, -52, MAX(period)),
@MidDate = DATEADD(wk, -25, MAX(period))
FROM TableName
WHERE ClientID = @ClientID
SELECT
SUM(CASE
WHEN period >= @BeginDate AND period < @MidDate THEN hours
ELSE 0
END) AS HoursInFirstHalf,
SUM(CASE
WHEN period >= @MidDate THEN hours
ELSE 0
END) AS HoursInLastHalf
FROM TableName
WHERE ClientID = @ClientID
精彩评论