Hello, help me figure it out ... There is a table

Начало смены(SHIFT_DATE) №оборудования(EQUIP_IDENT) Время начала статуса(START_TIMESTAMP) Статус(STATUS_CODE) Время окончания статуса(END_TIMESTAMP)

/ Status - for example, equipment costs, it’s all separate statuses moving /

The task is to break the time statuses by the hour. The problem is that the status can begin at one o'clock and end at another. How to make part of the status fall in one hour and the second part in the second?

I chose the watch

  DISTINCT DATEADD(hh,1,DATEADD(ss, -DATEPART(ss,est.START_TIMESTAMP), DATEADD(mi, -DATEPART(mi,est.START_TIMESTAMP),dbo.season_offset(est.START_TIMESTAMP,1)))) as 'DATE_TIME_HOURS'

I screwed one design, but it cuts off the hour on one side (if the status end hour> status start hour - then chop by the end of the starting hour), but then how to get the 2nd part from the start of the 2nd hour to the end status?

I understand that poking around in someone else's code with someone else's database ... but describing what is happening: 1. I select the clock at the start of the status. I select the clock at the end of the status. Equipment Status The status start time The end time

  1. Condition in external case - if status start hour = status end hour and status = worker then select status time if if hour = start hour and status end hour> status start hour then time (status start until status start hour (for example, 18) + 00 : 59: 59)

it turns out, for example, the status began at 18:30:23 and ended at 19:15:00, then it will be cut from 18:30:23 to 18:59:59

How to get part from 19:00:00 to 19:15:00 ??

  select DATE_TIME_HOURS, EQUIP_IDENT, START_TIMESTAMP, END_TIMESTAMP, CASE WHEN DATE_TIME_HOURS_START = DATE_TIME_HOURS_END AND tab.STATUS_CODE = 'KIO_WORK' THEN DATEDIFF(SS,START_TIMESTAMP,END_TIMESTAMP) WHEN DATE_TIME_HOURS_START = DATE_TIME_HOURS AND DATE_TIME_HOURS_END > DATE_TIME_HOURS_START AND tab.STATUS_CODE = 'KIO_WORK' THEN DATEDIFF(SS,START_TIMESTAMP, DATEADD(HH,-1,DATE_TIME_HOURS_START) + '00:59:59') ELSE 0 END AS WORK from( select DISTINCT DATEADD(hh,1,DATEADD(ss, -DATEPART(ss,est.START_TIMESTAMP), DATEADD(mi, -DATEPART(mi,est.START_TIMESTAMP),dbo.season_offset(est.START_TIMESTAMP,1)))) as 'DATE_TIME_HOURS', -- Это часы DATEADD(hh,1,DATEADD(ss, -DATEPART(ss,est.START_TIMESTAMP), DATEADD(mi, -DATEPART(mi,est.START_TIMESTAMP),dbo.season_offset(est.START_TIMESTAMP,1)))) as 'DATE_TIME_HOURS_START', --Это час старта статуса DATEADD(hh,1,DATEADD(ss, -DATEPART(ss,est.END_TIMESTAMP), DATEADD(mi, -DATEPART(mi,est.END_TIMESTAMP),dbo.season_offset(est.END_TIMESTAMP,1)))) as 'DATE_TIME_HOURS_END', -- Это час конца статуса est.EQUIP_IDENT, --DATEDIFF(SS,START_TIMESTAMP,END_TIMESTAMP)as duration, est.STATUS_CODE, START_TIMESTAMP, END_TIMESTAMP from EQUIPMENT_STATUS_TRANS est where SHIFT_DATE between @start_date and @end_date and SHIFT_IDENT = @shift_ident --AND DATEPART(HH,START_TIMESTAMP)=DATEPART(HH,END_TIMESTAMP) ) tab order by EQUIP_IDENT,DATE_TIME_HOURS,START_TIMESTAMP

An example of a query executed:

 DATE_TIME_HOURS EQUIP_IDENT START_TIMESTAMP END_TIMESTAMP DURATION 2016-03-23 21:00:00.000 11 2016-03-23 20:00:00.000 2016-03-23 20:05:17.000 0 2016-03-23 21:00:00.000 11 2016-03-23 20:05:17.000 2016-03-23 20:06:24.000 67 2016-03-23 21:00:00.000 11 2016-03-23 20:06:24.000 2016-03-23 20:09:45.000 0 2016-03-23 21:00:00.000 11 2016-03-23 20:09:45.000 2016-03-23 20:10:05.000 20 2016-03-23 21:00:00.000 11 2016-03-23 20:10:05.000 2016-03-23 20:11:16.000 71 2016-03-23 21:00:00.000 11 2016-03-23 20:11:16.000 2016-03-23 20:14:57.000 221 2016-03-23 21:00:00.000 11 2016-03-23 20:14:57.000 2016-03-23 20:16:18.000 0 2016-03-23 21:00:00.000 11 2016-03-23 20:16:18.000 2016-03-23 20:19:57.000 219 2016-03-23 21:00:00.000 11 2016-03-23 20:19:57.000 2016-03-23 20:21:16.000 79 2016-03-23 21:00:00.000 11 2016-03-23 20:21:16.000 2016-03-23 20:24:42.000 206 2016-03-23 21:00:00.000 11 2016-03-23 20:24:42.000 2016-03-23 20:26:25.000 0 2016-03-23 21:00:00.000 11 2016-03-23 20:26:25.000 2016-03-23 20:30:29.000 244
  • And if the status time lasts more than 2 hours - you still need the first hour and all the rest. Or is there already 3 or more parts? - Mike
  • If more then it is necessary to divide it into the number of hours how long it lasts for example from 15:00 to 17:30 means 15-16, 16-17, 17 - 17:30 - FastTI

1 answer 1

Create an additional table with integer numbers running from 0 to the maximum duration of the equipment in hours (preferably with a margin):

 create table seqnum( N int not null, CONSTRAINT PK_seqnum PRIMARY KEY CLUSTERED (N) ); insert into seqnum values(0),(1),(2),(3); insert into seqnum select N+4 from seqnum; insert into seqnum select N+8 from seqnum; insert into seqnum select N+16 from seqnum; -- Максимальное значение 31

The request for getting hourly intervals is as follows:

 select iif(SN=0,start_timestamp,dateadd(hour,SN,start_hour)) start_dt, iif(dateadd(hour,S.N+1,start_hour)>=end_timestamp,end_timestamp, dateadd(hour,SN,start_hour)+'00:59:59') end_dt from ( select dateadd(hour, datediff(hour, 0, start_timestamp), 0) start_hour, start_timestamp,end_timestamp, datediff(hour,start_timestamp,end_timestamp) period_length from EQUIPMENT_STATUS_TRANS ) E, seqnum S -- Перемножаем на порядковые номера where SN<=period_length -- И отсекаем все превышающие часы

Note that the time cut-off before the hour occurs like dateadd(hour, datediff(hour, 0, start_timestamp), 0)

  • did a little different but thanks for the advice! - FastTI