Schedule vs Shift counts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Schedule vs Shift counts

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-10-17 : 09:34:48

SELECT S.employeeNumber,
s.DateOfBusiness,
S.TotalHours,

CASE WHEN S.EmployeeShiftNumber > (SELECT count(*) from HstSchedule where FKEmployeeNumber = s.FKEmployeeNumber AND ScheduleDate = S.DateOfBusiness AND FKStoreId = S.FKStoreId AND HstSchedule.FKJobCodeId = S.FKJobCodeId)

THEN 0
ELSE IsNull(SCHED.Hours,0)
END AS TotalScheduledHours

FROM HstShift S
FULL OUTER JOIN HstSchedule SCHED
ON S.FKEmployeeNumber = SCHED.FKEmployeeNumber
AND S.FKJobCodeId = SCHED.FKJobCodeId
AND S.FKStoreId = SCHED.FKStoreId
AND S.DateOfBusiness = SCHED.ScheduleDate
AND S.ScheduleIn = SCHED.InMinute
AND S.ScheduleOut = SCHED.OutMinute

I cannot figure out how to address the part in red. If there are more shifts then there are schedule records, then I want to return 0 for TotalScheduleHours. I had:

CASE WHEN S.EmployeeShiftNumber > 1
THEN 0
ELSE ISNULL(SCHED.Hours,0)
END as TotalScheduledHours,

But I cannot trust that there will only be one schedule setup. Any suggestions?

Much thanks!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-17 : 10:15:45

If EmployeeShiftNumber holds the number of shifts for a given employee, date, store and job, then your code looks like it should work.

btw I don't think you want FULL OUTER JOIN. I believe that a LEFT JOIN should be sufficient (and more efficient)

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-10-17 : 10:33:49

It does not work. :( I have an employee that has 2 schedule records and 3 shift records. The 3rd shift record should return 0 for the TotalScheduledHours so that my Total TotalScheduledHours is not inflated.

I used the Full Outer Join because I need all shift records, whether they have a Schedule record, and I also need all schedule records whether or not they have a worked shift record. Will the LEFT OUTER JOIN give me ALL?

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-10-17 : 10:59:55

AHHHH. I got it!!! My EmployeeShiftNumbers are 0 based.

CASE WHEN (S.EmployeeShiftNumber + 1) > (SELECT count(*)
FROM HstSchedule
WHERE FKEmployeeNumber = s.FKEmployeeNumber
AND ScheduleDate = S.DateOfBusiness
AND FKStoreId = S.FKStoreId
AND HstSchedule.FKJobCodeId = S.FKJobCodeId)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources