none
Checking for consecutive values RRS feed

  • Question

  • Hi

    I have a table of StaffIDs and WeekNumbers; for weeks of year that a staff has worked.

    How can I get a list staff who have 12 consecutive week numbers, to show they have worked consistently for 12 weeks? 

    Thanks

    Regards



    • Edited by Y a h y a Thursday, December 12, 2019 4:54 PM
    Thursday, December 12, 2019 4:29 PM

Answers

  • If the consecutive weeks must occur in the same year, this should work:

    SELECT distinct tblWeeksWorked.StaffID
    FROM tblWeeksWorked
    WHERE (SELECT COUNT(*)
            FROM tblWeeksWorked AS W
            WHERE W.StaffID = tblWeeksWorked.StaffID AND
                W.WeekNumbers BETWEEN tblWeeksWorked.WeekNumbers AND tblWeeksWorked.WeekNumbers + 11)=12;

    If you need to count consecutive weeks that cross years (e.g. weeks 50 through 9) you'll need to do something a little more complex.

    -Bruce

    • Marked as answer by Y a h y a Friday, December 13, 2019 1:55 AM
    Thursday, December 12, 2019 5:32 PM