?
Solved

Get total number of days for entire period

Posted on 2013-01-30
11
Medium Priority
?
489 Views
Last Modified: 2013-01-31
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

0
Comment
Question by:al4629740
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 38838283
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
0
 
LVL 10

Expert Comment

by:deviprasadg
ID: 38838311
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
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38839138
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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:al4629740
ID: 38839911
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38840153
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
0
 

Author Comment

by:al4629740
ID: 38841664
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)
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 38841892
Hmmmm...

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

But you need to have a read about any impacts and changing compatability levels  http://msdn.microsoft.com/en-us/library/bb510680(v=sql.100).aspx  normally it is not too much of an issue, but you need to check anyway.

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

Open in new window

0
 

Author Comment

by:al4629740
ID: 38842529
Can I just use this query now without having to change anything?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38842570
Yep... (assuming it is SQL 2005 ie 90)
0
 

Author Comment

by:al4629740
ID: 38842678
wow.  I wish I knew exactly how that worked....

Looks good
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38842728
*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
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question