• Instead of all the hard-coded dates, it looks like you could use a CTE like this:

    declare @Date datetime;

    select @Date = dateadd(day, datediff(day, 0, getdate()), 0); -- Removes time from date

    ;with

    Shifts (Shift, Start, Done) as

    (select '1st', dateadd(hour, 3, @Date), dateadd(hour, 20, @Date)

    union all

    select '2nd', dateadd(hour, 9, @Date), dateadd(hour, 23, @Date)

    union all

    select '3rd', dateadd(hour, 18, @Date), dateadd(hour, 35, @Date))

    Change the value for @Date to an input parameter (but keep the part that removes the time, just replace getdate() with the parameter), and you can query this for any date you want.

    Join the CTE to the login time and logout time data, and you have your shifts, without the more complex unions you're currently using. Will definitely improve performance.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon