CODEWITHSUNDEEP
sqlsql-server
Oisian
Oisian

Reputation: 45

Grouping multiple days together

i have a query that gets data about employee clock times. this query also adds a field called inDay, groups the rows by day and gives them a number. so that i can datediff the rows to get the correct worked times.

    SELECT     CHINA_VISION_PubPersonnel.Name, CHINA_VISION_PubPersonnel.ID, CHINA_VISION_PubCards.CardCode, CHINA_VISION_PubPersonnel.Telephone, ATDShiftDetail.EndOverDay, 
                          CHINA_VISION_DorEvents.EventTM AS T, CONVERT(VARCHAR(10), CHINA_VISION_DorEvents.EventTM, 102) AS Day, FLOOR(CONVERT(FLOAT, 
                          CHINA_VISION_DorEvents.EventTM)) AS DayNumber,    
                          ROW_NUMBER() OVER(PARTITION BY FLOOR(CONVERT(FLOAT,CHINA_VISION_DorEvents.EventTm)) ORDER BY CHINA_VISION_DorEvents.EventTm) InDay  
    FROM         CHINA_VISION_PubCards INNER JOIN
                          CHINA_VISION_PubPersonnel ON CHINA_VISION_PubCards.PubPersonnel_Ref = CHINA_VISION_PubPersonnel.Reference INNER JOIN
                          CHINA_VISION_DorEvents ON CHINA_VISION_PubCards.CardCode = CHINA_VISION_DorEvents.CardCode INNER JOIN
                          ATDShiftDetail ON RIGHT(CHINA_VISION_PubPersonnel.ID, 4) = ATDShiftDetail.Name
    WHERE     (CHINA_VISION_DorEvents.DorCtrls_Ref = '16') AND (CHINA_VISION_DorEvents.CardCode = '0042f55c') AND (CONVERT(Date, CHINA_VISION_DorEvents.EventTM) 
                          > DATEADD(day, - 6, GETDATE())) AND ATDShiftDetail.EndOverDay = '1'

example output data.

        Name    ID  CardCode Telephone  EndOverDay          T                   Day   DayNumber InDay
        ----------------------------------------------------------------------------------------------
    Joe Blogs   1   0042f55c    8           1       2015-11-06 01:17:05.000 2015.11.06  42312   1
    Joe Blogs   1   0042f55c    8           1       2015-11-06 01:45:44.000 2015.11.06  42312   2
    Joe Blogs   1   0042f55c    8           1       2015-11-06 07:45:56.000 2015.11.06  42312   3
    Joe Blogs   1   0042f55c    8           1       2015-11-09 19:39:21.000 2015.11.09  42315   1
    Joe Blogs   1   0042f55c    8           1       2015-11-10 01:11:15.000 2015.11.10  42316   1
    Joe Blogs   1   0042f55c    8           1       2015-11-10 01:36:39.000 2015.11.10  42316   2
    Joe Blogs   1   0042f55c    8           1       2015-11-10 07:57:02.000 2015.11.10  42316   3
    Joe Blogs   1   0042f55c    8           1       2015-11-10 19:42:45.000 2015.11.10  42316   4
    Joe Blogs   1   0042f55c    8           1       2015-11-11 01:16:07.000 2015.11.11  42317   1
    Joe Blogs   1   0042f55c    8           1       2015-11-11 01:40:06.000 2015.11.11  42317   2
    Joe Blogs   1   0042f55c    8           1       2015-11-11 07:55:20.000 2015.11.11  42317   3

However this employee works nights, that means i need to group the last record from the night before, which will be when they clock on with the next three records.

using the output above here is what the out put should be like.

once the data is how i like i can then Datediff the rows to get the employee work hours. 

        Name    ID  CardCode Telephone  EndOverDay          T                   Day   DayNumber InDay
        ----------------------------------------------------------------------------------------------
    Joe Blogs   1   0042f55c    8           1       2015-11-06 01:17:05.000 2015.11.06  42312   1 -- were missing the record from the night before so start at 1 here
    Joe Blogs   1   0042f55c    8           1       2015-11-06 01:45:44.000 2015.11.06  42312   2
    Joe Blogs   1   0042f55c    8           1       2015-11-06 07:45:56.000 2015.11.06  42312   3
    Joe Blogs   1   0042f55c    8           1       2015-11-09 19:39:21.000 2015.11.09  42315   1
    Joe Blogs   1   0042f55c    8           1       2015-11-10 01:11:15.000 2015.11.10  42316   2
    Joe Blogs   1   0042f55c    8           1       2015-11-10 01:36:39.000 2015.11.10  42316   3
    Joe Blogs   1   0042f55c    8           1       2015-11-10 07:57:02.000 2015.11.10  42316   4
    Joe Blogs   1   0042f55c    8           1       2015-11-10 19:42:45.000 2015.11.10  42316   1
    Joe Blogs   1   0042f55c    8           1       2015-11-11 01:16:07.000 2015.11.11  42317   2
    Joe Blogs   1   0042f55c    8           1       2015-11-11 01:40:06.000 2015.11.11  42317   3
    Joe Blogs   1   0042f55c    8           1       2015-11-11 07:55:20.000 2015.11.11  42317   4

Upvotes: 0

Views: 87

Answers (2)

Jur
Jur

Reputation: 520

This is a functional question rather than a technical one... if the dates may differ, how do you know which "in" and "out" belong together? For example, the first four records MIGHT mean the emp came at 6-11 1:17, left 6-11 1:45, came 6-11 7:45, then stayed three days until 9-11 19:39.

There's only one time stamp at 9-11; how is that supposed to be processed?

You need a code for wether the emp time stamp is "in" or "out", it's the only way to determine which timespan between clock records is "present" and which is "absent".

Upvotes: 0

Jason Goemaat
Jason Goemaat

Reputation: 29214

You have to have some way to tell when a shift starts and ends, or is it just "the last time of a day is always the start of a shift"? If there is a certain time, it would be easy for this case do alter the time by 5 hours to get the day number if they always clock-in after 7:00 pm:

FLOOR(CONVERT(FLOAT, DATEADD(hour, 5, EventTM))) AS DayNumber

Upvotes: 1

Related Questions