Time Clock - Basic - Employee Entities (not users)

Version 4 V4 Tutorial
2

CUSTOM REPORTS using SQL for Employee Hours and Wages

:warning: Requires SQLEXPRESS 2012. The SQL script contains a function called format() which is unavailable in SQLEXPRESS 2008, and may be unavailable in SQL CE. If you are not running SQLEXPRESS 2012, you can edit the script to remove the format() function, but the results will be represented in a less-friendly manner. For example, this line:

Left(format(sl.[StartStateDate],'D'),3) as [Day] would become this:
Left(sl.[StartStateDate],3) as [Day]

The preferred method would be to simply upgrade to SQLEXPRESS 2012

Database Tools Import Files:

Hours:
Timeclock_Reports_SQL_Hours.zip (1.6 KB)

Wages:
Timeclock_Reports_SQL_Wages.zip (2.2 KB)

Set up your Employee Entities as such:

Each Employee Entity has 3 Rates defined:
Rate1: Regular Hourly Rate
Rate2: Overtime Hourly Rate, when hours worked per day > 8
Rate3: Holiday Hourly Rate (double Regular rate)

Create an Action to store the HolidayFlag:

Create 2 Rules that fire when SambaPOS is started to set the HolidayFlag:

04f888c36086bab4.png964×519 39.3 KB

Hours:

Template:

[Current:13,12, 12, 12, 20, 16, 7]
@@EmployeePunchInLatest:Employee,Myrna
[Previous:13,12, 12, 12, 20, 16, 7]
>Date|Day|Start|End|Hours|OT|H

@@EmployeeHoursDaily:Employee,Myrna
>@@EmployeeHoursTTL:Employee,Myrna

SQL:

Latest Punchin SQL: @@EmployeePunchInLatest

use [SambaPOS4]

declare @StartDate date = '{Start}'
--declare @StartDate date = left(CONVERT(VARCHAR(10), GETDATE(), 120),7)+'-01'
declare @EndDate date = '{End}'
--declare @EndDate date = dateadd(Day,15,@StartDate)
declare @Now datetime = GETDATE()

IF DateDiff(DAY, @StartDate, @EndDate) < 2
BEGIN
set @StartDate= left(CONVERT(VARCHAR(10), GETDATE(), 120),7)+'-01'
set @EndDate  = dateadd(Month,1,@StartDate)
END

-- set Employee Name
declare @Employee varchar(20) = '@2'
declare @HolidayFlag varchar(20) = 'Holiday'
declare @EntityType varchar(20) = '@1'
declare @Clockin varchar(20) = 'PunchIn'
-- set Overtime Hour Limit
declare @HourLimit decimal(4,2) = 8.00

-- NOTHING TO SET BEYOND HERE

SELECT
 substring(convert(varchar(19),sl.[EndStateDate],120),9,2) as [Date]
,Left(format(sl.[EndStateDate],'D'),3) as [Day]
,substring(convert(varchar(19),sl.[EndStateDate],120),12,5) as [Start]
,substring(convert(varchar(19),@Now,120),12,5) as [End]
,format((DateDiff(SECOND, sl.[EndStateDate], @Now)/60.0/60.0),'0.00') as [Hours]
, CASE WHEN (DateDiff(SECOND, sl.[EndStateDate], @Now)/60.0/60.0 > @HourLimit) THEN (format((DateDiff(SECOND, sl.[EndStateDate], @Now)/60.0/60.0-@HourLimit),'0.00')) ELSE '0' END as [OTH]
, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 1 ELSE 0 END as [H]

FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]
WHERE 1=1
and sl.[Id]=(select max([Id]) from [EntityStateLogs] where [EntityId]=sl.[EntityId] and [EndState]=@Clockin)
and [EndStateDate]>(select max([StartStateDate]) from [EntityStateLogs] where [EntityId]=sl.[EntityId] and [StartState]=@Clockin)
and sl.[StartStateDate] >= @StartDate
and sl.[EndStateDate]   <  @EndDate
and et.[EntityName]     =  @EntityType
and sl.[EndState]       =  @Clockin
and e.[Name]=@Employee
ORDER BY e.[Name], sl.[StartStateDate]

Daily SQL: @@EmployeeHoursDaily

use [SambaPOS4]

declare @StartDate date = '{Start}'
--declare @StartDate date = left(CONVERT(VARCHAR(10), GETDATE(), 120),7)+'-01'
declare @EndDate date = '{End}'
--declare @EndDate date = dateadd(Day,15,@StartDate)

IF DateDiff(DAY, @StartDate, @EndDate) < 2
BEGIN
set @StartDate= left(CONVERT(VARCHAR(10), GETDATE(), 120),7)+'-01'
set @EndDate  = dateadd(Month,1,@StartDate)
END

-- set Employee Name
declare @Employee varchar(20) = '@2'
declare @HolidayFlag varchar(20) = 'Holiday'
declare @EntityType varchar(20) = '@1'
declare @Clockin varchar(20) = 'PunchIn'
declare @RateField1 varchar(20) = 'Rate1'
-- set Overtime Hour Limit
declare @HourLimit decimal(4,2) = 8.00

-- NOTHING TO SET BEYOND HERE

SELECT
 substring(convert(varchar(19),sl.[StartStateDate],120),9,2) as [Date]
,Left(format(sl.[StartStateDate],'D'),3) as [Day]
,substring(convert(varchar(19),sl.[StartStateDate],120),12,5) as [Start]
,substring(convert(varchar(19),sl.[EndStateDate],120),12,5) as [End]
,format((DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0),'0.00') as [Hours]
, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN (format((DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit),'0.00')) ELSE '0' END as [OTH]
, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 1 ELSE 0 END as [H]

FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]
WHERE 1=1
and sl.[StartStateDate] >= @StartDate
and sl.[EndStateDate]   <  @EndDate
and et.[EntityName]     =  @EntityType
and sl.[StartState]     =  @Clockin
and e.[Name]=@Employee
ORDER BY e.[Name], sl.[StartStateDate]

Monthly SQL: @@EmployeeHoursTTL

use [SambaPOS4]

-- set START and END date for Pay Period
declare @StartDate date = '{Start}'
--declare @StartDate date = left(CONVERT(VARCHAR(10), GETDATE(), 120),7)+'-01'
--declare @StartDate date = '2014-09-01'

--declare @EndDate date = dateadd(Month,1,@StartDate)
declare @EndDate date = '{End}'
--declare @EndDate date = dateadd(Day,15,@StartDate)

IF DateDiff(DAY, @StartDate, @EndDate) < 2
BEGIN
set @StartDate= left(CONVERT(VARCHAR(10), GETDATE(), 120),7)+'-01'
set @EndDate  = dateadd(Month,1,@StartDate)
END

-- set Employee Name
declare @Employee varchar(20) = '@2'
declare @HolidayFlag varchar(20) = 'Holiday'
declare @EntityType varchar(20) = '@1'
declare @Clockin varchar(20) = 'PunchIn'
-- set Overtime Hour Limit
declare @HourLimit decimal(4,2) = 8.00

-- NOTHING TO SET BEYOND HERE

SELECT
 left(convert(date,[Date],120),4)
,left(convert(datetime,[Date],107),3)
,'',''
, format(sum([Hours]),'###.00') as [Hours]
, format(sum([OTH]),'###.00') as [OTH]
, sum([H]) as [H]
FROM (

SELECT
 sl.[StartStateDate] as [Date]
,Left(format(sl.[StartStateDate],'D'),3) as [Day]
,substring(convert(varchar(19),sl.[StartStateDate],120),12,5) as [Start]
,substring(convert(varchar(19),sl.[EndStateDate],120),12,5) as [End]
,(DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0) as [Hours]
, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit) ELSE 0 END as [OTH]
, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 1 ELSE 0 END as [H]

FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]

WHERE 1=1
and sl.[StartStateDate] >= @StartDate
and sl.[EndStateDate]   <  @EndDate
and et.[EntityName]     =  @EntityType
and sl.[StartState]     =  @Clockin
and e.[Name]=@Employee
) Daily
WHERE 1=1
GROUP BY left(convert(date,[Date],120),4), left(convert(datetime,[Date],107),3)

You can add the hourly Reports to your Timeclock Entity Screen using the Custom Report Viewer Widget:

Wages:

:heavy_exclamation_mark: :warning: The big caveat with this is How do you calculate Pay?
The SQL contained herein is responsible for calculating Wages, so you (might) need to alter it. The calculations are part of a CASE Statement and it is clearly commented in the code as such:

-- HOLIDAY PAY
-- OVERTIME PAY
-- REGULAR PAY

Template:

[Payroll:9,9,15, 14, 20, 7, 10, 7, 10]
>Beg|End|Employee|Hours|Pay|OTD|OTH|HD|HH
@@Payroll15
>@@PayrollTTL15
@@Payroll30
>@@PayrollTTL30
>@@PayrollTTL

SQL:

Daily Wages from 1st to 15th: @@Payroll15

use [SambaPOS4]

-- set START and END date for Pay Period
--declare @StartDate date = '2014-09-01'
declare @StartDate date = '{Start}'
--declare @EndDate date = dateadd(Month,1,@StartDate)
declare @EndDate date = '{End}'
--set @EndDate  = dateadd(Day,1,@EndDate)
--declare @EndDate date = dateadd(Day,15,@StartDate)


set @StartDate= left(@StartDate,7)+'-01'
set @EndDate  = dateadd(Day,15,@StartDate)

-- set name of HolidayFlag
declare @HolidayFlag varchar(20) = 'Holiday'
-- set name of Employee Entity Type
declare @EntityType varchar(20) = 'Employee'
-- set name of Clock-In State
declare @Clockin varchar(20) = 'PunchIn'
-- set names of Employee Custom Data Rate Fields
declare @RateField1 varchar(20) = 'Rate1'
declare @RateField2 varchar(20) = 'Rate2'
declare @RateField3 varchar(20) = 'Rate3'
-- set Overtime Hour Limit
declare @HourLimit decimal(4,2) = 8.00

-- NOTHING TO SET BEYOND HERE

SELECT
  right(@StartDate,5) as [Beg]
, right(dateadd(Day,-1,@EndDate),5) as [End]
, [EntName] as [Employee]
, format(sum([Hours]),'##0.00') as [HoursTTL]
, format(sum([PayTTL]),'0.000') as [PayTTL]
, sum([OvertimeDays]) as [OvertimeDays]
, format(sum([OvertimeHours]),'##0.00') as [OvertimeHours]
, sum([HolidayDays]) as [HolidayDays]
, format(sum([HolidayHours]),'##0.00') as [HolidayHours]
FROM (

SELECT
e.[Name] as [EntName]
,sl.[StartStateDate] as [SDateBegin]
,sl.[EndStateDate] as [SDateEnd]
,DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 as [Hours]
,CASE
-- HOLIDAY PAY
    WHEN (@HolidayFlag != '' AND CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 
    DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 *
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField3+'","Value":"',e.[CustomData])+LEN('"'+@RateField3+'","Value":"'),6))
-- OVERTIME PAY
    WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit)    THEN
    (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit) *
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField2+'","Value":"',e.[CustomData])+LEN('"'+@RateField2+'","Value":"'),6)) +
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField1+'","Value":"',e.[CustomData])+LEN('"'+@RateField1+'","Value":"'),6)) * @HourLimit
-- REGULAR PAY
    ELSE
    DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 *
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField1+'","Value":"',e.[CustomData])+LEN('"'+@RateField1+'","Value":"'),6))
END as [PayTTL]

, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN 1 ELSE 0 END as [OvertimeDays]
, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit) ELSE 0 END as [OvertimeHours]

, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 1 ELSE 0 END as [HolidayDays]
, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0) ELSE 0 END as [HolidayHours]

FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]

WHERE 1=1
and sl.[StartStateDate] >= @StartDate
and sl.[EndStateDate]   <  @EndDate
and et.[EntityName]     =  @EntityType
and sl.[StartState]     =  @Clockin
) StateLog

WHERE 1=1
GROUP BY [EntName]
ORDER BY [EntName]

Sum of Wages from 1st to 15th: @@PayrollTTL15

use [SambaPOS4]

-- set START and END date for Pay Period
--declare @StartDate date = '2014-09-01'
declare @StartDate date = '{Start}'
--declare @EndDate date = dateadd(Month,1,@StartDate)
declare @EndDate date = '{End}'
set @EndDate  = dateadd(Day,1,@EndDate)
--declare @EndDate date = dateadd(Day,15,@StartDate)

set @StartDate= left(@StartDate,7)+'-01'
set @EndDate  = dateadd(Day,15,@StartDate)

-- set name of HolidayFlag
declare @HolidayFlag varchar(20) = 'Holiday'
-- set name of Employee Entity Type
declare @EntityType varchar(20) = 'Employee'
-- set name of Clock-In State
declare @Clockin varchar(20) = 'PunchIn'
-- set names of Employee Custom Data Rate Fields
declare @RateField1 varchar(20) = 'Rate1'
declare @RateField2 varchar(20) = 'Rate2'
declare @RateField3 varchar(20) = 'Rate3'
-- set Overtime Hour Limit
declare @HourLimit decimal(4,2) = 8.00

-- NOTHING TO SET BEYOND HERE

SELECT
 left(convert(date,[SDateBegin],120),4)+' '+left(convert(datetime,[SDateBegin],107),3)+' 1-15'
, '',''
, format(sum([HoursTTL]),'##0.00') as [HoursTTL]
, format(sum([PayTTL]),'0000.000') as [PayTTL]
, sum([OvertimeDays]) as [OvertimeDays]
, format(sum([OvertimeHours]),'##0.00') as [OvertimeHours]
, sum([HolidayDays]) as [HolidayDays]
, format(sum([HolidayHours]),'##0.00') as [HolidayHours]
FROM (

SELECT
[SDateBegin]
, sum([Hours]) as [HoursTTL]
, sum([PayTTL]) as [PayTTL]
, sum([OvertimeDays]) as [OvertimeDays]
, sum([OvertimeHours]) as [OvertimeHours]
, sum([HolidayDays]) as [HolidayDays]
, sum([HolidayHours]) as [HolidayHours]
FROM (

SELECT
e.[Name] as [EntName]
,sl.[StartStateDate] as [SDateBegin]
,sl.[EndStateDate] as [SDateEnd]
,DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 as [Hours]
,CASE
-- HOLIDAY PAY
    WHEN (@HolidayFlag != '' AND CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 
    DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 *
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField3+'","Value":"',e.[CustomData])+LEN('"'+@RateField3+'","Value":"'),6))
-- OVERTIME PAY
    WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit)    THEN
    (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit) *
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField2+'","Value":"',e.[CustomData])+LEN('"'+@RateField2+'","Value":"'),6)) +
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField1+'","Value":"',e.[CustomData])+LEN('"'+@RateField1+'","Value":"'),6)) * @HourLimit
-- REGULAR PAY
    ELSE
    DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 *
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField1+'","Value":"',e.[CustomData])+LEN('"'+@RateField1+'","Value":"'),6))
END as [PayTTL]

, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN 1 ELSE 0 END as [OvertimeDays]
, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit) ELSE 0 END as [OvertimeHours]

, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 1 ELSE 0 END as [HolidayDays]
, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0) ELSE 0 END as [HolidayHours]

FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]

WHERE 1=1
and sl.[StartStateDate] >= @StartDate
and sl.[EndStateDate]   <  @EndDate
and et.[EntityName]     =  @EntityType
and sl.[StartState]     =  @Clockin
) StateLog

WHERE 1=1
GROUP BY [EntName],[SDateBegin]
--ORDER BY [EntName]
) emp

GROUP BY left(convert(date,[SDateBegin],120),4)+' '+left(convert(datetime,[SDateBegin],107),3)

Daily Wages from 16th to 31st: @@Payroll30

use [SambaPOS4]

-- set START and END date for Pay Period
--declare @StartDate date = '2014-09-01'
declare @StartDate date = '{Start}'
--declare @EndDate date = dateadd(Month,1,@StartDate)
declare @EndDate date = '{End}'
set @EndDate  = dateadd(Day,1,@EndDate)
--declare @EndDate date = dateadd(Day,15,@StartDate)

set @StartDate= left(@StartDate,7)+'-16'
set @EndDate  = dateadd(Day,15,@StartDate)

-- set name of HolidayFlag
declare @HolidayFlag varchar(20) = 'Holiday'
-- set name of Employee Entity Type
declare @EntityType varchar(20) = 'Employee'
-- set name of Clock-In State
declare @Clockin varchar(20) = 'PunchIn'
-- set names of Employee Custom Data Rate Fields
declare @RateField1 varchar(20) = 'Rate1'
declare @RateField2 varchar(20) = 'Rate2'
declare @RateField3 varchar(20) = 'Rate3'
-- set Overtime Hour Limit
declare @HourLimit decimal(4,2) = 8.00

-- NOTHING TO SET BEYOND HERE

SELECT
  right(@StartDate,5) as [Beg]
, right(@EndDate,5) as [End]
, [EntName] as [Employee]
, format(sum([Hours]),'##0.00') as [HoursTTL]
, format(sum([PayTTL]),'0.000') as [PayTTL]
, sum([OvertimeDays]) as [OvertimeDays]
, format(sum([OvertimeHours]),'##0.00') as [OvertimeHours]
, sum([HolidayDays]) as [HolidayDays]
, format(sum([HolidayHours]),'##0.00') as [HolidayHours]
FROM (

SELECT
e.[Name] as [EntName]
,sl.[StartStateDate] as [SDateBegin]
,sl.[EndStateDate] as [SDateEnd]
,DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 as [Hours]
,CASE
-- HOLIDAY PAY
    WHEN (@HolidayFlag != '' AND CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 
    DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 *
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField3+'","Value":"',e.[CustomData])+LEN('"'+@RateField3+'","Value":"'),6))
-- OVERTIME PAY
    WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit)    THEN
    (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit) *
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField2+'","Value":"',e.[CustomData])+LEN('"'+@RateField2+'","Value":"'),6)) +
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField1+'","Value":"',e.[CustomData])+LEN('"'+@RateField1+'","Value":"'),6)) * @HourLimit
-- REGULAR PAY
    ELSE
    DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 *
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField1+'","Value":"',e.[CustomData])+LEN('"'+@RateField1+'","Value":"'),6))
END as [PayTTL]

, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN 1 ELSE 0 END as [OvertimeDays]
, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit) ELSE 0 END as [OvertimeHours]

, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 1 ELSE 0 END as [HolidayDays]
, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0) ELSE 0 END as [HolidayHours]

FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]

WHERE 1=1
and sl.[StartStateDate] >= @StartDate
and sl.[EndStateDate]   <  @EndDate
and et.[EntityName]     =  @EntityType
and sl.[StartState]     =  @Clockin
) StateLog

WHERE 1=1
GROUP BY [EntName]
ORDER BY [EntName]

Sum of Wages from 16th to 31st: @@PayrollTTL30

use [SambaPOS4]

-- set START and END date for Pay Period
--declare @StartDate date = '2014-09-01'
declare @StartDate date = '{Start}'
--declare @EndDate date = dateadd(Month,1,@StartDate)
declare @EndDate date = '{End}'
set @EndDate  = dateadd(Day,1,@EndDate)
--declare @EndDate date = dateadd(Day,15,@StartDate)

set @StartDate= left(@StartDate,7)+'-16'
set @EndDate  = dateadd(Day,15,@StartDate)

-- set name of HolidayFlag
declare @HolidayFlag varchar(20) = 'Holiday'
-- set name of Employee Entity Type
declare @EntityType varchar(20) = 'Employee'
-- set name of Clock-In State
declare @Clockin varchar(20) = 'PunchIn'
-- set names of Employee Custom Data Rate Fields
declare @RateField1 varchar(20) = 'Rate1'
declare @RateField2 varchar(20) = 'Rate2'
declare @RateField3 varchar(20) = 'Rate3'
-- set Overtime Hour Limit
declare @HourLimit decimal(4,2) = 8.00

-- NOTHING TO SET BEYOND HERE

SELECT
 left(convert(date,[SDateBegin],120),4)+' '+left(convert(datetime,[SDateBegin],107),3)+' 16-30'
, '',''
, format(sum([HoursTTL]),'##0.00') as [HoursTTL]
, format(sum([PayTTL]),'0000.000') as [PayTTL]
, sum([OvertimeDays]) as [OvertimeDays]
, format(sum([OvertimeHours]),'##0.00') as [OvertimeHours]
, sum([HolidayDays]) as [HolidayDays]
, format(sum([HolidayHours]),'##0.00') as [HolidayHours]
FROM (

SELECT
[SDateBegin]
, sum([Hours]) as [HoursTTL]
, sum([PayTTL]) as [PayTTL]
, sum([OvertimeDays]) as [OvertimeDays]
, sum([OvertimeHours]) as [OvertimeHours]
, sum([HolidayDays]) as [HolidayDays]
, sum([HolidayHours]) as [HolidayHours]
FROM (

SELECT
e.[Name] as [EntName]
,sl.[StartStateDate] as [SDateBegin]
,sl.[EndStateDate] as [SDateEnd]
,DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 as [Hours]
,CASE
-- HOLIDAY PAY
    WHEN (@HolidayFlag != '' AND CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 
    DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 *
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField3+'","Value":"',e.[CustomData])+LEN('"'+@RateField3+'","Value":"'),6))
-- OVERTIME PAY
    WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit)    THEN
    (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit) *
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField2+'","Value":"',e.[CustomData])+LEN('"'+@RateField2+'","Value":"'),6)) +
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField1+'","Value":"',e.[CustomData])+LEN('"'+@RateField1+'","Value":"'),6)) * @HourLimit
-- REGULAR PAY
    ELSE
    DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 *
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField1+'","Value":"',e.[CustomData])+LEN('"'+@RateField1+'","Value":"'),6))
END as [PayTTL]

, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN 1 ELSE 0 END as [OvertimeDays]
, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit) ELSE 0 END as [OvertimeHours]

, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 1 ELSE 0 END as [HolidayDays]
, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0) ELSE 0 END as [HolidayHours]

FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]

WHERE 1=1
and sl.[StartStateDate] >= @StartDate
and sl.[EndStateDate]   <  @EndDate
and et.[EntityName]     =  @EntityType
and sl.[StartState]     =  @Clockin
) StateLog

WHERE 1=1
GROUP BY [EntName],[SDateBegin]
--ORDER BY [EntName]
) emp

GROUP BY left(convert(date,[SDateBegin],120),4)+' '+left(convert(datetime,[SDateBegin],107),3)

Monthly Wages Total: @@PayrollTTL

use [SambaPOS4]

-- set START and END date for Pay Period
--declare @StartDate date = '2014-09-01'
declare @StartDate date = '{Start}'
--declare @EndDate date = dateadd(Month,1,@StartDate)
declare @EndDate date = '{End}'
set @EndDate  = dateadd(Day,1,@EndDate)
--declare @EndDate date = dateadd(Day,15,@StartDate)


set @StartDate= left(@StartDate,7)+'-01'
set @EndDate  = dateadd(Month,1,@StartDate)

-- set name of HolidayFlag
declare @HolidayFlag varchar(20) = 'Holiday'
-- set name of Employee Entity Type
declare @EntityType varchar(20) = 'Employee'
-- set name of Clock-In State
declare @Clockin varchar(20) = 'PunchIn'
-- set names of Employee Custom Data Rate Fields
declare @RateField1 varchar(20) = 'Rate1'
declare @RateField2 varchar(20) = 'Rate2'
declare @RateField3 varchar(20) = 'Rate3'
-- set Overtime Hour Limit
declare @HourLimit decimal(4,2) = 8.00

-- NOTHING TO SET BEYOND HERE

SELECT
  left(convert(date,[SDateBegin],120),4)+' '+left(convert(datetime,[SDateBegin],107),3)+' 1-30'
, '',''
, format(sum([HoursTTL]),'##0.00') as [HoursTTL]
, format(sum([PayTTL]),'0000.000') as [PayTTL]
, sum([OvertimeDays]) as [OvertimeDays]
, format(sum([OvertimeHours]),'##0.00') as [OvertimeHours]
, sum([HolidayDays]) as [HolidayDays]
, format(sum([HolidayHours]),'##0.00') as [HolidayHours]
FROM (

SELECT
[SDateBegin]
, sum([Hours]) as [HoursTTL]
, sum([PayTTL]) as [PayTTL]
, sum([OvertimeDays]) as [OvertimeDays]
, sum([OvertimeHours]) as [OvertimeHours]
, sum([HolidayDays]) as [HolidayDays]
, sum([HolidayHours]) as [HolidayHours]
FROM (

SELECT
e.[Name] as [EntName]
,sl.[StartStateDate] as [SDateBegin]
,sl.[EndStateDate] as [SDateEnd]
,DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 as [Hours]
,CASE
-- HOLIDAY PAY
    WHEN (@HolidayFlag != '' AND CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 
    DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 *
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField3+'","Value":"',e.[CustomData])+LEN('"'+@RateField3+'","Value":"'),6))
-- OVERTIME PAY
    WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit)    THEN
    (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit) *
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField2+'","Value":"',e.[CustomData])+LEN('"'+@RateField2+'","Value":"'),6)) +
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField1+'","Value":"',e.[CustomData])+LEN('"'+@RateField1+'","Value":"'),6)) * @HourLimit
-- REGULAR PAY
    ELSE
    DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 *
    convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"'+@RateField1+'","Value":"',e.[CustomData])+LEN('"'+@RateField1+'","Value":"'),6))
END as [PayTTL]

, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN 1 ELSE 0 END as [OvertimeDays]
, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit) ELSE 0 END as [OvertimeHours]

, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 1 ELSE 0 END as [HolidayDays]
, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0) ELSE 0 END as [HolidayHours]

FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]

WHERE 1=1
and sl.[StartStateDate] >= @StartDate
and sl.[EndStateDate]   <  @EndDate
and et.[EntityName]     =  @EntityType
and sl.[StartState]     =  @Clockin
) StateLog

WHERE 1=1
GROUP BY [EntName],[SDateBegin]
--ORDER BY [EntName]
) emp

GROUP BY left(convert(date,[SDateBegin],120),4)+' '+left(convert(datetime,[SDateBegin],107),3)
2 Likes