I use the following sql string to get # of days that have activity in my attendance grid for each month per activity and committee.

How do I change this query to get only the TOTAL # of days that have activity for the whole period from July to December for each activity and committee?

It seems that there needs to be a way to loop through each month and add it up?

SELECT committee,Activity,[month],CASE WHEN SUM( [1]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [2]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [3]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [4]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [5]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [6]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [7]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [8]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [9]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [10]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [11]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [12]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [13]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [14]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [15]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [16]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [17]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [18]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [19]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [20]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [21]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [22]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [23]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [24]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [25]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [26]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [27]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [28]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [29]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [30]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM([31]) > 0 THEN 1 ELSE 0 END AS #Days_With_ActivityFROM AttendanceGridwhere Fiscal = 2013 and Total > 0 and not Committee like 'ivpa%' and Month IN ('July', 'August', 'September', 'October', 'November','December')GROUP BY activity, Committee,[month] order by Committee

do you actually have the table AttendanceGrid that looks like this? I do not envy you then. What a mess.

You should consider to re-design your data structure.

AttendanceGrid looks to me like a PIVOT view generated on some table (I guess its name is Attendance). If you have this table, use it directly, it will be much easier.

Otherwise, you need to use UNPIVOT to normalise your data.

So, to unpivot your grid you would use this query:

SELECT committee,Activity,[month], [day]FROM (SELECT committee,Activity,[month], [1], [2], [3], repeat it till [31] FROM AttendanceGrid) pUNPIVOT ([day] FOR Activity IN ([1], [2], [3], repeat it till [31]))AS Attendance;

SELECT committee,Activity,[month],CASE WHEN SUM( [1]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [2]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [3]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [4]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [5]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [6]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [7]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [8]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [9]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [10]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [11]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [12]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [13]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [14]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [15]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [16]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [17]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [18]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [19]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [20]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [21]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [22]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [23]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [24]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [25]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [26]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [27]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [28]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [29]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [30]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM([31]) > 0 THEN 1 ELSE 0 END AS #Days_With_ActivityFROM AttendanceGridwhere Fiscal = 2013 and Total > 0 and not Committee like 'ivpa%' and Month IN ('July', 'August', 'September', 'October', 'November','December')GROUP BY ROLLUP ( activity, Committee,[month] ) order by Committee

sum(#days_with_Activity) over (partition by committee, activity) as Tot#Days_With_Activity

You can change the "partition by committee, activity" but still need the () ie

sum(#days_with_Activity) over () as GrandTot#Days_With_Activity

But it will repeat on every line. And then you need to use a CTE or Subquery for your current code.

For example :

;with cte_AttendanceGrid as( SELECT committee,Activity,[month], CASE WHEN SUM( [1]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [2]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [3]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [4]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [5]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [6]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [7]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [8]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [9]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [10]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [11]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [12]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [13]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [14]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [15]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [16]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [17]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [18]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [19]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [20]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [21]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [22]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [23]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [24]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [25]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [26]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [27]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [28]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [29]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [30]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM([31]) > 0 THEN 1 ELSE 0 END AS #Days_With_Activity FROM attendancegrid where Fiscal = 2013 and Total > 0 and not Committee like 'ivpa%' and Month IN ('July', 'August', 'September', 'October', 'November','December') GROUP BY activity, Committee,[month] )SELECT committee,Activity,[month],#Days_With_Activity, sum(#days_with_Activity) over (partition by committee, activity) as Tot#Days_With_ActivityFROM cte_AttendanceGridorder by Committee

Msg 10707, Level 15, State 1, Line 6
The CUBE() and ROLLUP() grouping constructs are not allowed in the current compatibility mode. They are only allowed in 100 mode or higher.

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

Thats interesting. SQL Server 2008 is compatability mode 100. So, the Server is SQL 2008 and that is a good thing.

But your Database sounds like it came from an earlier version, so the database compatability level will be less than 100.

CUBE MERGE ROLLUP are three reserved words that were introduced with SQL 2008.

Now, the good news is you can change that, but there are a couple of implication depending on where you are coming from.

So, in SSMS, please right click on the database name and go into properties. A new window will pop-up. On the left hand side is a list of "pages", there you should see "Options" in that list and click on "Options". Now on the right hand side, toward the top, there is a drop down for compatability level - what does that currently show ?

You can use that drop down to select the SQL 2008 compatability level 100

Now, some more good news, the query I wrote will work in SQL Server 2005 compatability mode 90.

;with cte_AttendanceGrid as( SELECT committee,Activity,[month], CASE WHEN SUM( [1]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [2]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [3]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [4]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [5]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [6]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [7]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [8]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [9]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [10]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [11]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [12]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [13]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [14]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [15]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [16]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [17]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [18]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [19]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [20]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [21]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [22]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [23]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [24]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [25]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [26]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [27]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [28]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [29]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM( [30]) > 0 THEN 1 ELSE 0 END + CASE WHEN SUM([31]) > 0 THEN 1 ELSE 0 END AS #Days_With_Activity FROM attendancegrid where Fiscal = 2013 and Total > 0 and not Committee like 'ivpa%' and Month IN ('July', 'August', 'September', 'October', 'November','December') GROUP BY activity, Committee,[month] )SELECT committee,Activity,[month],#Days_With_Activity, sum(#days_with_Activity) over (partition by committee, activity) as Tot#Days_With_ActivityFROM cte_AttendanceGridorder by Committee

It uses a CTE query. CTE stands for Common Table Expression and basically mean you can give a name to a select query so you can then subsequently refer to it by name.

So, it takes on the form :

with <my cte name> as
( select goes here )
select * from <my cte name>

Now, it gets more involved than that because one of the big benefits is recursion. In the ( select goes here ) you can also use the CTE.

Before CTE we would probably write as a traditional subquery e.g.

select sq.*
from (select * from my_data) as sq

But once you get used to CTE it is very hard to go back to the traditional subquery.

Now, those aggregate windows functions... We know about sum() and count() etc as aggregates, but when you add the OVER() clause it becomes a windows function. There are quite a few of them including row_number().

The advantage is you dont have to group by to use them. They pretty much define the grouping themselves by using a "partition" within the OVER() clause.

so, we end up with with a function then the "over (partition by col1,col2 order by col4,col3)" except that an aggregate function cannot use the order by...

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL).
Depending on business requirements, a single Stored Procedure can return differeâ€¦