CODEWITHSUNDEEP
sql-server-2008
user1796443
user1796443

Reputation: 33

SQL Add Sum Row for Week and At the End Add the Grand Total

I have a table which has Columns Like numberofCalls, Date, CallsNotRecieved etc.

I am in need of a way to get this records for a month but at the end of each Saturday i have to Show the week total and after all i have to show the Grand Total for the Month.

Is it posible in SQL server? Any Help?

Upvotes: 3

Views: 461

Answers (1)

AdamL
AdamL

Reputation: 13181

You should give us more information on data structure. Assuming that your [Date] column is of type datetime, and that you want all your date/week total/month total in one column:

set datefirst 7

select 
    case 
        when grouping(cast(datepart(week, [Date]) as varchar(255)))=1 then '<monthtotal>'       
        when grouping(cast([Date] as date))=1 then '<weektotal>'        
        else cast(cast([Date] as date) as varchar(255))
    end as Period
    ,CallsNotRecieved = sum(CallsNotRecieved)
    ,NumberOfCalls = sum(numberofCalls)
from <yourtable>
group by 
    grouping sets(  
    (cast(datepart(month, [Date]) as varchar(255)), cast(datepart(week, [Date]) as varchar(255)),cast([Date] as date)),
    (cast(datepart(month, [Date]) as varchar(255)), cast(datepart(week, [Date]) as varchar(255))),
    (cast(datepart(month, [Date]) as varchar(255)))
    )

You can figure out how to do this without set datefirst, insert week/month number instead of weektotal and monthtotal, and also make sure that data set is ordered by using order by grouping(), and order by [Period]

Edit: In sql server 2005, as there's no grouping sets, one would have to use WITH ROLLUP (or worse - WITH CUBE) clause, and then filter out unneeded grouping combinations with grouping(<column_name>)=1

Upvotes: 1

Related Questions