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 biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Records not qualfying with CASE 1

Status
Not open for further replies.
gatetec

gatetec

MIS
Mar 22, 2007
420
US
// SQL Server 2000

In the 'DailyLog' table, there is a field called 'VisitReason'.

If 'VisitReason' contains both of 'Flu Accept' and 'Flu Vaccine' in the date range given, then I need the count AS AcceptVac.

If 'VisitReason' contains 'Flu Accept' but no 'Flu Vaccine' in the date range given, then I need the count AS AcceptNoVac.

If 'VisitReason' contains both of 'Flu Decline' and 'Flu Vaccine' in the date range given, then I need the count AS DeclineVac.

If 'VisitReason' contains 'Flu Decline' but no 'Flu Vaccine' in the date range given, then I need the count AS DeclineNoVac.

I don't have outputs from 'AcceptVac' and 'DeclineVac'. I know there are qualifying records though.

Please advise.

thx so much

select
a.Dept,
COUNT(CASE WHEN d.VisitReason ='Flu Accept'
and d.VisitReason ='Flu Vaccine'
and visitdate between '01-01-2008' and '12-31-2008' THEN 1 END)
AS AcceptVac,
COUNT(CASE WHEN d.VisitReason ='Flu Accept'
and visitdate between '01-01-2008' and '12-31-2008' THEN 1 END)
AS AcceptNoVac,
COUNT(CASE WHEN d.VisitReason ='Flu Decline'
and d.VisitReason ='Flu Vaccine'
and visitdate between '01-01-2008' and '12-31-2008' THEN 1 END)
AS DeclineVac,
COUNT(CASE WHEN d.VisitReason ='Flu Decline'
and visitdate between '01-01-2008' and '12-31-2008'
THEN 1 END)
AS DeclineNoVac,
c.Assonum
from DailyLog d, AssociatePersonal a, CountAssociate c
where d.AssociateIDLog = a.AssociateID
and Dept !=''
and c.DeptName = a.dept
and c.DeptDate = convert(varchar, GETDATE(), 101)
and CONVERT(VARBINARY, c.Deptname) = CONVERT(VARBINARY, UPPER(c.Deptname))
group by a.Dept, c.Assonum
order by a.Dept
 
Sort by date Sort by votes
[tt][blue]
COUNT(CASE WHEN [!]d.VisitReason ='Flu Accept' [/!]
and [!]d.VisitReason ='Flu Vaccine' [/!]
and visitdate between '01-01-2008' and '12-31-2008' THEN 1 END)
AS AcceptVac
[/blue][/tt]

How can VisitReason = 'Flu Accept' and 'Flu Vaccine' at the same time? It can't, so that count will always be 0.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I see your point. The table contains multiple rows like below. Please let me know how to resolve this query to get the correct counts for the 4 scenarios.

thx so much

VisitReason visitdate
Flu Vaccine 2008-11-21 00:00:00.000
Flu Vaccine 2008-11-21 00:00:00.000
Flu Vaccine 2008-11-21 00:00:00.000
Flu Decline 2008-11-21 00:00:00.000
Flu Accept 2008-11-20 00:00:00.000
Flu Accept 2008-11-17 00:00:00.000
Flu Accept 2008-11-20 00:00:00.000
Flu Accept 2008-11-14 00:00:00.000
Flu Accept 2008-11-15 00:00:00.000
Flu Decline 2008-11-19 00:00:00.000
Flu Decline 2008-11-16 00:00:00.000
Flu Decline 2008-11-20 00:00:00.000
Flu Decline 2008-11-15 00:00:00.000
Flu Decline 2008-11-19 00:00:00.000
Flu Decline 2008-11-16 00:00:00.000
.....
 
Upvote 0 Downvote
and more like this..

Flu Vaccine 2008-11-24 00:00:00.000
Flu Vaccine 2008-11-24 00:00:00.000
Flu Vaccine 2008-11-24 00:00:00.000
Flu Vaccine 2008-11-24 00:00:00.000
Flu Vaccine 2008-11-24 00:00:00.000
Flu Vaccine 2008-11-24 00:00:00.000
Flu Vaccine 2008-11-24 00:00:00.000
....
 
Upvote 0 Downvote
Sorry.. to eliminate any confusion, I am adding more here:

The table 'DailyLog' contains 4 fields as shown below.

SeqNo AssociateIDLog VisitReason VisitDate
89738 055624 Flu Vaccine 2008-11-03 00:00:00.000
89739 107714 Flu Vaccine 2008-11-03 00:00:00.000
89740 200467 Flu Vaccine 2008-11-03 00:00:00.000
89741 032292 Flu Vaccine 2008-11-03 00:00:00.000
89742 144253 Flu Vaccine 2008-11-03 00:00:00.000
89743 095216 Flu Vaccine 2008-11-03 00:00:00.000
89744 136556 Flu Vaccine 2008-11-03 00:00:00.000

 
Upvote 0 Downvote
Please forgive me, but I really don't understand what you are trying to achieve here. Can you show some sample data from the DailyLog table that has all 4 conditions. And then... based on the sample data, please show the expected results.

This will help to understand the goal, so I can better advise you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
SeqNo AssociateIDLog VisitReason VisitDate
90105 130427 Flu Accept 2008-11-02 00:00:00.000
89913 130427 Flu Vaccine 2008-11-04 00:00:00.000
91078 130641 Flu Decline 2008-11-21 00:00:00.000
89939 130641 Flu Vaccine 2008-11-05 00:00:00.000
91073 130666 Flu Decline 2008-11-21 00:00:00.000
90099 131227 Flu Accept 2008-11-05 00:00:00.000
.....

Here are the examples.

AssociateIDLog 130427 has both of 'Flu Accept' and 'Flu Vaccine' in the date range given, so this should be counted as 'AcceptVac'.

AssociateIDLog 130666 has 'Flu Accept' but no 'Flu Vaccine' in the date range given, so this should be counted as 'AcceptNoVac'.

AssociateIDLog 130641 has both of 'Flu Decline' and 'Flu Vaccine' in the date range given, so this should be counted as 'DeclineVac'.

AssociateIDLog 131227 has 'Flu Decline' but no 'Flu Vaccine' in the date range given, so this should be counted as 'DeclineNoVac'.

Please let me know if this clarifies.

thx so much




 
Upvote 0 Downvote
I think you got your expected results mixed up. Nevertheless, the query I show below should work out well for you.

First, I create a table variable to store your sample data. Then I wrote a query that produces the output you are looking for. You can copy/paste this to a query window to see how it works. If you are satisfied, simply change it to use your real table.

Code: 
Declare @Temp Table(SeqNo Int, AssociateIdLog int, VisitReason VarChar(20), VisitDate DateTime)

Insert Into @Temp Values(90105,130427,'Flu Accept' ,'2008-11-02 00:00:00.000')
Insert Into @Temp Values(89913,130427,'Flu Vaccine','2008-11-04 00:00:00.000')
Insert Into @Temp Values(91078,130641,'Flu Decline','2008-11-21 00:00:00.000')
Insert Into @Temp Values(89939,130641,'Flu Vaccine','2008-11-05 00:00:00.000')
Insert Into @Temp Values(91073,130666,'Flu Decline','2008-11-21 00:00:00.000')
Insert Into @Temp Values(90099,131227,'Flu Accept' ,'2008-11-05 00:00:00.000')

Select AssociateIdLog,
       Case When FluAccept  > 0 And FluVaccine > 0 Then 'AcceptVac'
            When FluAccept  > 0 And FluVaccine = 0 Then 'AcceptNoVac'
            When FluDecline > 0 And FluVaccine > 0 Then 'DeclineVac'
            When FluDecline > 0 And FluVaccine = 0 Then 'DeclineNoVac'
            End StatusColumn
From   (
       Select AssociateIdLog,
              Count(Case When VisitReason = 'Flu Accept' Then 1 End) As FluAccept,
              Count(Case When VisitReason = 'Flu Vaccine' Then 1 End) As FluVaccine,
              Count(Case When VisitReason = 'Flu Decline' Then 1 End) As FluDecline
       From   @Temp
       Where  VisitDate >= '20081101' And VisitDate < '20081201'
       Group By AssociateIdLog
       ) As AliasForCount

Notice the query in the from clause. When written this way, it's called a derived table. This derived table gets each associate id and the count of rows that have flu accept, decline, and vaccine. We then use the counts to determine the 'StatusColumn' that each associate falls under.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks so much. It works with the real table.
I am scratching my head how to join the other two tables so that I can have a.Dept, c.Assonum on SELECT

..............

from DailyLog d, AssociatePersonal a, CountAssociate c
where d.AssociateIDLog = a.AssociateID
and Dept !=''
and c.DeptName = a.dept
and c.DeptDate = convert(varchar, GETDATE(), 101)
and CONVERT(VARBINARY, c.Deptname) = CONVERT(VARBINARY, UPPER(c.Deptname))
group by a.Dept, c.Assonum
order by a.Dept

Pls advise.
 
Upvote 0 Downvote
First. You should start using ANSI joins. It may take a little while to get comfortable with them, but you'll be glad you did.

Using ANSI join syntax, your FROM clause would look like this.

Code: 
from  DailyLog d 
      Inner Join AssociatePersonal a
        On d.AssociateIDLog = a.AssociateID
      Inner Join CountAssociate c
        On c.DeptName = a.dept
where Dept !=''  
      and c.DeptDate = convert(varchar, GETDATE(), 101)
      and CONVERT(VARBINARY, c.Deptname) = CONVERT(VARBINARY, UPPER(c.Deptname))
group by a.Dept, c.Assonum
order by a.Dept

Now, how do we use the previous query? Simple, really. Just replace the DailyLog table with the query I showed you earlier. Again, this is called a derived table. Like this:

Code: 
from  [!]([/!]
      Select AssociateIdLog,
             Case When FluAccept  > 0 And FluVaccine > 0 Then 'AcceptVac'
                  When FluAccept  > 0 And FluVaccine = 0 Then 'AcceptNoVac'
                  When FluDecline > 0 And FluVaccine > 0 Then 'DeclineVac'
                  When FluDecline > 0 And FluVaccine = 0 Then 'DeclineNoVac'
                  End StatusColumn
      From   (
             Select AssociateIdLog,
                    Count(Case When VisitReason = 'Flu Accept' Then 1 End) As FluAccept,
                    Count(Case When VisitReason = 'Flu Vaccine' Then 1 End) As FluVaccine,
                    Count(Case When VisitReason = 'Flu Decline' Then 1 End) As FluDecline
             From   DailyLog
             Where  VisitDate >= '20081101' And VisitDate < '20081201'
             Group By AssociateIdLog
             ) As AliasForCount
      [!]) As d[/!]
      Inner Join AssociatePersonal a
        On d.AssociateIDLog = a.AssociateID
      Inner Join CountAssociate c
        On c.DeptName = a.dept
where Dept !=''  
      and c.DeptDate = convert(varchar, GETDATE(), 101)
      and CONVERT(VARBINARY, c.Deptname) = CONVERT(VARBINARY, UPPER(c.Deptname))
group by a.Dept, c.Assonum
order by a.Dept

The code shown in [!]red[/!] are the relevant parts that allow you to take a query and make it become a derived table.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thank you so much. It looks good so far.
Jut one issue I am having is to display the result in the horizontal format.

It returns like below:

1S 44 NULL 0
1S 44 AcceptNoVac 8
1S 44 AcceptVac 5
1S 44 DeclineNoVac 13
.....

How do you correct this into the format below?

DEPT empcnt AcceptVac AcceptNoVac DeclineVac DeclineNoVac
1S 44 5 8 0 13

In this example, DeclineVac is returned as NULL, which should have been 0 (zero).

Please advise.

<CODE>
select a.Dept, c.Assonum, StatusColumn, count (StatusColumn)
from
(Select AssociateIdLog,
Case When FluAccept > 0 And FluVaccine > 0 Then 'AcceptVac'
When FluAccept > 0 And FluVaccine = 0 Then 'AcceptNoVac'
When FluDecline > 0 And FluVaccine > 0 Then 'DeclineVac'
When FluDecline > 0 And FluVaccine = 0 Then 'DeclineNoVac'
End StatusColumn
From (
Select AssociateIdLog,
Count(Case When VisitReason = 'Flu Accept' Then 1 End) As FluAccept,
Count(Case When VisitReason = 'Flu Vaccine' Then 1 End) As FluVaccine,
Count(Case When VisitReason = 'Flu Decline' Then 1 End) As FluDecline
From DailyLog
Where VisitDate between '01-01-2008' and '12-31-2008'
Group By AssociateIdLog
) As AliasForCount
) as d
Inner Join AssociatePersonal a
On d.AssociateIDLog = a.AssociateID
Inner Join CountAssociate c
On c.DeptName = a.dept
where a.Dept !=''
and c.DeptDate = convert(varchar, GETDATE(), 101)
and CONVERT(VARBINARY, c.Deptname) = CONVERT(VARBINARY, UPPER(c.Deptname))
group by a.Dept, c.Assonum, StatusColumn
order by a.Dept, StatusColumn
</CODE>
 
Upvote 0 Downvote
TRy this:

Code: 
select a.Dept, c.Assonum, StatusColumn, count(AccecptVac), Count(AcceptNoVac), Count(DeclineVac), Count(DeclineNoVac)
from 
  (Select AssociateIdLog,
       Case When FluAccept  > 0 And FluVaccine > 0 Then 1 End As 'AcceptVac',
       Case When FluAccept  > 0 And FluVaccine = 0 Then 1 End As 'AcceptNoVac',
       Case When FluDecline > 0 And FluVaccine > 0 Then 1 End As'DeclineVac',
       Case When FluDecline > 0 And FluVaccine = 0 Then 1 End As 'DeclineNoVac'
   From   (
       Select AssociateIdLog,
              Count(Case When VisitReason = 'Flu Accept' Then 1 End) As FluAccept,
              Count(Case When VisitReason = 'Flu Vaccine' Then 1 End) As FluVaccine,
              Count(Case When VisitReason = 'Flu Decline' Then 1 End) As FluDecline
       From   DailyLog 
       Where  VisitDate between '01-01-2008' and '12-31-2008'
       Group By AssociateIdLog
          ) As AliasForCount
  ) as d
     Inner Join AssociatePersonal a
        On d.AssociateIDLog = a.AssociateID
      Inner Join CountAssociate c
        On c.DeptName = a.dept
where a.Dept !=''  
      and c.DeptDate = convert(varchar, GETDATE(), 101)
      and CONVERT(VARBINARY, c.Deptname) = CONVERT(VARBINARY, UPPER(c.Deptname))
group by a.Dept, c.Assonum, StatusColumn
order by a.Dept, StatusColumn

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I tried:

select a.Dept, c.Assonum, StatusColumn, count(AccecptVac), Count(AcceptNoVac), Count(DeclineVac), Count(DeclineNoVac)

and, it returns:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'AccecptVac'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'AcceptNoVac'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'DeclineVac'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'DeclineNoVac'.

Please advise.
 
Upvote 0 Downvote
Try:

Code: 
select a.Dept, c.Assonum, StatusColumn, 
       count(D.AcceptVac) As AcceptVac, Count(d.AcceptNoVac) As AcceptNoVac, Count(d.DeclineVac) As DeclineVac, Count(d.DeclineNoVac) As DeclineNoVac
from 
  (Select AssociateIdLog,
       Case When FluAccept  > 0 And FluVaccine > 0 Then 1 End As AcceptVac,
       Case When FluAccept  > 0 And FluVaccine = 0 Then 1 End As AcceptNoVac,
       Case When FluDecline > 0 And FluVaccine > 0 Then 1 End As DeclineVac,
       Case When FluDecline > 0 And FluVaccine = 0 Then 1 End As DeclineNoVac
   From   (
       Select AssociateIdLog,
              Count(Case When VisitReason = 'Flu Accept' Then 1 End) As FluAccept,
              Count(Case When VisitReason = 'Flu Vaccine' Then 1 End) As FluVaccine,
              Count(Case When VisitReason = 'Flu Decline' Then 1 End) As FluDecline
       From   DailyLog 
       Where  VisitDate between '01-01-2008' and '12-31-2008'
       Group By AssociateIdLog
          ) As AliasForCount
  ) as d
     Inner Join AssociatePersonal a
        On d.AssociateIDLog = a.AssociateID
      Inner Join CountAssociate c
        On c.DeptName = a.dept
where a.Dept !=''  
      and c.DeptDate = convert(varchar, GETDATE(), 101)
      and CONVERT(VARBINARY, c.Deptname) = CONVERT(VARBINARY, UPPER(c.Deptname))
group by a.Dept, c.Assonum, StatusColumn
order by a.Dept, StatusColumn

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Sorry. You'll need to remove StatusColumn from the list.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks so much.
This gives me the right format.

select a.Dept, c.Assonum,
sum (CASE WHEN StatusColumn ='AcceptVac' THEN 1 ELSE 0 END ) AS CntAcceptVac,
sum (CASE WHEN StatusColumn ='AcceptNoVac' THEN 1 ELSE 0 END ) AS CntAcceptNoVac,
sum (CASE WHEN StatusColumn ='DeclineVac' THEN 1 ELSE 0 END ) AS CntDeclineVac,
sum (CASE WHEN StatusColumn ='DeclineNoVac' THEN 1 ELSE 0 END ) AS CntDeclineNoVac,

But, I have a very confusing counting issue here.
I will post it below.
 
Upvote 0 Downvote
// I am verifying the results.

select a.Dept, c.Assonum,
sum (CASE WHEN VisitReason ='Flu Accept' THEN 1 ELSE 0 END ) AS CntAcc,
sum (CASE WHEN VisitReason ='Flu Decline' THEN 1 ELSE 0 END ) AS CntDec,
sum (CASE WHEN VisitReason ='Flu Vaccine' THEN 1 ELSE 0 END ) AS CntVac
from DailyLog d, AssociatePersonal a, CountAssociate c
where d.AssociateIDLog = a.AssociateID
and Dept !=''
and c.DeptName = a.dept
and VisitDate between '01-01-2008' and '12-31-2008'
and c.DeptDate = convert(varchar, GETDATE(), 101)
and CONVERT(VARBINARY, c.Deptname) = CONVERT(VARBINARY, UPPER(c.Deptname))
group by a.Dept, c.Assonum
order by a.Dept

This query returns the right counts.

Dept emp CntAcc CntDec CntVac
1S 44 13 13 7

However, if I run the query below, which is:

use AssociateHealth

select a.Dept, c.Assonum,
sum (CASE WHEN StatusColumn ='AcceptVac' THEN 1 ELSE 0 END ) AS CntAcceptVac,
sum (CASE WHEN StatusColumn ='AcceptNoVac' THEN 1 ELSE 0 END ) AS CntAcceptNoVac,
sum (CASE WHEN StatusColumn ='DeclineVac' THEN 1 ELSE 0 END ) AS CntDeclineVac,
sum (CASE WHEN StatusColumn ='DeclineNoVac' THEN 1 ELSE 0 END ) AS CntDeclineNoVac,
(c.Assonum- (sum (CASE WHEN StatusColumn ='AcceptVac' THEN 1 ELSE 0 END)+
sum (CASE WHEN StatusColumn ='AcceptNoVac' THEN 1 ELSE 0 END)+
sum (CASE WHEN StatusColumn ='DeclineVac' THEN 1 ELSE 0 END)+
sum (CASE WHEN StatusColumn ='DeclineNoVac' THEN 1 ELSE 0 END))) as CntNA
from
(Select AssociateIdLog,
Case When FluAccept > 0 And FluVaccine > 0 Then 'AcceptVac'
When FluAccept > 0 And FluVaccine = 0 Then 'AcceptNoVac'
When FluDecline > 0 And FluVaccine > 0 Then 'DeclineVac'
When FluDecline > 0 And FluVaccine = 0 Then 'DeclineNoVac'
End StatusColumn
from (
Select AssociateIdLog,
Count(Case When VisitReason = 'Flu Accept' Then 1 End) As FluAccept,
Count(Case When VisitReason = 'Flu Vaccine' Then 1 End) As FluVaccine,
Count(Case When VisitReason = 'Flu Decline' Then 1 End) As FluDecline
from DailyLog
Where VisitDate between '01-01-2008' and '12-31-2008'
Group By AssociateIdLog
) As AliasForCount
) as d
Inner Join AssociatePersonal a
On d.AssociateIDLog = a.AssociateID
Inner Join CountAssociate c
On c.DeptName = a.dept
where a.Dept !=''
and c.DeptDate = convert(varchar, GETDATE(), 101)
and CONVERT(VARBINARY, c.Deptname) = CONVERT(VARBINARY, UPPER(c.Deptname))
group by a.Dept, c.Assonum --, StatusColumn
order by a.Dept --, StatusColumn

dept Assonum CntAcceptVac CntAcceptNoVac CntDeclineVac CntDeclineNoVac
1S 44 5 8 0 13

so, if you comapre both:


 
Upvote 0 Downvote
Qry 1)
Dept emp CntAcc CntDec CntVac
1S 44 13 13 7

Qry 2)
dept Assonum CntAcceptVac CntAcceptNoVac CntDeclineVac CntDeclineNoVac
1S 44 5 8 0 13

The total emp counts (44) are correct on both.
The total count for 'Flu Accept' is CntAcc = 13 on Qry 1, so if we add CntAcceptVac + CntAcceptNoVac, it is 13 (= 5+8), which is a match.

The issue is:

The total count of 'Flu Vaccine = 7 on Qry 1
but, if CntAcceptVac + CntDeclineVac, it gives 5 (= 5+0).

What would cause this discrepancy?

Pls advise.

thx so much


 
Upvote 0 Downvote
gmmastros,

A ton of thanks to you. I think I resolved the puzzle.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Case sensitive request 1
Replies
3
Views
225
Chris Miller
Chris Miller
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
1K
Chris Miller
Chris Miller
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
428
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Adding field duplicates records
Replies
16
Views
390
Chris Miller
Chris Miller
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
514
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top