Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009

    Unanswered: Labor Report - $ per hour

    Hello all,

    Forgive me if I miss something, I'm new to both SQL and these forums. I've been teaching myself over the past couple of months, but my boss hit me with something that's had me spinning tires all week. I have 2 tables that hold employee schedule info. dbo.Schedule contains clock in, clock out, and total hours . dbo.ScheduleHours contains job id, regular pay hours, overtime pay hours, and pay rate.

    I need to make a report with an hourly labor $ figure. I've made it work for shifts where the employee only has one payrate, but handling mixed shifts is killing me. Note: Hour fields are represented in integer format as seconds.

    CREATE TABLE [dbo].[Schedule]([SiteNumber] [int] NOT NULL,[StoreID] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[SubPeriod] [int] NOT NULL,[InternalEmployeeNumber] [int] NOT NULL,[PublicEmployeeNumber] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[JobNumber] [int] NOT NULL,[JobID] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[JobName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[SchdDate] [datetime] NOT NULL,[ClockIn] [int] NOT NULL,[ClockOut] [int] NULL,[Hours] [decimal](10, 3) NULL,CONSTRAINT [PK_Schedule] PRIMARY KEY NONCLUSTERED [SiteNumber] ASC,[InternalEmployeeNumber] ASC,[SchdDate] ASC,[ClockIn] ASC (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] 
    CREATE TABLE [dbo].[ST_ScheduleHours]( [SiteNumber] [int] NOT NULL, [StoreID] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [InternalEmployeeNumber] [int] NOT NULL, [PublicEmployeeNumber] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [JobNumber] [int] NOT NULL, [JobName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ScheduleDate] [datetime] NOT NULL, [SubPeriod] [int] NULL, [RegularHours] [decimal](10, 3) NULL, [OverTimeHours] [decimal](10, 3) NULL, [DoubleOTHours] [decimal](10, 3) NULL, [Rate] [decimal](10, 3) NULL, CONSTRAINT [PK_ST_ScheduleHours] PRIMARY KEY NONCLUSTERED ( [SiteNumber] ASC, [InternalEmployeeNumber] ASC, [JobNumber] ASC, [ScheduleDate] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
    INSERT INTO [Schedule] ([SiteNumber],[StoreID],[SubPeriod],[InternalEmployeeNumber],[PublicEmployeeNumber] ,[JobNumber],[JobID],[JobName],[SchdDate],[ClockIn],[ClockOut],[Hours]) VALUES (1008495,123,120,1008536,ABC123,100563,15,Cashier,2009-02-10 00:00:00.000,660,1240,9.5) 
    INSERT INTO ScheduleHours] ([SiteNumber] ,[StoreID] ,[InternalEmployeeNumber] ,[PublicEmployeeNumber] ,[JobNumber] ,[JobName] ,[ScheduleDate] ,[SubPeriod] ,[RegularHours] ,[OverTimeHours] ,[DoubleOTHours] ,[Rate]) VALUES (1008495 ,123 ,1008536 ,ABC123 ,15 ,Cashier ,2009-02-10 00:00:00.000 ,120 ,6.5 ,3 ,0 ,6.75 )
    CREATE FUNCTION dbo.Ufn_rangetobucket
                   (@startHour INT
                    , @endHour INT)
    RETURNS @hourbuckets TABLE(HOUR               DATETIME    NOT NULL
                               , regminutesworked INT    NOT NULL)
      -- body of the function
        SELECT Convert(VARCHAR(5),Dateadd(HOUR,h1.hourofday,0),108)   AS 'Hour'
               , Datediff(MINUTE,-- If clockin occurs within hour of day, set datediff left time to clockin. Else, set it to hour of day.
                                   WHEN Datediff(MINUTE,Dateadd(HOUR,h1.hourofday,0),Convert(VARCHAR,Dateadd(n,@startHour,0),108)) BETWEEN 0 AND 59 THEN Convert(VARCHAR,Dateadd(n,@startHour,0),108)
                                   ELSE Dateadd(HOUR,h1.hourofday,0)
                                       --If clockout is within hourofday, make that right value. Otherwise goto else
                                       WHEN Datediff(MINUTE,Convert(VARCHAR,Dateadd(n,@endhour,0),108),
                                                     Dateadd(HOUR,h1.hourofday + 1,0)) BETWEEN 0 AND 59 THEN Convert(VARCHAR,Dateadd(n,@endhour,0),108)
                                       ELSE -- If hourofday is outside clockin & clcokout, set rightvalue to hourofday
                                              WHEN Convert(VARCHAR,Dateadd(n,@startHour,0),108) < Convert(VARCHAR,Dateadd(n,@endhour,0),108) THEN CASE
                                                                                                                                                    WHEN Dateadd(HOUR,h1.hourofday,0) <= Dateadd(HOUR,-1,Convert(VARCHAR,Dateadd(n,@startHour,0),108))
                                                                                                                                                          OR Dateadd(HOUR,h1.hourofday,0) >= Convert(VARCHAR,Dateadd(n,@endhour,0),108) THEN Dateadd(HOUR,h1.hourofday,0)
                                                                                                                                                    ELSE Dateadd(HOUR,h1.hourofday + 1,0)
                                              --If hourofday is less than or equal to clockin-1 and greater than or equal to clockout, set hourofday + 1 (make value 1)
                                            ELSE CASE
                                                     WHEN Dateadd(HOUR,h1.hourofday,0) <= Dateadd(HOUR,-1,Convert(VARCHAR,Dateadd(n,@startHour,0),108))
                                                          AND Dateadd(HOUR,h1.hourofday,0) = Convert(VARCHAR,Dateadd(n,@endhour,0),108) THEN Dateadd(HOUR,h1.hourofday,0)
                                                     ELSE Dateadd(HOUR,h1.hourofday + 1,0)
                                     END) 'regminutesworked'
        FROM   sandbox.dbo.tmp_hours h1
        -- copy the required columns to the result of the function
    This is a function I found while researching various sites. It splits my clockin/clockout ranges into buckets.

    The following snippet works as intended for single-payrate employees, breaking their shifts down into 1hour buckets and returning it in table form. However, when I try to build up union alls with case statement filters to handle overtime and mixed rate employees, I start getting cartesian aggreggates or something. It'll show 18000 hours of labor for a store that only has 300:

    DECLARE @delis int, @startdate datetime, @enddate datetime
    SET @storeid = 4
    SET @startdate = cast('1/19/2009' as datetime)
    SET @enddate = cast('1/19/2009' as datetime)
    --Bucket all employees with no overtime together
    select distinct
    	,(rb.minworked) 'TotalMinutes'
    		WHEN otmins <=0 THEN ((regmins/60)*regpay)
    	select otpay, regpay, regmins, otmins, clockin, clockout from dbo.shiftlist where storeid in(@delis) and schddate between @startdate and @enddate and otmins) s
    cross apply 
    dbo.ufn_shifthours(s.clockin, s.clockout) rb

    I can't find the cause, I've spent the past 24 hours searching.
    Any help would be appreciated! Here's the goal:

    0	0	0.000
    1	0	0.000
    2	0	0.000
    3	0	0.000
    4	0	0.000
    5	30	0.000
    6	510	59.000
    7	3120	364.000
    8	5850	840.500
    9	7020	994.550
    10	9660	1224.650
    11	11145	1556.400
    12	11235	1578.700
    13	10410	1447.150
    14	5520	725.600
    15	3015	357.650
    16	3945	507.800
    17	5805	755.950
    18	6120	824.500
    19	6060	815.500
    20	5490	740.250
    21	2940	388.300
    22	1815	200.500
    23	285	0.000

  2. #2
    Join Date
    Feb 2009
    Sorry, forgot headers on the columns. it's " HourOfDay --- MinutesWorked --- Labor$"

    Also, this is on sql server 2k5 sp3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts