SQL nested if statement inside count

Question

Here's what I have:

SELECT   Count(*) AS cnt 
FROM     tblOccurrence AS x 
WHERE    (((x.OccurrenceDate) Between DateAdd('m',-6,Date()) And Date()) 
         AND ((Exists ( SELECT * 
                        FROM tblOccurrence AS y 
                        WHERE y.TechID = x.TechID AND DATEADD ('d', -1, x.[OccurrenceDate]) = y.[OccurrenceDate]))=False) 
                        AND ((x.TechID)= " & Me.tbxTechID.Text & ")) 
GROUP BY x.TechID;

This is used to calculate "occurrences" for employee absences, if the employee is unexcused for 1 day it's 1 occurrence. If they are unexcused for 5 days in a row, it's still one occurrence.

This is working perfectly until it was pointed out that if an employee has a M-F shift and calls in on Friday and then on Monday, that is only 1 occurrence. To make things even more tough, our employee shifts very greatly (M-F, Tue-Sat, Sun-Mon, M-Tue & Fri-Sat, etc) ... so I have built a table that holds all the different shifts (about 8 of them) that has 9 columns:

ID - ShiftName - Sunday - Monday - Tuesday - Wednesday - Thursday - Friday - Saturday

The ShiftName is a text field with short descriptions (Mon-Fri, Tue-Fri, Sun-Thu, etc) and then Yes/No fields for the days of the week. Then a check mark is a scheduled day.

So I would love everyone's input for 2 things,

is this a 'good' way to capture what I need captured?
how would that SQL statement change to get the occurrence count correct taking into account the unexcused absences that wrap a weekend but count as 1 occurrence?

So, it seems that the only time a person actually would incur a second occurrence is if they actually return to work and have another absence. Do you record what days they are at work? — UnhandledExcepSean
Correct. If someone is unexcused on a monday comes back to work tuesday and calls off again on wednesday that's 2 occurrences. — Zamael
And the employee's shift is now captured with a table (tblShifts) that I described above. 9 columns, yes/no for each day of the week with their scheduled days having "yes" values and their scheduled days off with "no" values — Zamael
Do you record what days they are at work? I don't mean schedule, but them actually being at work. If so, then who cares what their schedule is? All absences are delimited by attendance; I would try to go this route first. — UnhandledExcepSean
No. This program was developed to track unexcused absences and that's it. So we are going to enter just when the employee has an unexcused absence. — Zamael

Amy A Amy A · Accepted Answer · 2012-08-28T01:45:00

This would be easier to implement with one more column addition to the table tblOccurrence. Call this new column ie: occuranceCounter. Populate this column with a trigger. Whenever you insert a new row into the tblOccurence, this trigger will populate the occuranceCounter column.

Trigger logic : Check the employee's shift, check the last absense date, current absense date, if the days are consecutive based on the employees shift then keep the occuranceCounter same as the last absense's occuranceCounter else increment the occuranceCounter by one.

If the employees shift data changes over the time, then adding an employee shift history table will be helpful.

SQL nested if statement inside count

1 Answers