Difficult SQL Statement 1

[rewdee]

I have a table called TimeSheet that has EmpID, Date, ShiftID,HoursWorked as fields.

I would like to Sum the HoursWorked for each employee between 2 dates say June 20th to June 26th like:

SELECT TimeSheet.EmployeeID, Sum(TimeSheet.HrsWorked) AS SumOfHrsWorked, Count(EmployeeID)  AS ShiftNum
FROM Timesheet
WHERE TimeSheet.ShiftDate >= #June 20, 2005# AND
           TimeSheet.ShiftDate <= #June 26, 2005#           
GROUP BY TimeSheet.EmployeeID

[COLOR=red yellow]Now here's the tricky part:[/color] In the sum I need to exclude the values if the ShiftID on the last date (June 26th) is 3 and include the values towards the sum if on the day before the start date (June 19) the ShiftID = 3.

If anyone can write this query, I will definitely add a star and heap much praise.

Thanks,
Rewdee

 

[DotNetGnat]

try looking in to Exists and NOT Exists queries

-DNG

 

[travisbrown]

haven't tested it yet, but here's how I would do it. Probably a cleaner way is put the sub or join table in a seperate saved query.


The Ugly Way:

SELECT t.EmployeeID,

IIF((SELECT Sum(t1.HrsWorked) FROM TimeSheet AS t1 WHERE (t1.EmployeeID=t.EmployeeID) AND (Date = #6/26/2005# AND ShiftID <> '3') AND (Date < #6/19/2005# AND ShiftID = '3')) = Null,(SELECT Sum(t1.HrsWorked) FROM TimeSheet AS t1 WHERE (t1.EmployeeID=t.EmployeeID) AND (Date = #6/26/2005# AND ShiftID <> '3') AND (Date < #6/19/2005# AND ShiftID = '3')),0) AS SumofField, 

Count(t.EmployeeID) AS ShiftNum

FROM Timesheet AS t

GROUP BY t.EmployeeID;

The Prettier Way:

SELECT 
TimeSheet.EmployeeID, IIf(isNull(q2.SumofHrs)=true,0,q2.SumofHrs) AS SumofHrs, Count(TimeSheet.ShiftID) AS CountOfShiftID
FROM TimeSheet LEFT JOIN (SELECT t1.EmployeeID, Sum(t1.HrsWorked) AS SumofHrs
FROM TimeSheet AS t1
WHERE (((t1.Date)=#6/26/2005# And (t1.Date)<#6/19/2005#) AND ((t1.ShiftID)<>3 And (t1.ShiftID)=3))
GROUP BY t1.EmployeeID)q2 ON TimeSheet.EmployeeID = q2.EmployeeID
GROUP BY TimeSheet.EmployeeID, q2.SumofHrs;

 

[travisbrown]

Sorry, second query should be more like this:

SELECT

TimeSheet.EmployeeID, IIf(isNull(q2.SumofHrs)=true,0,q2.SumofHrs) AS SumofHrs, Count(TimeSheet.ShiftID) AS CountOfShiftID
FROM TimeSheet 

LEFT JOIN 

(SELECT t1.EmployeeID, Sum(t1.HrsWorked) AS SumofHrs, Count(t1.ShiftID) AS CountOfShiftID
FROM TimeSheet AS t1
WHERE 
 (Date = #6/26/2005# AND ShiftID <> 3) AND (Date < #6/19/2005# AND ShiftID = 3)
GROUP BY  t1.EmployeeID)q2 

ON TimeSheet.EmployeeID = q2.EmployeeID
GROUP BY TimeSheet.EmployeeID, q2.SumofHrs;

 

[PHV]

Another way:
SELECT EmployeeID, Sum(HrsWorked) AS SumOfHrsWorked, Count(EmployeeID) AS ShiftNum
FROM Timesheet
WHERE ShiftDate Between #2005/06/19# And #2005/06/26#
AND Not (ShiftDate = #2005/06/26# AND ShiftID = 3)
AND Not (ShiftDate = #2005/06/19# AND ShiftID <> 3)
GROUP BY EmployeeID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[travisbrown]

PVH, wouldn't this limit the records though rather than just exclude from the SUM the hours worked for the days and shifts in question? I was under the impression that rewdee wanted to filter just the SUM value, not the top level records themselves. Maybe the employees worked third shift free for a day?

By filtering the toplevel query, the count value and could be affected too. Maybe this is what rewdee wanted anyways. Dunno. What do you have to say rewdee?

Though you made me realize I need to fix mine yet again. Sigh. I'd stick your filter in my right table for the joined query.

SELECT

TimeSheet.EmployeeID, IIf(isNull(q2.SumofHrs)=true,0,q2.SumofHrs) AS SumofHrs, Count(TimeSheet.ShiftID) AS CountOfShiftID
FROM TimeSheet 

LEFT JOIN 

(SELECT t1.EmployeeID, Sum(t1.HrsWorked) AS SumofHrs, Count(t1.ShiftID) AS CountOfShiftID
FROM TimeSheet AS t1
WHERE ShiftDate Between #2005/06/19# And #2005/06/26#
AND Not (ShiftDate = #2005/06/26# AND ShiftID = 3)
AND Not (ShiftDate = #2005/06/19# AND ShiftID <> 3)
GROUP BY  t1.EmployeeID)q2 

ON TimeSheet.EmployeeID = q2.EmployeeID
GROUP BY TimeSheet.EmployeeID, q2.SumofHrs;

 

[rewdee]

Travis, I don't know where you work or how much you get paid but I would go to your boss and ask for greater renumeration for you.

Brilliant work.

I just modified it a bit since I needed to add a few more fields and the count was returning all records in the timesheet not just between.

SELECT

TimeSheet.EmployeeID, IIf(isNull(q2.SumOfHrsWorked)=true,0,q2.SumOfHrsWorked) AS SumOfHrsWorked, Count(TimeSheet.ShiftID) AS CountOfShiftID,Sum(CalledInBonus) AS SumOfCalledInBonus, Sum(Premium) AS SumOfPreum
FROM TimeSheet

LEFT JOIN

(SELECT t1.EmployeeID, Sum(t1.HrsWorked) AS SumOfHrsWorked, Count(t1.ShiftID) AS CountOfShiftID,Sum(t1.CalledInBonus) AS SumOfCalledInBonus, Sum(t1.Premium) AS SumOfPremium
FROM TimeSheet AS t1
WHERE ShiftDate Between #2005/06/19# And #2005/06/26#
AND Not (ShiftDate = #2005/06/26# AND ShiftID = 3)
AND Not (ShiftDate = #2005/06/19# AND ShiftID <> 3)
GROUP BY  t1.EmployeeID)q2

ON TimeSheet.EmployeeID = q2.EmployeeID
[blue]WHERE TimeSheet.ShiftDate Between #2005/06/19# And #2005/06/26#[/blue]
GROUP BY TimeSheet.EmployeeID, q2.SumOfHrsWorked;

Thanks Again,
Rewdee