asked on

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

MohammedU

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

intimenordic

ASKER

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

MohammedU

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

intimenordic

ASKER

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..

Lowfatspread

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

intimenordic

ASKER

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 Pletcher

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?

intimenordic

ASKER

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

SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

intimenordic

ASKER

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