Time Clock - Basic - Employee Entities (not users)

QMcKay · October 6, 2014, 3:27pm

Basic TimeClock implementation using Employee Entities (not Users) and 2 Entity States called PunchIn and PunchOut.

REQUIRED: SambaPOS v4.1.65 +
REQUIRED: Custom Report Module from SambaMarket.com

Database Tools Import Files:

Automation (Automation Commands, Actions, Rules):
TimeClock_Automation.zip (2.1 KB)

Entity Type, Entity Screen, States
TimeClock_EntityType_EntityScreen_States.zip (1.5 KB)

FLOW:

Button to show TimeClock screen:

TimeClock screen with 4 Employee Entities: (click on an Employee to Clock In or Out)

Ask TimeClock Question:

SETUP:

Entity States - TCStatus - PunchIn & PunchOut:

Entity Type Employee:

Sample Employee Entity:

Entity Screen for Entity Type Employee:

Employee Entity Screen Entity Grid Properties and Settings: ( ❌ )

Automation Command Button: (Click to Show Employee Entity Screen) Here is a character you can use for the Button Header: ( ⏰ ) … copy it from here and paste it into the Button Header field.

Automation Command Button: (Click to Close Entity Screen) Here is a character you can use for the Button Header: ( ❌ ) … copy it from here and paste it into the Button Header field.

Action: Execute Automation Command: (generic)

Action: Ask Question to perform function on Entity: (generic)

Action: Change Ticket Entity: (Show Entity Screen)

Action: Refresh Ticket: (Close Entity Screen)

Action: Load Entity:

Action: Update Entity State:

Action: Log Entity State: (v4.1.65+)

:heavy_exclamation_mark: All Rules that follow require a Default Mapping ( * * * * )

Rule: Show TimeClock Screen: (click Automation Command Button to Show)

Rule: Close TimeClock Screen: (click Automation Command Button to Close)

Rule: Load Employee Entity:

Rule: Ask Question: (Punch Options)

Question: Clock In=PunchIn:Orange;Gray,Clock Out=PunchOut:White;Gray,Cancel=Cancel:Red;Gray

Rule: Update Entity State:

Rule: Log Entity State:

REPORTING

Custom Report Module has Tags that can be used to aggregate the Log data.

Here is some SQL that does a fairly good job:

use [SambaPOS4]

SELECT
 sl.[Id] as [SLid]
,e.[Id] as [EntId]
,e.[Name] as [EntName]
,sb.[GroupName] as [SGroup]
,sl.[StartState] as [StateBegin]
,sl.[EndState] as [StateEnd]
,sl.[StartStateDate] as [SDateBegin]
,sl.[EndStateDate] as [SDateEnd]
,right('00'+convert(varchar(5),DateDiff(s, sl.[StartStateDate], sl.[EndStateDate])/3600),2)+':'+right('00'+convert(varchar(5),DateDiff(s, sl.[StartStateDate], sl.[EndStateDate])%3600/60),2)+':'+right('00'+convert(varchar(5),(DateDiff(s, sl.[StartStateDate], sl.[EndStateDate])%60)),2) as [HMS]
,DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 as [Hours]
--,substring(e.[CustomData],CHARINDEX('"Rate1","Value":"',e.[CustomData])+LEN('"Rate1","Value":"'),6) as [Rate1]
--,substring(e.[CustomData],CHARINDEX('"Rate2","Value":"',e.[CustomData])+LEN('"Rate2","Value":"'),6) as [Rate2]
--,substring(e.[CustomData],CHARINDEX('"Rate3","Value":"',e.[CustomData])+LEN('"Rate3","Value":"'),6) as [Rate3]
--,convert(decimal(7,3),substring(e.[CustomData],CHARINDEX('"Rate1","Value":"',e.[CustomData])+LEN('"Rate1","Value":"'),6)) * DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 as [PayEst]
,sl.[Name] as [SLName]
,sl.[CustomData] as [SCustomData]
FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]
left join [States] sb on sb.[Name]=sl.[StartState]
left join [States] se on se.[Name]=sl.[EndState]
WHERE 1=1
and et.[EntityName]='Employee'
--and sl.[Name]='TimeClock'
--and sb.[GroupName]='TCStatus'
--and sl.[StartState]='PunchIn'
--and sl.[StartState]<>sl.[EndState] 
ORDER BY e.[Name], sl.[EndStateDate] DESC, sl.[StartStateDate]

QMcKay · October 6, 2014, 3:28pm

CUSTOM REPORTS using SQL for Employee Hours and Wages

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:

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: 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)

windinghouse · October 6, 2014, 5:09pm

@QMcKay looking good! will have a go at doing this soon! one question… how do you see what hours each employee has worked ?

Jesse · October 6, 2014, 5:10pm

That would come by reporting. Custom Reports can pull that data. I will be including this in my tutorial hopefully be done with it soon.

QMcKay · October 6, 2014, 5:13pm

As @Jesse mentioned, the Custom Reports will give you this data. Or use SQL…

That said, I would hold off on implementation until you see the method by @Jesse. His flow is handled quite a bit differently, and you may prefer it.

Jesse · October 6, 2014, 5:22pm

I am pretty much ready to publish my tutorial (I kind of did inside my Issue Post) I just wanted to wait for that issue that I reported earlier to get an answer… However that issue does not really apply to the real flow of the system as it would not follow that flow. My system clocks in with user login. My users are tied to the Employee Entities. Unlike @qmckay his are not. Like he said you may wait to see mine before you decide which one to build they are both different approaches.

Plus after seeing both you may decide to build your own.

This reminds me, unless your planning to do it… @qmckay I hope you would not mind me building a copy of your system and modifying the naming so its autonomous and dropping it as a Database Tools file in the Prebuilt Database thread I made.

EDIT: I figured out my issue so I will be posting my tutorial in next few minutes. I will post the file in Prebuilt Database forum category first and then I will release my tutorial and link it to that same file.

QMcKay · October 6, 2014, 6:41pm

This topic is now invisible. It will no longer be displayed in any topic lists. The only way to access this topic is via direct link.

QMcKay · October 6, 2014, 7:35pm

This topic is now visible. It will be displayed in topic lists.

QMcKay · October 7, 2014, 1:28pm

By all means, go for it… unfortunately, I don’t use the DB Tools (I have no need for them) so I never do any exports… maybe this is a good enough reason to install them.

QMcKay · October 7, 2014, 7:47pm

Pay Reports per Employee showing Hours and Wages

This is a supplement to the Wage Summaries from the 2nd Post that you can use on a per-Employee basis, like a Timesheet and Pay Report that the Employee can sign when they are paid.

This Report uses 2 SQL scripts from the 2nd post for the Daily Hours:

@@EmployeeHoursDaily
@@EmployeeHoursTTL

… and 3 additional SQL scripts to calculate Pay:

@@EmployeePay15
@@EmployeePay30
@@EmployeePayTTL

Template: (one for each Employee)

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

[Pay Myrna:20,15, 12, 16, 8, 10, 7, 10]
>Period|Employee|Hours|Pay|OTD|OTH|HD|HH
@@EmployeePay15:Employee,Myrna
@@EmployeePay30:Employee,Myrna
>@@EmployeePayTTL:Employee,Myrna

Employee Pay Summary for 1st to 15th: @@EmployeePay15

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(CONVERT(VARCHAR(10), GETDATE(), 120),7)+'-01'
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) = '@1'
-- set name of Employee
declare @Employee varchar(20) = '@2'
-- 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'
, @Employee
, format(sum([HoursTTL]),'##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
[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 e.[Name]            =  @Employee
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)

Employee Pay Summary for 16th to 31th: @@EmployeePay30

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(CONVERT(VARCHAR(10), GETDATE(), 120),7)+'-16'
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) = '@1'
-- set name of Employee
declare @Employee varchar(20) = '@2'
-- 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'
, @Employee
, format(sum([HoursTTL]),'##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
[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 e.[Name]            =  @Employee
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)

Employee Pay Summary for Month: @@EmployeePayTTL

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(CONVERT(VARCHAR(10), GETDATE(), 120),7)+'-01'
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) = '@1'
-- set name of Employee
declare @Employee varchar(20) = '@2'
-- 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'
, @Employee
, format(sum([HoursTTL]),'##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
[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 e.[Name]            =  @Employee
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)

QMcKay · October 9, 2014, 8:28pm

Updated 2nd Topic post to include Custom Reports (with SQL) for Employee Hours and Wages.

Click the UP arrow on this quote…

allmixedup · October 10, 2014, 9:25pm

Hi

In v4.1.67 I don’t see " Action Type: Log Entity State "

any one know why or is it missing in v4.1.67 ?

QMcKay · October 10, 2014, 9:37pm

It is there…

allmixedup · October 10, 2014, 11:03pm

@ QMcKay

I tired 4.1.65 and 67 still I don’t see that Log Entity State

I am using sql 2012 express and upgrade from .62 to .65. .67

is there any steps to get this ? or it’s built in .65 ?

Jesse · October 10, 2014, 11:17pm

Its there. I to upgraded and its there.

QMcKay · October 11, 2014, 2:37pm

@allmixedup, if you haven’t used the Action Type before, it won’t be Categorized on the existing Action list, so you won’t see it there. You need to Add an Action of that Type. Look at my screenshot again and follow this:

Click Add Action, then in the box for Action Type, start typing “Log Entity…” - it should appear in that list.

Jesse · October 11, 2014, 2:41pm

We figured it out. Its tied to custom reports module. Have to install custom reports to use this action. This makes sense as you would need custom reports to get any benefit.

superlik · November 4, 2014, 10:32am

i followed instruction in the first post when i click the employee name, nothing happened? there’s no button for clock in clock out legend on the right side as well

superlik · November 4, 2014, 11:36am

clicked the time clock inside the ticket no response also?

i rechecked everything…still not working…any tips please?

would that be becoz u didn’t set the automation command for AM TC_LoadEntity automation command ? which u typed it under the entity screen?

antasp3136 · November 4, 2014, 2:48pm

So @Jesse … have you gotten rid of the timetrex integration?.. do you still plan to build a timeclock with this?