SQL query start time query

Question

so i have a table like this

CREATE TABLE Table_Status
(
Status VARCHAR(10) NOT NULL,
StartTime DATETIME NOT NULL, 
EndTime DATETIME NOT NULL
); 

and the data looks like this: StartTime and Endtime are the time lapse :

Status1 2007-10-16 18:38:25.000 2007-10-17 05:30:22.000 
Status2 2007-10-17 05:30:22.000 2007-10-17 18:48:46.000
Status2 2007-10-17 18:48:46.000 2007-10-17 21:48:46.000
Status1 2007-10-17 21:48:46.000 2007-10-18 08:11:59.000

So, the idea is to SELECT * for any period of time if the user passes two parameters

SET @From = '2007-10-17 00:00:00.000'
SET @To = '2007-10-17 23:59:59.000' 

Somehow it should return the table as follows:

Status1 2007-10-17 00:00:00.000 2007-10-17 05:30:22.000
Status2 2007-10-17 05:30:22.000 2007-10-17 21:48:46.000
Status1 2007-10-17 21:48:46.000 2007-10-17 23:59:59.000

You see, the hard part is to reduce the original time interval to a user-defined time period (@From - @To), I tried my best all day. Please inform.

Thank you so much in advance.

Answer 1

There are two parts to getting the result set you are looking for.

• ""

• , :

  • , /
  • , from/to
  • , from/to

, , :

http://www.sqlservercentral.com/Forums/Topic1364849-392-1.aspx

http://sqlmag.com/blog/solutions-packing-date-and-time-intervals-puzzle

, . , .

, :

with all_times (time_type,date_range_part,status) as (
select 'start',
       starttime,
       status
from table_status
union all
select 'end',
       endtime,      
       status
from table_status),

ordered_starts as (
select date_range_part,
       status,
       row_number() over(partition by status order by date_range_part, time_type desc) as rnboth,
       2*(row_number() over(partition by status,time_type order by date_range_part))-1 as rnstartend
from all_times),

ordered_ends as (
select date_range_part,
       status,
       row_number() over(partition by status order by date_range_part desc,time_type) as rnbothrev,
       2*(row_number() over(partition by status,time_type order by date_range_part desc))-1 as rnstartendrev
from all_times),

starts as (
select date_range_part,
       status,
       row_number() over(partition by status order by date_range_part) as rn
from ordered_starts
where rnboth=rnstartend),

ends as (
select date_range_part,
       status,
       row_number() over(partition by status order by date_range_part) as rn
from ordered_ends
where rnbothrev=rnstartendrev)

select 
s.status,
s.date_range_part [start_time],
e.date_range_part [end_time]
into #table_status_merged
from starts s
inner join ends e on e.status=s.status and e.rn=s.rn and s.date_range_part<=e.date_range_part
order by s.date_range_part;

, ,

declare @from datetime
declare @to datetime

set @from = '2007-10-17 00:00:00.000'
set @to = '2007-10-17 23:59:59.000'

select
[status],
@from,
end_time
from #table_status_merged
where start_time < @from
and end_time <= @to
union all
select
[status],
start_time,
end_time
from #table_status_merged
where start_time >= @from
and end_time <= @to
union all
select
[status],
start_time,
@to
from #table_status_merged
where start_time >= @from
and end_time > @to

drop table #table_status_merged

Answer 2

: , .

SET @From = '2007-10-17 00:00:00'
SET @To = '2007-10-17 23:59:59'

-- Intervals where from is included, but to is not
SELECT 'A' Union_Case, Status, @From StartTime, EndTime FROM Table_Status 
    WHERE StartTime < @From AND @From <= EndTime AND EndTime < @To
  UNION 
-- Intervals where @to and @from are incased in the interval
SELECT 'B', Status, @From, @To FROM Table_Status WHERE StartTime < @From AND EndTime >= @To
  UNION  
-- Intervals where @from is before start and @to is after end
SELECT 'C', Status, StartTime, EndTime FROM Table_Status WHERE StartTime > @From AND StartTime <= @To AND EndTime < @To 
  UNION 
-- intervals where @from is before start but @to ends within
SELECT 'D',  Status, StartTime, @To FROM Table_Status WHERE @From <= StartTime AND EndTime > @To
  ORDER BY 3, 4

Answer 3

I faced a similar situation, and the way I was able to fix this was as follows:

DECLARE @TimeStamp as DateTimeType
SET @TimeStamp = cast('2016-10-13 00:00:00.000' as datetime)
DECLARE @DayNum as ApsDayOrdinalType = DATEPART(dw,@TimeStamp)
DECLARE @Time as Time = cast(@TimeStamp as time)
DECLARE @DefaultTime as Time = CAST('00:00' as time)

SELECT @DayNum,@Time,@DefaultTime
-- CTEs don't work pretty well with inline data transformation :'(
DECLARE @WorkCenterShifts as table(
  WorkCenter varchar(15),
  ShiftId varchar(15),
  StartDateTime DateTime,
  EndDateTime DateTime
)

INSERT INTO @WorkCenterShifts
SELECT 
  wc
  ,shiftid
  ,CAST(CAST(DateAdd(dd,-(@DayNum - sday),@TimeStamp) as date) as varchar) +' '+ cast(stime as varchar)
  ,CAST(CAST(DateAdd(dd,(@DayNum - eday),@TimeStamp) as date) as varchar) +' '+ cast(etime as varchar)
FROM workcentershiftview 
WHERE wc = @wc
  and @DayNum >= IsNull(sday,1) 
  and @DayNum <= IsNull(eday,7) 

Where "workcentershiftview" returns data as follows:

WC      ShiftId sDay    eDay    sTime   eTime
MXMCE2  2nd9    6       2       16:00   06:30
MXMCE2  1st9.5  6       6       06:30   16:00
MXMCE2  2nd9    5       6       16:00   06:30
MXMCE2  1st9.5  5       5       06:30   16:00
MXMCE2  2nd9    4       5       16:00   06:30

From there, I could finally create a simple query, for example:

SELECT * FROM @WorkCenterShifts
WHERE @TimeStamp between StartDateTime and EndDateTime

And get this result:

WorkCencer    ShiftId     StartDateTime               EndDateTime
MXMCE2        2nd9        2016-10-12 16:00:00.000     2016-10-13 06:30:00.000

Hope this is helpful to others :)