I have [shift_start_datetime], [shift_end_datetime], [trackout_datetime] and [work_week] columns in a table.

Basically I want to make a condition into my main query so that if [trackout_datetime] falls in between [shift_start_datetime] and [shift_end_datetime], it will show me which [work_week] that it is in

Sample table:

So let's say that my [trackout_datetime] is '2022-11-16 21:00:00', it falls under the second row of the sample table, so I should get something like this:

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

I tried something like this

Entire query:

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

CodePudding user response：

I don't know what these complex queries should mean. So I just focus on your description and requirement that tells us all data comes from the same table:

I have [shift_start_datetime], [shift_end_datetime], [trackout_datetime] and [work_week] columns in a table. Basically I want to make a condition into my main query so that if [trackout_datetime] falls in between [shift_start_datetime] and [shift_end_datetime], it will show me which [work_week] that it is in

This can be done using BETWEEN:

SELECT 
id,
shift_start_datetime, 
shift_end_datetime, 
trackout_datetime, 
work_week
FROM yourtable
WHERE 
trackout_datetime BETWEEN shift_start_datetime AND shift_end_datetime;

We can verify here this is working correctly: db<>fiddle

If your description is incorrect and you need a query fetching data from different tables, please edit your question and show all affected tables with sample input.