CODEWITHSUNDEEP
sql-server-2008-r2
TWood
TWood

Reputation: 2583

sql server - summing totals within a month/year

I am doing a sql server report that shows running daily, monthly, and yearly totals at the bottom of the report.

I figured out how to do the daily report by taking the datediff of the "timestamp" field and a getdate() and returning rows that were = 0. That way I knew the difference in dates was 0 days and thus part of the daily total.

I am running into an issue as to a good way to do this with month and year. Here is the query I have for month calculation and it is giving me way more results than it should (I have another table which i'm using to double check my calculations. I should get about 300 as my total but the query below gives me about 7400 instead. Just a little off)

     SELECT     SUM(Rc0) AS Good, 
                      SUM(Rc0 + Rc1 + Rc2 + Rc3 + Rc4 + Rc5 + Rc6 + Rc7 + Rc8 + Rc9 + Rc10 + Rc11 + Rc12 + Rc13 + Rc14 + Rc15 + Rc16 + Rc17 + Rc18 + Rc19 + Rc20 + Rc21 + Rc22 + Rc23
                       + Rc24 + Rc25 + Rc26 + Rc27 + Rc28 + Rc29 + Rc30 + Rc31 + Rc32 + Rc33 + Rc34 + Rc35 + Rc36 + Rc37 + Rc38 + Rc39 + Rc40) AS Not_Good  
FROM         someTable WHERE     (MONTH(timestamp) = MONTH(GETDATE())) and (YEAR(timestamp) = YEAR(GETDATE()))

edit: alternate form of query that is giving me same result

SELECT     SUM(Rc0) AS Good, 
                      SUM(Rc0 + Rc1 + Rc2 + Rc3 + Rc4 + Rc5 + Rc6 + Rc7 + Rc8 + Rc9 + Rc10 + Rc11 + Rc12 + Rc13 + Rc14 + Rc15 + Rc16 + Rc17 + Rc18 + Rc19 + Rc20 + Rc21 + Rc22 + Rc23
                       + Rc24 + Rc25 + Rc26 + Rc27 + Rc28 + Rc29 + Rc30 + Rc31 + Rc32 + Rc33 + Rc34 + Rc35 + Rc36 + Rc37 + Rc38 + Rc39 + Rc40) AS Not_Good
FROM         someTable
WHERE     convert(varchar(7), timestamp, 126) = convert(varchar(7), getdate(), 126)

Upvotes: 1

Views: 1199

Answers (1)

Mark Thompson
Mark Thompson

Reputation: 326

--As a generic sort of grouping including most spans.....

SELECT 'Year' = DATEPART(Yy, rowdate), 'Month' = DATEPART(Mm, rowdate), 'Week' = DATEPART(wk, rowdate), 'Total' = sum(yournumericfield)

FROM sometable

GROUP BY DATEPART(Yy, rowdate), DATEPART(Mm, rowdate), DATEPART(wk, rowdate)

ORDER BY DATEPART(Yy, rowdate), DATEPART(Mm, rowdate), DATEPART(wk, rowdate)

Upvotes: 1

Related Questions