SQL Server, вычисляя общее количество часов в день с несколькими перерывами

Я создаю запрос, чтобы получить общее количество часов, прошедших в день кем-то, но может быть несколько разрывов в раз в день.

Вот запрос, который у меня есть на данный момент.

SELECT CHINA_VISION_DorEvents.DorCtrls_Ref, CHINA_VISION_PubCards.CardCode, CHINA_VISION_DorEvents.EventTM FROM CHINA_VISION_PubCards INNER JOIN CHINA_VISION_DorEvents ON CHINA_VISION_PubCards.CardCode = CHINA_VISION_DorEvents.CardCode WHERE (CHINA_VISION_PubCards.CardCode = '000006f1') AND CHINA_VISION_DorEvents.DorCtrls_Ref = '16' ORDER BY CONVERT(Date,CHINA_VISION_DorEvents.EventTM) DESC 

Этот запрос в настоящее время не пытается выработать прошедшее время, но вот результаты этого, чтобы вы могли видеть, как выглядят данные.

 Ref CardCode EventTM --------------------------------------- 16 000006f1 2015-01-27 07:32:35.000 16 000006f1 2015-01-26 07:38:02.000 16 000006f1 2015-01-26 12:30:54.000 16 000006f1 2015-01-26 13:03:28.000 16 000006f1 2015-01-26 17:28:47.000 16 000006f1 2015-01-23 07:31:10.000 16 000006f1 2015-01-23 12:22:50.000 16 000006f1 2015-01-23 12:47:51.000 16 000006f1 2015-01-23 17:00:20.000 16 000006f1 2015-01-22 07:35:03.000 16 000006f1 2015-01-22 12:28:13.000 16 000006f1 2015-01-22 13:03:12.000 16 000006f1 2015-01-22 16:55:56.000 

Как вы можете видеть большинство дней, есть 4 записи, и мне нужно разработать прошедшее время для них, например, для 26

 07:38:02 12:30:54 elapsed time = 4 hours, 52 minutes and 52 seconds 13:03:28 17:28:47 Elapsed time = 4 hours, 25 minutes and 19 seconds 

Таким образом, общая сумма, прошедшая за 26-е, составит 9 часов 17 минут 71

Таким образом, в результате это будет выглядеть

  Date Elapsed 2015-01-26 9:17:71 

и так далее

Нам не нужно рассчитывать между 2-3, так как пользователь не входит в систему здесь.

  1 2 3 4 think of it like this ON - OFF BACK ON - OFF 

структура таблицы

  Name type allow null Reference int Unchecked DorCtrls_Ref int Checked EventsID tinyint Checked EventTM datetime Checked CardCode varchar(50) Checked JustificationCode tinyint Checked RecordIndex bigint Checked Memo varchar(50) Checked TempltCard varchar(1024)Checked Templtlength varchar(32)Checked TempltDir varchar(50) Checked 

Если вы не используете очень старую версию SQL Server, это будет работать на вас:

Данные испытаний:

 CREATE TABLE Test(Ref int, CardCode varchar(20), EventTM datetime) insert into Test select 16,'000006f1','2015-01-27T07:32:35.000' union all select 16,'000006f1','2015-01-26T07:38:02.000' union all select 16,'000006f1','2015-01-26T12:30:54.000' union all select 16,'000006f1','2015-01-26T13:03:28.000' union all select 16,'000006f1','2015-01-26T17:28:47.000' union all select 16,'000006f1','2015-01-23T07:31:10.000' union all select 16,'000006f1','2015-01-23T12:22:50.000' union all select 16,'000006f1','2015-01-23T12:47:51.000' union all select 16,'000006f1','2015-01-23T17:00:20.000' union all select 16,'000006f1','2015-01-22T07:35:03.000' union all select 16,'000006f1','2015-01-22T12:28:13.000' union all select 16,'000006f1','2015-01-22T13:03:12.000' union all select 16,'000006f1','2015-01-22T16:55:56.000'; 

Запрос:

 WITH ByDays AS ( -- Number the entry register in each day SELECT EventTm AS T, CONVERT(VARCHAR(10),EventTm,102) AS Day, FLOOR(CONVERT(FLOAT,EventTm)) DayNumber, ROW_NUMBER() OVER(PARTITION BY FLOOR(CONVERT(FLOAT,EventTm)) ORDER BY EventTm) InDay FROM Test ) --SELECT * FROM ByDays ORDER BY T ,Diffs AS ( SELECT E.Day, ET ET, OT OT, OT-ET Diff, DATEDIFF(S,ET,OT) DiffSeconds -- difference in seconds FROM (SELECT BE.T, BE.Day, BE.InDay FROM ByDays BE WHERE BE.InDay % 2 = 1) E -- Even rows INNER JOIN (SELECT BO.T, BO.Day, BO.InDay FROM ByDays BO WHERE BO.InDay % 2 = 0) O -- Odd rows ON E.InDay + 1 = O.InDay -- Join rows (1,2), (3,4) and so on AND E.Day = O.Day -- in the same day ) --SELECT * FROM Diffs SELECT Day, SUM(DiffSeconds) Seconds, CONVERT(VARCHAR(8), (DATEADD(S, SUM(DiffSeconds), '1900-01-01T00:00:00')), 108) TotalHHMMSS -- The same, formatted as HH:MM:SS FROM Diffs GROUP BY Day 

Результат выглядит следующим образом.

 Day Seconds TotalHHMMSS 2015.01.22 31554 08:45:54 2015.01.23 32649 09:04:09 2015.01.26 33491 09:18:11 

См. Соответствующий скрипт sql: http://sqlfiddle.com/#!3/e1d31/1

Из вашего результата, который вы опубликовали в своем вопросе, вы можете попробовать приведенный ниже код

 CREATE TABLE #TEMP(Ref INT,CardCode VARCHAR(40),EventTM DATETIME) INSERT INTO #TEMP SELECT 16, '000006f1', '2015-01-27 07:32:35.000' UNION ALL SELECT 16, '000006f1', '2015-01-26 07:38:02.000' UNION ALL SELECT 16, '000006f1', '2015-01-26 12:30:54.000' UNION ALL SELECT 16, '000006f1', '2015-01-26 13:03:28.000' UNION ALL SELECT 16, '000006f1', '2015-01-26 17:28:47.000' UNION ALL SELECT 16, '000006f1', '2015-01-23 07:31:10.000' UNION ALL SELECT 16, '000006f1', '2015-01-23 12:22:50.000' UNION ALL SELECT 16, '000006f1', '2015-01-23 12:47:51.000' UNION ALL SELECT 16, '000006f1', '2015-01-23 17:00:20.000' UNION ALL SELECT 16, '000006f1', '2015-01-22 07:35:03.000' UNION ALL SELECT 16, '000006f1', '2015-01-22 12:28:13.000' UNION ALL SELECT 16, '000006f1', '2015-01-22 13:03:12.000' UNION ALL SELECT 16, '000006f1', '2015-01-22 16:55:56.000' 

QUERY

 ;WITH CTE AS ( -- Gets row number Order the date SELECT ROW_NUMBER() OVER( ORDER BY EventTM)RNO, * FROM #TEMP ) ,CTE2 AS ( -- Split to hours,minutes and seconds SELECT C1.*,C2.EventTM EM,DATEDIFF(S,C1.EventTM,C2.EventTM)DD, cast( (cast(cast(C2.EventTM as float) - cast(C1.EventTM as float) as int) * 24) + datepart(hh, C2.EventTM - C1.EventTM) as INT)HH ,CAST(right('0' + cast(datepart(mi, C2.EventTM - C1.EventTM) as varchar(2)), 2)AS INT)MM ,CAST(right('0' + cast(datepart(ss, C2.EventTM - C1.EventTM) as varchar(2)), 2)AS INT)SS FROM CTE C1 LEFT JOIN CTE C2 ON C1.RNO=C2.RNO-1 WHERE C1.RNO % 2 <> 0 ), CTE3 AS ( -- Sum the hours, minute and seconds SELECT CAST(EventTM AS DATE)EventTM, SUM(HH) HH,SUM(MM) MM,SUM(SS) SS FROM CTE2 GROUP BY CAST(EventTM AS DATE) ) -- Format the elapsed time SELECT EventTM, CASE WHEN MM >=60 THEN CAST(HH+1 AS VARCHAR(10)) END + ':' + CASE WHEN MM >=60 THEN right('0' + CAST(MM-60 AS VARCHAR(10)),2) END + ':' + CAST(SS AS VARCHAR(10))Elapsed FROM CTE3 
  • Нажмите здесь, чтобы посмотреть результат

РЕДАКТИРОВАТЬ :

По вашему запросу вы можете использовать приведенный ниже код

 ;WITH CTE AS ( -- Gets row number Order the date SELECT ROW_NUMBER() OVER( ORDER BY CONVERT(DateTime,CHINA_VISION_DorEvents.EventTM))RNO, CHINA_VISION_DorEvents.DorCtrls_Ref Ref, CHINA_VISION_PubCards.CardCode, CONVERT(DateTime,CHINA_VISION_DorEvents.EventTM) EventTM FROM CHINA_VISION_PubCards INNER JOIN CHINA_VISION_DorEvents ON CHINA_VISION_PubCards.CardCode = CHINA_VISION_DorEvents.CardCode WHERE (CHINA_VISION_PubCards.CardCode = '000006f1') and CHINA_VISION_DorEvents.DorCtrls_Ref= '16' ) ,CTE2 AS ( -- Split to hours,minutes and seconds SELECT C1.*,C2.EventTM EM,DATEDIFF(S,C1.EventTM,C2.EventTM)DD, cast( (cast(cast(C2.EventTM as float) - cast(C1.EventTM as float) as int) * 24) + datepart(hh, C2.EventTM - C1.EventTM) as INT)HH ,CAST(right('0' + cast(datepart(mi, C2.EventTM - C1.EventTM) as varchar(2)), 2)AS INT)MM ,CAST(right('0' + cast(datepart(ss, C2.EventTM - C1.EventTM) as varchar(2)), 2)AS INT)SS FROM CTE C1 LEFT JOIN CTE C2 ON C1.RNO=C2.RNO-1 WHERE C1.RNO % 2 <> 0 ), CTE3 AS ( -- Sum the hours, minute and seconds SELECT CAST(EventTM AS DATE)EventTM, SUM(HH) HH,SUM(MM) MM,SUM(SS) SS FROM CTE2 GROUP BY CAST(EventTM AS DATE) ) -- Format the elapsed time SELECT EventTM, CASE WHEN MM >=60 THEN CAST(HH+1 AS VARCHAR(10)) END + ':' + CASE WHEN MM >=60 THEN right('0' + CAST(MM-60 AS VARCHAR(10)),2) END + ':' + CAST(SS AS VARCHAR(10))Elapsed FROM CTE3 

Попробуйте это, The out put правильно.

 your output is wrong.9:17:71 is wrong.it should be 9:18:11. Declare @t table(Ref int, CardCode varchar(20), EventTM datetime) insert into @t select 16,'000006f1','2015-01-27 07:32:35.000' union all select 16,' 000006f1','2015-01-26 07:38:02.000' union all select 16,' 000006f1','2015-01-26 12:30:54.000' union all select 16,' 000006f1','2015-01-26 13:03:28.000' union all select 16,' 000006f1','2015-01-26 17:28:47.000' union all select 16,' 000006f1','2015-01-23 07:31:10.000' union all select 16,' 000006f1','2015-01-23 12:22:50.000' union all select 16,' 000006f1','2015-01-23 12:47:51.000' union all select 16,' 000006f1','2015-01-23 17:00:20.000' union all select 16,' 000006f1','2015-01-22 07:35:03.000' union all select 16,' 000006f1','2015-01-22 12:28:13.000' union all select 16,' 000006f1','2015-01-22 13:03:12.000' union all select 16,' 000006f1','2015-01-22 16:55:56.000' ;with CTE as ( select *,row_number()over(partition by dateadd(d,0,datediff(d,0,EventTM)) order by EventTM)rn from @t ) ,CTE1 as ( select Ref,CardCode,EventTM, rn oddrn,0 TimeElapse from CTE where rn%2<>0 union all select a.Ref,a.CardCode,a.EventTM, rn ,datediff(s,b.EventTM,a.EventTM) from CTE a inner join CTE1 b on dateadd(d,0,datediff(d,0,a.EventTM))= dateadd(d,0,datediff(d,0,b.EventTM)) and a.ref=b.ref and a.rn-b.oddrn=1 and a.rn%2=0 ) select EventTM,cast((convert(varchar(5),TimeElapse/3600) +':'+ convert(varchar(5),TimeElapse%3600/60) +':'+ convert(varchar(5),TimeElapse%60)) as datetime) from (select dateadd(d,0,datediff(d,0,EventTM)) EventTM,sum(TimeElapse) TimeElapse from cte1 where TimeElapse>0 group by dateadd(d,0,datediff(d,0,EventTM)))tbl 

Я не смог проверить это сам, но это может дать вам хорошее начало. Я бы использовал курсор, если вы хотите сохранить его в SQL, хотя я бы предпочел сделать это в CLR. У других может быть лучший метод, но вы можете попробовать следующее:

 Declare @olddate datetime, @date datetime DECLARE @Table table (Ref int, CardCode varchar(20), EventTM datetime, ElapsedTime varchar(30)) Declare cur_mycursor Cursor fast_forward for SELECT CHINA_VISION_DorEvents.EventTM FROM CHINA_VISION_PubCards INNER JOIN CHINA_VISION_DorEvents ON CHINA_VISION_PubCards.CardCode = CHINA_VISION_DorEvents.CardCode WHERE (CHINA_VISION_PubCards.CardCode = '000006f1') and CHINA_VISION_DorEvents.DorCtrls_Ref= '16' Order by CHINA_VISION_DorEvents.EventTM desc Open cur_mycursor Fetch next from cur_mycursor into @olddate While @@FETCH_STATUS = 0 Begin Fetch next from cur_mycursor into @date INSERT INTO @Table (Ref, CardCode, EventTM, ElapsedTime) SELECT CHINA_VISION_DorEvents.DorCtrls_Ref, CHINA_VISION_PubCards.CardCode, CHINA_VISION_DorEvents.EventTM, case when Convert(varchar, @date, 112) = Convert(varchar, CHINA_VISION_DorEvents.EventTM, 112) then Cast(datediff(mi, @date, CHINA_VISION_DorEvents.EventTM) / 1440 as varchar) + ' days ' + Cast(datediff(mi, @date, CHINA_VISION_DorEvents.EventTM) % 1440 / 60 as varchar) + ' hours ' + Cast(datediff(mi, @date, CHINA_VISION_DorEvents.EventTM) % 1440 %60 as varchar) + ' minutes' else '0' end as elapsedtime FROM CHINA_VISION_PubCards INNER JOIN CHINA_VISION_DorEvents ON CHINA_VISION_PubCards.CardCode = CHINA_VISION_DorEvents.CardCode WHERE (CHINA_VISION_PubCards.CardCode = '000006f1') and CHINA_VISION_DorEvents.DorCtrls_Ref= '16' and CHINA_VISION_DorEvents.EventTM = @olddate Order by CHINA_VISION_DorEvents.EventTM desc Set @olddate = @date end close cur_mycursor Select * from @Table order by EventTM asc deallocate cur_mycursor 
Interesting Posts

Объединение нескольких строк с дублирующимся идентификатором

сопоставление групп строк в двух базах данных

получить количество между диапазоном

Установите нулевое значение по умолчанию для нулевых строк в SQL Server

SQL: как выбрать количество нескольких записей из одной таблицы на основе записей в другой таблице?

SQL Server для выбора всех записей, кроме записей, повторяющихся в определенный интервал времени

Соединение плохо разработанных таблиц SQL?

Не удается найти асимметричный ключ – поскольку он не существует или у вас нет разрешения

Куча SQL Server против кластерного индекса

Запрос SQL Server с производной таблицей вызывает ошибку синтаксиса

Расширенная группировка – вытягивание последней транзакции, но сохранение группировки

Получите Top 5 каждого отдельного значения столбца

Как sql выбрать продукт с двумя статусами?

Обновление строк par lot SQL Server

Библиотека синхронизации файлов?

Давайте будем гением компьютера.