Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    44

    Unanswered: Select with consecutive dates

    Hi guys,

    I have a table with

    EmpNum, Date, Abstype

    What I want is to pull a list of all the employees and the Monday date of employees who have an absence on a Monday --> Friday consecutively.

    eg Table

    EmpNum, Date, Abstype

    001 07/23/2012 VAC *Monday
    001 07/24/2012 VAC
    001 07/25/2012 VAC
    001 07/26/2012 VAC
    001 07/27/2012 VAC
    003 07/23/2012 VAC * Monday
    003 07/25/2012 VAC
    003 08/23/2012 VAC
    003 09/23/2012 VAC
    001 08/06/2012 VAC * Monday
    001 08/07/2012 VAC
    001 08/08/2012 VAC
    001 08/09/2012 VAC
    001 08/10/2012 VAC


    This would return

    001 07/23/2012
    001 08/06/2012

    As these are the 2 that run from monday-friday

    Thanks in advance!

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    select * from MyTable as t
    where (DATEPART(dw, [Date]) = 2) and
          (exists(select 1 from MyTable as s
                  where (s.EmpNum = t.EmpNum) and
                        (s.[Date] = DATEADD(DAY, 4, t.[Date]))))
    Hope this helps.

  3. #3
    Join Date
    Sep 2011
    Posts
    44
    Thanks! This showed me where absence was on Monday and Friday. I replicated what you had for the other days.

    I used the below and it gave me what I wanted. There is probably a cleaner way in a loop but it works for me.


    Thanks for your help!



    select * from tmsuser.tmsfc as t
    where (DATEPART(dw, [startDate]) = 2) and code like '%s_vacday%' and
    (exists(select 1 from tmsuser.tmsfc as s
    where (s.empref = t.empref) and code like '%s_vacday%' and
    (s.[startDate] = DATEADD(DAY, 1, t.[startdate])))) and

    (exists(select 1 from tmsuser.tmsfc as s
    where (s.empref = t.empref) and code like '%s_vacday%' and
    (s.[startDate] = DATEADD(DAY, 2, t.[startdate]))))
    and

    (exists(select 1 from tmsuser.tmsfc as s
    where (s.empref = t.empref) and code like '%s_vacday%' and
    (s.[startDate] = DATEADD(DAY, 3, t.[startdate]))))
    and

    (exists(select 1 from tmsuser.tmsfc as s
    where (s.empref = t.empref) and code like '%s_vacday%' and
    (s.[startDate] = DATEADD(DAY, 3, t.[startdate]))))

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •