Solved

Get total number of days for entire period

Posted on 2013-01-30
11
470 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
11 Comments
 
LVL 24

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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 5 110
ms sql stored procedure 22 77
MS SQL 2014 get SPIDs of users 6 25
SQL server 2008 SP4 29 33
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 the fundamental information of how to create a table.

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now