Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count for CASE & Group By 1

Status
Not open for further replies.
gatetec

gatetec

MIS
Mar 22, 2007
420
US
I have a script and need to have the counts for A.AssociateID (to get the total emp count per dept) and vComply (which has three outputs, 'Compliant', 'Non-Compliant', 'Current'), grouped by dept.
So, the outputs I want are:

DEPT EMP_COUNT Compliant Current Non-Compliant
1S 44 12 17 15
1N 15 7 3 4
2A 21 16 ...................
..........

Please advise.
Thanks so much.

select
A.Dept,
CASE
WHEN
ANNUADateRespFCGiven BETWEEN '10/01/2008' and '12/31/2008'
and DATENAME(m,ANNUADateRespFCGiven) = RespFCMonth
and ANNUADateRespFCGiven BETWEEN DateAdd(mm,DateDiff(m,0,'2008-10-01 00:00:00'),-31)
AND DateAdd(mm,DateDiff(m,-1,'2008-12-31 23:59:59'),30)
THEN 'Compliant'
WHEN
ANNUADateRespFCGiven BETWEEN '10/01/2008' and '12/31/2008'
and DATENAME(m,ANNUADateRespFCGiven) != RespFCMonth
THEN 'Current'
ELSE 'Non-compliant'
END AS vComply
from AssociatePersonal A, MedicalINFO M
where A.AssociateID = M.AssociateID
and A.HireStatus not in ('Terminated Associates - Final', 'Term', 'Terminated Associates - Pending')
and M.RespirtoryResult not in ('Deferred', 'Exempt', 'Fail')
and CONVERT(VARBINARY, A.lname) = CONVERT(VARBINARY, UPPER(A.lname))
and A.Dept !=''
order by dept
 
Sort by date Sort by votes
Try this:

Code: 
Select A.Dept,
       Count(vComply) As EMP_COUNT,
       Sum(Case When vComply = 'Compliant' Then 1 End) As Compliant,
       Sum(Case When vComply = 'Current' Then 1 End) As [Current],
       Sum(Case When vComply = 'Non-Compliant' Then 1 End) As [Non-Compliant]
From   (
       select 
       A.Dept, 
       CASE
         WHEN 
           ANNUADateRespFCGiven BETWEEN '10/01/2008' and '12/31/2008' 
           and DATENAME(m,ANNUADateRespFCGiven) = RespFCMonth
           and ANNUADateRespFCGiven BETWEEN DateAdd(mm,DateDiff(m,0,'2008-10-01 00:00:00'),-31) 
                   AND DateAdd(mm,DateDiff(m,-1,'2008-12-31 23:59:59'),30) 
            THEN 'Compliant'
         WHEN 
           ANNUADateRespFCGiven BETWEEN '10/01/2008' and '12/31/2008' 
           and DATENAME(m,ANNUADateRespFCGiven) != RespFCMonth
         THEN 'Current'
       ELSE 'Non-compliant' 
       END AS vComply
       from AssociatePersonal A, MedicalINFO M
       where A.AssociateID = M.AssociateID 
            and A.HireStatus not in ('Terminated Associates - Final', 'Term', 'Terminated Associates - Pending')
            and M.RespirtoryResult not in ('Deferred', 'Exempt', 'Fail')
            and CONVERT(VARBINARY, A.lname) = CONVERT(VARBINARY, UPPER(A.lname))
            and A.Dept !=''
       ) As A
order by A.Dept

Notice how your original query was embedded in this one. This technique is called a derived table. This is a very power technique that you should become familiar with, especially when writing more complex queries.

If this returns the correct result, and you would like for me to explain it, let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Yes, the format works REALLY great.
After looking the outputs, I need to make two more counts there because the counts of EMP_COUNT is not representing the correct count.

In each dept, there are more counts than Count(vComply) As EMP_COUNT. Count(vComply) simply gives me the SUM of three categories. How do we get the emp_count of each dept in this query?

In addition, I need the count from
M.RespirtoryResult in ('Deferred', 'Exempt', 'Fail') as opposed to M.RespirtoryResult NOT in ('Deferred', 'Exempt', 'Fail')

So, basically, the count is from:

select dept, count (dept) as CNT_DEF
from AssociatePersonal A, MedicalINFO M
where A.AssociateID = M.AssociateID
and A.HireStatus not in ('Terminated Associates - Final', 'Term', 'Terminated Associates - Pending')
and M.RespirtoryResult in ('Deferred', 'Exempt', 'Fail')
and CONVERT(VARBINARY, A.lname) = CONVERT(VARBINARY, UPPER(A.lname))
and A.Dept !=''
group by dept
order by dept

Please advise how to get those counts.

Thanks so much
 
Upvote 0 Downvote
Count(vComply) simply gives me the SUM of three categories.
Click to expand...

I'm confused. In the derived table, all rows must fall in to one of 3 categories. Meaning, each employee is Current, compliant, or non-compliant. The query you show that returns the count has the same where clause as the derived table, so the COUNT(dept) should be the same as Count(vComply) As EMP_COUNT. I don't see how they could be different.

Also...

In addition, I need the count from
M.RespirtoryResult in ('Deferred', 'Exempt', 'Fail') as opposed to M.RespirtoryResult NOT in ('Deferred', 'Exempt', 'Fail')
Click to expand...

I would suggest that you add to the derived to get this value. Something like this...

Code: 
Select A.Dept,
       Count(*) As EMP_COUNT,
       Sum(Case When vComply = 'Compliant' Then 1 End) As Compliant,
       Sum(Case When vComply = 'Current' Then 1 End) As [Current],
       Sum(Case When vComply = 'Non-Compliant' Then 1 End) As [Non-Compliant][!],
       Count(IsrespitoryResult) As [CountOfRespitoryResult][/!]
From   (
       select 
       A.Dept, 
       CASE
         WHEN 
           ANNUADateRespFCGiven BETWEEN '10/01/2008' and '12/31/2008' 
           and DATENAME(m,ANNUADateRespFCGiven) = RespFCMonth
           and ANNUADateRespFCGiven BETWEEN DateAdd(mm,DateDiff(m,0,'2008-10-01 00:00:00'),-31) 
                   AND DateAdd(mm,DateDiff(m,-1,'2008-12-31 23:59:59'),30) 
            THEN 'Compliant'
         WHEN 
           ANNUADateRespFCGiven BETWEEN '10/01/2008' and '12/31/2008' 
           and DATENAME(m,ANNUADateRespFCGiven) != RespFCMonth
         THEN 'Current'
       ELSE 'Non-compliant' 
       END AS vComply[!],
       Case When M.RespirtoryResult in ('Deferred', 'Exempt', 'Fail') Then 1 End As IsRespirtoryResult[/!]
       from AssociatePersonal A, MedicalINFO M
       where A.AssociateID = M.AssociateID 
            and A.HireStatus not in ('Terminated Associates - Final', 'Term', 'Terminated Associates - Pending')
            and M.RespirtoryResult not in ('Deferred', 'Exempt', 'Fail')
            and CONVERT(VARBINARY, A.lname) = CONVERT(VARBINARY, UPPER(A.lname))
            and A.Dept !=''
       ) As A
order by A.Dept

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
>> In the derived table, all rows must fall in to one of 3 categories.

Yes, you are correct. I was confused with other counts.
Thanks you SO much for your GREAT help!!
 
Upvote 0 Downvote
Case When M.RespirtoryResult in ('Deferred', 'Exempt', 'Fail') Then 1 End As IsRespirtoryResult

and M.RespirtoryResult not in ('Deferred', 'Exempt', 'Fail')

I wanted to double-check the counts again. sorry :)

WHERE clause has "and M.RespirtoryResult not in ('Deferred', 'Exempt', 'Fail')"
So, wouldn't the count from -- Case When M.RespirtoryResult in ('Deferred', 'Exempt', 'Fail') Then 1 End As IsRespirtoryResult -- be ALWAYS 0 (zero)?

if conflicting each other, please advise how to correct it.
 
Upvote 0 Downvote
I completely missed that.

In that case, I suggest you remove that from the where clause, and add it as another condition for your case statement.

Now, please understand that this is becoming a relatively complex query, and I don't understand your data, so this may not be correct. But it is probably worth a shot.

Code: 
Select A.Dept,
       Count(*) As EMP_COUNT,
       Sum(Case When vComply = 'Compliant' Then 1 End) As Compliant,
       Sum(Case When vComply = 'Current' Then 1 End) As [Current],
       Sum(Case When vComply = 'Non-Compliant' Then 1 End) As [Non-Compliant],
       Count(IsrespitoryResult) As [CountOfRespitoryResult]
From   (
       select 
       A.Dept, 
       CASE
         WHEN 
           M.RespirtoryResult not in ('Deferred', 'Exempt', 'Fail')
           And ANNUADateRespFCGiven BETWEEN '10/01/2008' and '12/31/2008' 
           and DATENAME(m,ANNUADateRespFCGiven) = RespFCMonth
           and ANNUADateRespFCGiven BETWEEN DateAdd(mm,DateDiff(m,0,'2008-10-01 00:00:00'),-31) 
                   AND DateAdd(mm,DateDiff(m,-1,'2008-12-31 23:59:59'),30) 
            THEN 'Compliant'
         WHEN 
           M.RespirtoryResult not in ('Deferred', 'Exempt', 'Fail')
           and ANNUADateRespFCGiven BETWEEN '10/01/2008' and '12/31/2008' 
           and DATENAME(m,ANNUADateRespFCGiven) != RespFCMonth
         THEN 'Current'
         WHEN M.RespirtoryResult not in ('Deferred', 'Exempt', 'Fail')
          THEN 'non-Compliant'
       ELSE 'Other' 
       END AS vComply,
       Case When M.RespirtoryResult in ('Deferred', 'Exempt', 'Fail') Then 1 End As IsRespirtoryResult
       from AssociatePersonal A, MedicalINFO M
       where A.AssociateID = M.AssociateID 
            and A.HireStatus not in ('Terminated Associates - Final', 'Term', 'Terminated Associates - Pending')
            and CONVERT(VARBINARY, A.lname) = CONVERT(VARBINARY, UPPER(A.lname))
            and A.Dept !=''
       ) As A
order by A.Dept

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Case sensitive request 1
Replies
3
Views
128
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
How te filter by date ?
Replies
9
Views
201
dhookom
dhookom
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
178
grnzbra
grnzbra
peac3
  • Locked
  • Question
Derive staus date time by aligning start and end datetime
Replies
7
Views
325
Andrzejek
Andrzejek
grnzbra
  • Locked
  • Question
Is "IF EXISTS()..." better than "IF (SELECT COUNT...) > 0"
Replies
4
Views
1K
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top