Sequential Dates and Grouping

Peet Schultz

Hi All We have a table with a persons Employee number (Resource tag) , Date Worked and the Shift Worked on that date. Like So resource tag date worked shift type ------------ ------------------------------------------------------ -------------------------------------------------- 125197584 2004-07-14 00:00:00.000 Night 125197584 2004-07-15 00:00:00.000 Night 125197584 2004-07-16 00:00:00.000 Night 125197584 2004-07-17 00:00:00.000 Night 125197584 2004-07-18 00:00:00.000 Morning 125197584 2004-07-19 00:00:00.000 Morning 125197584 2004-07-20 00:00:00.000 Morning 125197584 2004-07-21 00:00:00.000 Morning 125197584 2004-07-22 00:00:00.000 Morning 125197584 2004-07-23 00:00:00.000 Morning 125197584 2004-07-24 00:00:00.000 Morning 125197584 2004-07-25 00:00:00.000 Night 125197584 2004-07-26 00:00:00.000 Night 125197584 2004-07-27 00:00:00.000 Night 125197584 2004-07-28 00:00:00.000 Night 125197584 2004-07-29 00:00:00.000 Night 125197584 2004-07-30 00:00:00.000 Night 125197584 2004-07-31 00:00:00.000 Night 125197584 2004-08-01 00:00:00.000 Morning 125197584 2004-08-02 00:00:00.000 Morning 125197584 2004-08-03 00:00:00.000 Morning 125197584 2004-08-04 00:00:00.000 Morning 125197584 2004-08-05 00:00:00.000 Afternoon 125197584 2004-08-06 00:00:00.000 Morning 125197584 2004-08-07 00:00:00.000 Morning 125197584 2004-08-08 00:00:00.000 Morning 125197584 2004-08-09 00:00:00.000 Morning 125197584 2004-08-10 00:00:00.000 Morning 125197584 2004-08-11 00:00:00.000

Michael Potter

You can try something like:

SELECT
     wt.DateWorked
FROM 
     WorkTable wt
WHERE
     wt.ShiftType <>
          (SELECT
               ShiftType
           FROM
               WorkTable
           WHERE
               ResourceTag = wt.ResourceTag AND
               DateWorked = (SELECT 
                                 MAX(DateWorked)
                             FROM 
                                 WorkTable
                             WHERE
                                 ResourceTag = wt.ResourceTag AND
                                 DateWorked < wt.DateWorked))

The table will need the following index to work quickly:

CREATE INDEX ShiftTest ON WorkTable
(
     ResourceTag,
     DateWorked
)

This assumes that no worker will work more than 1 shift per day.

Peet Schultz

Thanks This looks good, only problem is does not return the first record either. I do have a way of getting past that and will run a couple of benchmarks to check the speed of this against the left join Regards Peet YASP

Michael Potter

Didn't think about the first record. Just wrap the correlated Select in an ISNULL() that will return an invalid ShiftType:

wt.ShiftType <> ISNULL((Select...),'')