Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
Select WorkShiftId,
Sum(DurationSecQty) As WorkTime,
DateDiff(Second, Min(StartDTM), Max(EndDTM)) - Sum(DurationSecQty) As BreakTime
From VP_TIMESHEETPUNCH
Where VP_TIMESHEETPUNCH.EVENTDATE >= '2008-06-01 00:00:00.000'
AND VP_TIMESHEETPUNCH.EVENTDATE < '2008-06-02 00:00:00.000'
AND VP_TIMESHEETPUNCH.PERSONNUM = '099136'
Now I need each break calculated though for each shift between each line. Not a sum of the breaks for a day.
Declare @Temp
Table (RowId Int Identity(1,1),
WorkedShiftId Int,
StartDTM DateTime,
EndDTM DateTime
Primary Key (WorkedShiftId, StartDTM))
Insert Into @Temp(WorkedShiftId, StartDTM, EndDTM)
Select WorkedShiftId, StartDTM, EndDTM
From TimeSheetItem
[!]Order By WorkedShiftID, StartDTM[/!]
Select A.WorkedShiftId,
A.EndDTM As BreakStart,
B.StartDTM As BreakEnd,
DateDiff(Second, A.EndDTM, B.StartDTM)
From @Temp As A
Inner Join @Temp As B
On A.WorkedShiftId = B.WorkedShiftId
And [!]A.RowId = B.RowId - 1[/!]
Where A.EventDTM = '2008-06-01'
Declare @Temp
Table (RowId Int Identity(1,1),
WorkedShiftId Int,
StartDTM DateTime,
EndDTM DateTime
Primary Key (WorkedShiftId, StartDTM))
Insert Into @Temp(WorkedShiftId, StartDTM, EndDTM)
Select WorkedShiftId, StartDTM, EndDTM
From TimeSheetItem
Order By WorkedShiftID, StartDTM
Select A.WorkedShiftId,
A.EndDTM As BreakStart,
B.StartDTM As BreakEnd,
DateDiff(Second, A.EndDTM, B.StartDTM)
From @Temp As A
Inner Join @Temp As B
On A.WorkedShiftId = B.WorkedShiftId
And A.RowId = B.RowId - 1
Where A.StartDTM >= '2008-06-01'
A.StartDTM < '2008-06-02'
Declare @Temp
Table (RowId Int Identity(1,1) Primary Key,
WorkedShiftId Int,
StartDTM DateTime,
EndDTM DateTime)
-- Primary Key (WorkedShiftId, StartDTM))
Insert Into @Temp(WorkedShiftId, StartDTM, EndDTM)
Select WorkedShiftId, StartDTM, EndDTM
From TimeSheetItem
where workedshiftid is not null
Order By WorkedShiftID, StartDTM
Select A.rowid,
B.rowid,
A.WorkedShiftId,
A.EndDTM As BreakStart,
B.StartDTM As BreakEnd,
DateDiff(Second, A.EndDTM, B.StartDTM) As BreakDuration,
TotalAlias.BreakTime As TotalBreakTime
From @Temp As A
Inner Join @Temp As B
On A.WorkedShiftId = B.WorkedShiftId
And A.RowId = B.RowId - 1
Inner Join [!]([/!]
Select WorkShiftId,
Sum(DurationSecQty) As WorkTime,
DateDiff(Second, Min(StartDTM), Max(EndDTM)) - Sum(DurationSecQty) As BreakTime
From VP_TIMESHEETPUNCH
Where VP_TIMESHEETPUNCH.EVENTDATE >= '2008-06-01 00:00:00.000'
AND VP_TIMESHEETPUNCH.EVENTDATE < '2008-06-02 00:00:00.000'
AND VP_TIMESHEETPUNCH.PERSONNUM = '099136'
[!]) As TotalAlias
On A.WorkedShiftId = TotalAlias.WorkShiftId[/!]
Where A.StartDTM >= '2008-06-01'
and A.StartDTM < '2008-06-02'
Declare @Temp
Table (RowId Int Identity(1,1) Primary Key,
WorkedShiftId Int,
PersonFullName Text,
InPunchDTM DateTime,
OutPunchDTM DateTime)
-- Durationsecsqty Int)
-- Primary Key (WorkedShiftId, StartDTM))
Insert Into @Temp(WorkedShiftId, InPunchDTM, OutPunchDTM, PersonFullName)
Select a.WorkedShiftId,
b.InPunchDTM,
b.OutPunchDTM,
b.PersonFullName
-- a.durationsecsqty
From TimeSheetItem a,
Vp_Timesheetpunch b
where a.workedshiftid is not null
And a.timesheetitemid = b.timesheetitemid
Order By a.WorkedShiftID, b.InPunchDtm
Select --A.rowid,
--B.rowid,
A.PersonFullName,
A.WorkedShiftId,
A.OutPunchDTM As BreakStart,
B.InPunchDTM As BreakEnd,
DateDiff(Second, A.OutPunchDTM, B.InPunchDTM) As BreakTime[!],
TotalAlias.WorkTime[/!]
-- Sum(A.DurationSecsQty) As totalalias
From @Temp As A
Inner Join @Temp As B
On A.WorkedShiftId = B.WorkedShiftId
And A.RowId = B.RowId - 1
Inner Join (
Select
TIMESHEETITEM.WorkedShiftId,
Sum(TIMESHEETITEM.DurationSecsQty) as [blue]WorkTime[/blue]
From WFC_PRD.dbo.TIMESHEETITEM,
WFC_PRD.dbo.VP_TIMESHEETPUNCH
Where VP_TIMESHEETPUNCH.TIMESHEETITEMID = TIMESHEETITEM.TIMESHEETITEMID
AND VP_TIMESHEETPUNCH.EVENTDATE >= '2008-06-01 00:00:00.000'
AND VP_TIMESHEETPUNCH.EVENTDATE < '2008-06-02 00:00:00.000'
group by TIMESHEETITEM.WorkedShiftId
) As [blue]TotalAlias[/blue]
On A.WorkedShiftId = TotalAlias.WorkedShiftId
Where A.InPunchDTM >= '2008-06-01'
and A.InPunchDTM < '2008-06-02'