Get work time in minutes based on shift schedule

Question

In production we have 3 shifts. Each Shift timing is described in table tbl_ShiftSched:

WT - work time, PT - break time. ShiftTmID - schedule for 2 and for 3 shifts. I am looking for easy way to get work time in minutes having start and end time. For example, having input between #2015.05.29 06:10:00# and #2015.05.29 09:30:00# and tbl_WorkStations.WksID='GRD' (Workstation code with relation on ShiftTmID ='3P') should give output 190 min.

I have function in MS Access which gives me needed output. But when migrated to T-SQL it becomes very complicated because I do not find easy way how to use 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))

Off course it gives me an error Invalid column name 'PrdS' and 'PrdE' because I use alias.

Must be some more easy way to achieve it. Maybe I am on wrong direction?...

Answer 1

Whenever I have to calculate a field and use the results in a second field, I use a common table expression to make the first calculation. Given this query, it could 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 on CTE's here