sql - 根据轮类时间表以分钟为单位获取工作时间

Question

在生产中，我们有 3 个类次。每个 Shift 时间在表 tbl_ShiftSched 中描述:

WT - 工作时间，PT - 休息时间。ShiftTmID - 2 类和 3 类的时间表。我正在寻找简单的方法来在几分钟内获得开始和结束时间的工作时间。例如，在 #2015.05.29 06:10:00# 和 #2015.05.29 09:30:00# 和 tbl_WorkStations.WksID='GRD' 之间输入(与 ShiftTmID ='3P' 相关的工作站代码)应该给出输出190 分钟。

我在 MS Access 中有功能，可以提供我需要的输出。但是当迁移到 T-SQL 时，它变得非常复杂，因为我没有找到如何在 T-SQL 中使用别名的简单方法。这是代码:

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

当然它会给我一个错误 Invalid column name 'PrdS' and 'PrdE' because I use alias.

必须有一些更简单的方法来实现它。也许我走错方向了？...

Answer 1

每当我必须计算一个字段并在第二个字段中使用结果时，我都会使用一个公共(public)表表达式来进行第一个计算。给定这个查询，它可能看起来像这样:

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

关于 CTE 的更多信息 here