Get working hours in minutes based on shift schedule
We have 3 shifts in production. Each Shift sync is described in the tbl_ShiftSched table:
WT - working time, PT-break time. ShiftTmID - schedule for 2 and 3 shifts. I am looking for an easy way to get working hours in minutes with start and end times. For example, input between # 2015.05.29 06: 10: 00 # and # 2015.05.29 09: 30: 00 # and tbl_WorkStations.WksID = 'GRD' (workstation code with relation by ShiftTmID = '3P) should produce 190 min output ...
I have a function in MS Access that gives me the information I need. But when porting to T-SQL, it gets very tricky because I don't find an easy way to use an alias in T-SQL. Here is the code:
USE [STRDAT]
GO
declare
@strWks varchar(3),
@dteIN datetime='2013.08.05 03:30',
@dteOUT datetime='2013.08.05 05:30',
@strShf varchar(12)=null,--'2013.08.04-3',
@strInd varchar(2) = 'WT',
@dteFTm datetime,
@dteShf date
--@PrdS datetime,
--@PrdE datetime
select top 1
@dteFTm =
case
when @strShf is not null
then (select shiftstart from tbl_ShiftSched where ShiftTmID=(select ShiftTiming from tbl_WorkStations where WksID=@strWks) and shift=right(@strshf,1) and sortind=1)
else @dteIN-dateadd(day,datediff(day,0,@dteIN),0) --CAST(@dteIN-cast(floor(@dteIN) as float) as datetime)
end,
@dteShf=
case
when @strShf is not null
then left(@strShf,10)
else convert(varchar,dateadd(day,datediff(day,0,@dteIN),0),102)
end
--select @dteftm,@dteShf
SELECT tbl_ShiftSched.Shift,
tbl_ShiftSched.SortInd,
[ShiftStart]+
case
when @dteFTm>[shiftstart]
then DateAdd(day,1,@dteShf)
else @dteShf
end AS PrdS,
[ShiftEnd]+
case
when @dteFTm>[shiftend]
then DateAdd(day,1,@dteShf)
else @dteShf
end AS PrdE,
case
when @dteIN>=[PrdS] AND [PrdE]>=@dteOUT
then DateDiff(minute,@dteIN,@dteOUT)
else case
when @dteIN<=[PrdS] AND [PrdE]<=@dteOUT
then DateDiff(minute,[PrdS],[PrdE])
else case
when [PrdS]<=@dteIN AND @dteIN<=[PrdE]
then DateDiff(minute,@dteIN,[Prde])
else case
when [PrdS]<=@dteOUT AND @dteOUT<=[PrdE]
then DateDiff(minute,[Prds],@dteOUT)
else 0
end
end
end
end AS Tm,
@dteIN AS S,
@dteOUT AS E,
tbl_ShiftSched.ShiftType,tbl_ShiftSched.ShiftStart,tbl_ShiftSched.ShiftEnd
FROM tbl_WorkStations
INNER JOIN tbl_ShiftSched ON tbl_WorkStations.ShiftTiming = tbl_ShiftSched.ShiftTmID
WHERE (((tbl_WorkStations.WksID)=@strWks))
Of course this gives me the error Invalid column name "PrdS" and "PrdE" because I am using an alias.
There must be an easier way to achieve it. Maybe I'm in the wrong direction? ...
source to share
Whenever I have to calculate a field and use the results in the second field, I use a common table expression for the first calculation. Given this request, it might look like this:
with cte_preprocess as
(
SELECT tbl_ShiftSched.Shift,
tbl_ShiftSched.SortInd,
[ShiftStart]+
case
when @dteFTm>[shiftstart]
then DateAdd(day,1,@dteShf)
else @dteShf
end AS PrdS,
[ShiftEnd]+
case
when @dteFTm>[shiftend]
then DateAdd(day,1,@dteShf)
else @dteShf
end AS PrdE,
tbl_ShiftSched.ShiftType,tbl_ShiftSched.ShiftStart,tbl_ShiftSched.ShiftEnd
FROM tbl_WorkStations
INNER JOIN tbl_ShiftSched ON tbl_WorkStations.ShiftTiming = tbl_ShiftSched.ShiftTmID
WHERE (((tbl_WorkStations.WksID)=@strWks))
)
SELECT [Shift]
, SortInd
, PrdS
, PrdE
, case
when @dteIN>=[PrdS] AND [PrdE]>=@dteOUT
then DateDiff(minute,@dteIN,@dteOUT)
else case
when @dteIN<=[PrdS] AND [PrdE]<=@dteOUT
then DateDiff(minute,[PrdS],[PrdE])
else case
when [PrdS]<=@dteIN AND @dteIN<=[PrdE]
then DateDiff(minute,@dteIN,[Prde])
else case
when [PrdS]<=@dteOUT AND @dteOUT<=[PrdE]
then DateDiff(minute,[Prds],@dteOUT)
else 0
end
end
end
end AS Tm
, @dteIN
, @dteOUT
, ShiftEnd
FROM cte_preprocess
More about CTE here
source to share