Link to home
Start Free TrialLog in
Avatar of al4629740
al4629740Flag for United States of America

asked on

Get total number of days for entire period

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_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] order by Committee

Open in new window

Avatar of chaau
chaau
Flag of Australia image

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) p
UNPIVOT
   ([day] FOR Activity IN 
      ([1], [2], [3], repeat it till [31])
)AS Attendance;

Open in new window


When you get the data, you will be able to save it as a view, and run normal select statement on it
You can summerize data Using ROLLUP.

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 ROLLUP ( activity, Committee,[month] ) order by Committee 

Open in new window


More on rollup can be found here:
http://msdn.microsoft.com/en-us/library/ms189305(v=sql.90).aspx
Can also use the aggregate windows function ie :

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_Activity
FROM  cte_AttendanceGrid
order by Committee 

Open in new window

Avatar of al4629740

ASKER

mark  i get this message

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.
OK, I didnt do the CUBE() or ROLLUP(), but it does raise an interesting question...

What version of SQL are you using ?

You can try in a new query window :

select @@version
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)
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Can I just use this query now without having to change anything?
Yep... (assuming it is SQL 2005 ie 90)
wow.  I wish I knew exactly how that worked....

Looks good
*laughing* you can still ask...

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...

Have a read of OVER clause: http://msdn.microsoft.com/en-us/library/ms189461(v=sql.105).aspx
And for CTE : http://msdn.microsoft.com/en-us/library/ms175972(v=sql.105).aspx