SQL查询需求：依据trackout_datetime匹配对应work_week

需求说明 #

我的数据表包含shift_start_datetime、shift_end_datetime、trackout_datetime和work_week列，需要在主查询中添加条件：当trackout_datetime落在shift_start_datetime与shift_end_datetime区间内时，返回对应的work_week。

示例班次表 #

预期结果 #

当trackout_datetime为2022-11-16 21:00:00时，应返回：

错误尝试 #

我写了如下条件，但逻辑有误：

where (flh.tracked_out_datetime >= e.shift_start_datetime and flh.tracked_out_datetime < e.shift_end_datetime) = e.[Work Week]

完整查询代码 #

declare @area OID, @time datetime
select @area=(select area_OID from reference.dbo.FP_area where area_name = 'F10 DIFFUSION')
select @time = dateadd(week, -4, getdate())


select distinct * 
into #datetime2 
from (
    select distinct coalesce
        (wws.shift_begin_datetime, wws.shift_end_datetime, eqmhist.shift_start_datetime) as shift_start_datetime, shift_end_datetime, wws.work_week
    from reference.dbo.ww_shift as wws
    inner join OPS_IMP_METRICS.OI_METRICS.shift_equip_planning_data as eqmhist on wws.shift_begin_datetime = eqmhist.shift_start_datetime --ehist
    --where wws.shift_begin_datetime = eqmhist.shift_start_datetime
    ) as dt2
--where shift_start_datetime > @time
--order by dt2.work_week asc
select distinct * from #datetime2

--copies data from one table into a new table
select distinct
    dt2.shift_start_datetime,
    dt2.shift_end_datetime,
    eqmhist.WS_OID,
    eqmhist.equip_OID,
    trim(e.equip_id) as [Equid ID], --removes spaces
    trim(ws.WS_name) as [WS Name],
    dt2.work_week as [Work Week]
into #eqm_hist --workdstation to tool mapping
from OPS_IMP_METRICS.OI_METRICS.shift_equip_planning_data eqmhist
inner join equip_tracking_DSS.dbo.equipment e on eqmhist.equip_OID = e.equip_OID --change equip_tracking_DSS.dbo.equipment to variable e and inner join using equip_OID
inner join reference.dbo.FP_WS ws on ws.WS_OID = eqmhist.WS_OID --change reference.dbo.FP_WS to variable ws and inner join using WS_OID
inner join #datetime2 dt2 on dt2.shift_start_datetime = eqmhist.shift_start_datetime 
    --and dt2.shift_start_datetime = eqmhist.shift_start_datetime
where eqmhist.shift_start_datetime > @time
and area_OID = @area


select distinct * from #eqm_hist 

select distinct
    (b.batch_id) as [Batch ID],
    substring(rtrim(flh.part_type_code),1,4) as [DID],
    rtrim(b.lot_id) as [Lot ID],
    flh.lot_out_qty as [Qty],
    rtrim(s.step_name) as [Step],
    rtrim(flh.tracking_interface_id) as [Tool],
    [WS Name],
    flh.tracked_in_datetime as [Track In Datetime],
    flh.tracked_out_datetime as [Track Out Datetime],
    flh.staged_datetime as [Running Datetime],
    e.shift_start_datetime as [Shift Start],
    e.shift_end_datetime as [Shift End],
    e.[Work Week]
into #main -- base table that shows the detail of each batch
from fab_lot_extraction.dbo.fab_lot_hist flh
inner join fab_lot_extraction.dbo.batch_run_lot b on flh.trav_step_OID = b.trav_step_OID
    and flh.lot_id = b.lot_id
    and b.fab_lot_hist_OID = flh.fab_lot_hist_OID
    and flh.part_type_code != 'TW'
    and (b.inserted_datetime = (select max(inserted_datetime) from fab_lot_extraction.dbo.batch_run_lot b2 where b2.lot_id = b.lot_id and b2.trav_step_OID = b.trav_step_OID and b2.fab_lot_hist_OID = b.fab_lot_hist_OID))
inner join #eqm_hist e on e.[Equid ID] = flh.tracking_interface_id
    and tracking_interface_id <> 'Global Sampling Framework'
inner join traveler.dbo.trav_step ts on ts.trav_step_OID = b.trav_step_OID
inner join traveler.dbo.traveler t on t.trav_OID = ts.trav_OID
inner join traveler.dbo.step s on s.step_OID = ts.step_OID
inner join traveler.dbo.step_data_for_fab sdff on sdff.step_OID = s.step_OID
inner join reference.dbo.mfg_area a on a.mfg_area_OID = sdff.mfg_area_OID
where flh.staged_datetime > @time
and (flh.tracked_out_datetime >= e.shift_start_datetime and flh.tracked_out_datetime < e.shift_end_datetime) --= e.[Work Week]
and exists (
    select distinct eh.[Equid ID] from #eqm_hist eh where eh.[Equid ID] = flh.tracking_interface_id)
order by [Batch ID]

select distinct * from #main

修正方案 #

你的错误在于把布尔条件和work_week做等值判断，逻辑完全不成立。正确的处理方式有两种：

方式1：将时间条件移到JOIN关联中（推荐） #

把时间匹配逻辑放到#eqm_hist的关联条件里，这样能提前过滤掉不匹配的班次，提升查询效率，同时避免无关的匹配结果：

inner join #eqm_hist e 
    on e.[Equid ID] = flh.tracking_interface_id
    and flh.tracked_out_datetime >= e.shift_start_datetime 
    and flh.tracked_out_datetime < e.shift_end_datetime
    and tracking_interface_id <> 'Global Sampling Framework'
...
where flh.staged_datetime > @time
and exists (
    select distinct eh.[Equid ID] from #eqm_hist eh where eh.[Equid ID] = flh.tracking_interface_id)
order by [Batch ID]

方式2：修正WHERE条件 #

直接删除错误的= e.[Work Week]部分，保留时间区间判断即可：

where flh.staged_datetime > @time
and flh.tracked_out_datetime >= e.shift_start_datetime 
and flh.tracked_out_datetime < e.shift_end_datetime
and exists (
    select distinct eh.[Equid ID] from #eqm_hist eh where eh.[Equid ID] = flh.tracking_interface_id)
order by [Batch ID]

两种方式都能实现需求：当trackout_datetime落在对应班次的时间区间内时，返回该班次的work_week。

内容的提问来源于stack exchange，提问作者sheesh