CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlcase
jballard81
jballard81

Reputation: 135

Use Case Statement with AND in order to filter results SQL Server 2008

I am trying to build a statement that will return the number of work orders worked on by each employee each day. The problem is that the employee could work on a work order multiple times in one day (which I would only count as 1), or they could work on a work order multiple times on multiple days (which i would count as 1 per day worked on).

Can I use the Case statement with an AND expression to do this? something like this:

SELECT    
   WKE_EmployeeID, WKE_LabDate, WKE_RecordID, 
   'WorkOrder' = CASE
                   WHEN WKE_WorkOrderID = WKE_WorkOrderID AND WKE_LabDate = WKE_LabDate 
                     THEN (Select Distinct WKE_WorkOrderID)
                   ELSE WKE_WorkOrderID
FROM         
   dbo.WKE
WHERE     
   (WKE_EmployeeID IN (N'sweda', N'bakja', N'gebwa')) 
   AND (WKE_LabDate BETWEEN @FromDate AND @ToDate)
ORDER BY 
   WKE_WorkOrderID

This is just the first part of course, b/c then I need to count the workorders per employee per day, but I want to figure out how to do this part first so that I can verify my data.

Am I on the right path, or should i be looking to do this in a completely different way? Maybe sub reports?

SAMPLE DB

WKO employee    date
RW19800 bakja   1/6/14 12:00 AM  
RW20573 gebwa   1/2/14 12:00 AM  
RW20574 gebwa   1/2/14 12:00 AM  
RW20600 gebwa   1/10/14 12:00 AM  
**RW20602   gebwa   1/2/14 12:00 AM  
RW20602 gebwa   1/7/14 12:00 AM**  
*RW20603    bakja   1/8/14 12:00 AM  
RW20603 bakja   1/8/14 12:00 AM*

So for the BOLDED part of the above, I would count that as 2 instances since work was done on it 2 different days.

For the Italicized part, I would want to count that as 1 instance since work was done on it twice in the same day.

Final Output sample example: 

date    WKO Count   employee  
1/2/14 12:00 AM 3   gebwa  
1/6/14 12:00 AM 1   bakja  
1/7/14 12:00 AM 1   gebwa  
1/8/14 12:00 AM 1   bakja  
1/10/14 12:00 AM1   gebwa

Upvotes: 1

Views: 1951

Answers (2)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171511

No, you are not on the right path. This should just be done using GROUP BY.

It would look something like this:

select EmployeeID, Year, Month, Day, Count(*) as Count
from (
    --get one row per employee/workorder/day
    select EmployeeID, WorkOrderID, year(date) as Year, month(date) as Month, day(date) as Day
    from WorkOrder
    where EmployeeID in (N'sweda', N'bakja', N'gebwa')
        and date between @FromDate and @ToDate
    group by EmployeeID, WorkOrderID, year(date), month(date), day(date)
    ) a
group by EmployeeID, Year, Month, Day

Upvotes: 3

Anon
Anon

Reputation: 10908

SELECT WKE_EmployeeID, WKE_LabDate, COUNT(DISTINCT WorkOrderID)
FROM dbo.WKE
WHERE EmployeeID IN (N'sweda', N'bakja', N'gebwa')
  AND Date BETWEEN @FromDate AND @ToDate
GROUP BY WKE_EmployeeID, WKE_LabDate

Upvotes: 3

Related Questions