We help IT Professionals succeed at work.

SQL Query for minutes worked for every hour


Very simplified, I have a SQL table containing the following;

PERSONNUM;      LABORLEVEL1;      EVENTDATE;                  INPUNCHDTM;                  OUTPUNCHDTM;
100;                    0001;            2011-11-11 00:00:00.000;      2011-11-11 09:05:00.000;      2011-11-11 12:57:00.000
101;                    0001;            2011-11-11 00:00:00.000;      2011-11-11 10:10:00.000;      2011-11-11 13:51:00.000


There will be at least one record per person per day.

Wanted output is

EVENTDATE;      LABORLEVEL1;      Time_start;      Time_end;      Staff_minutes_per_day      
20111111;            0001;                            0900;                1000;                     55      
20111111;            0001;                            1000;                1100;                    110      
20111111;            0001;                            1100;                1200;                    120      
20111111;            0001;                            1200;                1300;                    117      

etc...

Basically I need to know number of minutes worked for every hour during the day, based on in and out punches.
There will be hundreds of records in this table every day.

Any ideas on how to solve this?

Thanks,
Bjorn
 
Comment
Watch Question

SELECT Convert(Char(8), EventDate, 112) as EventDate, LABORLEVEL1,
datepart(hh, INPUNCHDTM) Time_start  , datepart(hh, OUTPUNCHDTM) as Time_end,
datediff(mi, INPUNCHDTM, OUTPUNCHDTM) as Staff_minutes_per_day
FROM TABLE_NAME

Author

Commented:
Thanks for getting back,

My bad for poor explanation. I should have called the last output column Staff_minutes_per_hour.

This gives me minutes per day, but what I really need is minutes worked per hour of the day.
I need to count the sum of minutes being worked from 8-9, from 9-10, from 10-11 etc.

Thanks,
Bjorn
If you want to do that you have to include the PERSONNUM in your report and use the GROUP BY

Author

Commented:
I need it grouped by Laborlevel1, butwith nu,ber of minutes worked between every hour of the day.
I might have to use lots of CASE statements to check for hour and minutes of the day..
Top Expert 2011

Commented:
like this

you may need to extend the "tally" table n3 if the period of inpunch - outpunch can extend over 999 hours...

i assume you want hours after midnight recorded as the following day?
;with n1 as (select 0 as n union all select 1 union all select 2
union all select 3 union ALL SElECT 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9)
, n2 as (select (a.n*10)+b.n from n1 as a,n1 as b)
,n3 as (select (a.n*10)+b.n from n2 as a,n1 as b)
,hrdata as (select convert(char(8),dateadd(h,n3.n,inpunchdtm),112) as Evendate
                  ,laborlevel1
                  ,datepart(hh,dateadd(h,n3.n,inpunchdtm)) as Starthr
                  ,case when datediff(minute,dateadd(h,n3.n,inpunchdtm),outpunchdtm)
                        > 60 then 60 else datediff(minute,dateadd(h,n3.n,inpunchdtm),outpunchdtm)
                      end as worked
             from Yourtable
             cross join n3
             where outpunchdtm)>=dateadd(h,n3,n,inpunchdtm)
              ) 
select eventdate,laborlevel1
      ,right('0000'+convert(varchar(4),starthr*100),4) as time_start
      ,right('000'+convert(varchar(4),(starthr+1)*100),4) as time_end
      ,sum(worked) as staff_minutes_per_day
from hrdata
group by eventdata,laborlevel1.starthr
order by 1,2,3

Open in new window

Author

Commented:

I made a few adjustments, had to use hh instead of h for dateadd, but still I can't get it to run.

Error message is:

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 'n2'.

Thanks,
Bjorn
;with n1 as (select 0 as n union all select 1 union all select 2
union all select 3 union ALL SElECT 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9)
, n2 as (select (a.n*10)+b.n from n1 as a,n1 as b)
,n3 as (select (a.n*10)+b.n from n2 as a,n1 as b)
,hrdata as (select convert(char(8),dateadd(hh,n3.n,inpunchdtm),112) as Evendate
                  ,laborlevel1
                  ,datepart(hh,dateadd(hh,n3.n,inpunchdtm)) as Starthr
                  ,case when datediff(minute,dateadd(hh,n3.n,inpunchdtm),outpunchdtm)
                        > 60 then 60 else datediff(minute,dateadd(hh,n3.n,inpunchdtm),outpunchdtm)
                      end as worked
             from vp_timesheetpunch
             cross join n3
             where outpunchdtm>=dateadd(hh,n3.n,inpunchdtm)
              ) 
select eventdate,laborlevel1
      ,right('0000'+convert(varchar(4),starthr*100),4) as time_start
      ,right('000'+convert(varchar(4),(starthr+1)*100),4) as time_end
      ,sum(worked) as staff_minutes_per_day
from hrdata
group by eventdata,laborlevel1.starthr
order by 1,2,3

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Do you want to look at a specific time range, i.e., do you have a start_datetime and end_datetime you want to see totals for or do you want it based on the data itself?

Author

Commented:
This query will typically be run every night with yesterday's data. It might also need to be run for a range of dates. I don't need totals other than per hour.

Thanks
Top Expert 2011
Commented:
yeah sorry missing AS N
;with n1 as (select 0 as n union all select 1 union all select 2
union all select 3 union ALL SElECT 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9)
, n2 as (select (a.n*10)+b.n as n from n1 as a,n1 as b)
,n3 as (select (a.n*10)+b.n as n from n2 as a,n1 as b)
,hrdata as (select convert(char(8),dateadd(hh,n3.n,inpunchdtm),112) as Evendate
                  ,laborlevel1
                  ,datepart(hh,dateadd(hh,n3.n,inpunchdtm)) as Starthr
                  ,case when datediff(minute,dateadd(hh,n3.n,inpunchdtm),outpunchdtm)
                        > 60 then 60 else datediff(minute,dateadd(hh,n3.n,inpunchdtm),outpunchdtm)
                      end as worked
             from vp_timesheetpunch
             cross join n3
             where outpunchdtm>=dateadd(hh,n3.n,inpunchdtm)
              ) 
select eventdate,laborlevel1
      ,right('0000'+convert(varchar(4),starthr*100),4) as time_start
      ,right('000'+convert(varchar(4),(starthr+1)*100),4) as time_end
      ,sum(worked) as staff_minutes_per_day
from hrdata
group by eventdata,laborlevel1.starthr
order by 1,2,3

Open in new window

Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Note that the @end_date is not actually reported on, i.e., the report ends one minute before that.

With date ranges, I always use < on the end range so that you don't have to worry about the datetime precision, i.e. smalldatetime vs datetime vs datetime2.

So @start = 2011-11-11 and @end = 2011-11-12 reports for all of the 2011-11-11 (but not the first hour of 2011-11-12).

Naturally if it's easier for you, you could adjust the code to accept an "active" end date and add one minute to it prior to execution :-) .

DECLARE @start_date datetime
DECLARE @end_date datetime

SET @start_date = '20111111'
SET @end_date = '20111112'


DECLARE @num_hours int
SET @num_hours = DATEDIFF(HOUR, @start_date, @end_date)

/*
PERSONNUM;      LABORLEVEL1;      EVENTDATE;                  INPUNCHDTM;                  OUTPUNCHDTM;
100;                    0001;            2011-11-11 00:00:00.000;      2011-11-11 09:05:00.000;      2011-11-11 12:57:00.000
101;                    0001;            2011-11-11 00:00:00.000;      2011-11-11 10:10:00.000;      2011-11-11 13:51:00.000


EVENTDATE;      LABORLEVEL1;      Time_start;      Time_end;    Staff_minutes_per_day      
20111111;            0001;           0900;           1000;                 55      
20111111;            0001;           1000;           1100;                110      
20111111;            0001;           1100;           1200;                120      
20111111;            0001;           1200;           1300;                117      
*/

;WITH 
nums1 AS (
    SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
nums2 AS ( SELECT (n2.num * 10) + n1.num AS num FROM nums1 n2 CROSS JOIN nums1 n1
),
nums3 AS ( SELECT (n3.num * 100) + n2.num AS num FROM nums1 n3 CROSS JOIN nums2 n2 
)
SELECT
    CONVERT(char(8), DATEADD(HOUR, n3.num, @start_date), 112) AS EVENTDATE,
    vt.LABORLEVEL1,
    CONVERT(char(2), DATEADD(HOUR, n3.num, @start_date), 8) + ':00' AS Time_Start,
    CONVERT(char(2), DATEADD(HOUR, n3.num + 1, @start_date), 8) + ':00' AS Time_Start,
    SUM(CASE WHEN vt.INPUNCHDTM <= DATEADD(HOUR, n3.num, @start_date) AND vt.OUTPUNCHDTM >= DATEADD(HOUR, n3.num + 1, @start_date) 
             THEN 60
             WHEN vt.INPUNCHDTM <= DATEADD(HOUR, n3.num, @start_date) 
             THEN 60 - DATEDIFF(MINUTE, vt.OUTPUNCHDTM, DATEADD(HOUR, n3.num + 1, @start_date))
             ELSE DATEDIFF(MINUTE, vt.INPUNCHDTM, CASE WHEN vt.OUTPUNCHDTM >= DATEADD(HOUR, n3.num + 1, @start_date) 
                 THEN DATEADD(HOUR, n3.num + 1, @start_date) ELSE vt.OUTPUNCHDTM END)
         END) AS Staff_Minutes_Per_Day
FROM nums3 n3
LEFT OUTER JOIN dbo.vp_timesheetpunch vt ON
    vt.INPUNCHDTM < DATEADD(HOUR, n3.num + 1, @start_date) AND
    vt.OUTPUNCHDTM >= DATEADD(HOUR, n3.num, @start_date)
WHERE n3.num <= (@num_hours - 1)
GROUP BY
    CONVERT(char(8), DATEADD(HOUR, n3.num, @start_date), 112),
    vt.LABORLEVEL1,
    CONVERT(char(2), DATEADD(HOUR, n3.num, @start_date), 8) + ':00',
    CONVERT(char(2), DATEADD(HOUR, n3.num + 1, @start_date), 8) + ':00'
ORDER BY
    1, 3, 4

Open in new window

Author

Commented:
I will split the points, as both of you made a great contribution. Thank you very much for assisting!