SQL Server Performance

Cumulative Man Hours Query

Discussion in 'ALL SQL SERVER QUESTIONS' started by Michael Parks, Jan 30, 2013.

  1. Michael Parks New Member

    I am looking to create a query from my timeclock database that will show the cumulative staff minutes hour to hour (grouped by department).

    The table I am using is called dbo.timeclock has all the necessary columns (I believe) they are named:

    date_in

    date_out

    time_in

    time_out

    department_desc

    I want to see a column for every hour (12a-1a, 1a-2a,3a-4a, etc) with the total minutes worked by my staff members for each hour. I.e. employee A clocks in at 7am, employee B clocks in at 7:30. Both work until 9 am. The 7-8a colum should reflect 90 minutes total worked, and the 8a-9a column should reflect 120 minutes worked.

    I am not sure how to begin to define this query and the logic that goes with it.

    I would really appreciate guidance/help in writing this one out. Thanks to anyone willing to help on this!

    
  2. davidfarr Member

    To create queries that comprehensively cater for all "date-time in" and "date-time out" scenarios, you would need to consider nightshift staff that clock in, for example, at 8pm on one day and clock out at 6am on the following day.
    Their work hours would then flow over two date periods, working a total of 240 minutes on Day1 and also 360 minutes on Day 2.
    Your expected query result requires a column for every hour (12a-1a, 1a-2a,3a-4a, etc) and I assume one result row to represent each day (7 Feb, 8 Feb, etc), and therefore a single clocktime record in your source table might need to display as two records in your query result to represent the two dates that span this time period in such a case.

    You would also need to define calculation rules (meaningful time parameters that SQL Server can calculate on) for each column to specify that column "1a-2a" implies a time period between 01h00 and 02h00 , and create queries or functions to determine whether the worker's in-out time falls within the time period defined by each column, either entirely (60 minutes) or partially (less than 60 minutes), while also keeping in consideration that some employees times will span not only multiple hour columns but also multiple days/dates.

    In my opinion; there can be no single query that will allow you to extract an accurate linear result directly from your source table.
    Your solution here would require several intermediate staging tables or #temp tables and possibly some user-defined functions to generate values within those tables.
    I believe that I (and a few other forum members) are capable of formulating a complete set of queries and functions to return the exact result that you need, but in all honesty it would take me more time and effort than I am willing to devote to a single forum post.
  3. satya Moderator

    I believe David explained the solution clealry about how to do it, so its your turn to write up the TSQL and show here so that we can save lot of time in fine tuning.

Share This Page