Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
D.Solonskiy
Member

Откуда:
Сообщений: 23
aleks2
Когда я вижу таких монстреков для простейшей задачи - рука сама тянется к пистолету...

;with
-- базовый запрос
base as(
SELECT T.IDE ,T.IDD ,T.ST ,T.DATE_ST, row_number() over(partition by T.IDD order by T.DATE_ST asc) as N
FROM
DevicesOnHistory AS T
WHERE 1=1
AND T.DATE_ST >= '2013-01-01'
AND T.DATE_ST <= '2013-01-22' 
)
,
-- удаляем избыточность, оставляем только точки переключения состояния устройства
switchs as(
select *
from base b1 where not exists(select * from base b2 where b2.IDD=b1.IDD and b2.N=b1.N-1 and b2.ST=b1.ST )
)
,
-- дальше соображаем, что це таке "количество рабочих дней для каждого устройства"?
-- K = summa( Твыкл(i) - Tвкл(i) ) по всем периодам i "включилось-выключилось"
-- K = summa( Твыкл(i) ) - summa( Tвкл(i) )
workdays as (
select IDD, sum((case ST when 'True' then -1 else 1 end) * datediff(day, 0, DATE_ST)) as Workdays
from switchs
group by IDD

select * from workdays;
-- некоторая засада в том, что начинаться должно с "включилось", а заканчиваться "выключилось", но это мелочи.
-- я уж не стал в них вдаваться...

вот результат (

4298 -41293
4314 -8
4327 -41281
4383 -4
4444 41281
4779 41288
4832 41283
4886 41282
4918 41275
6022 -4
6025 -41289
6029 -41289
6036 -41288
6049 -4
6051 -4
6052 -4
6066 -4
6075 -41286
6076 -4
6094 -41283
6121 1
6344 41288
6345 41293
6366 41289
6495 41282
6534 -15
6552 41282
25 янв 13, 15:02    [13828420]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
D.Solonskiy
Member

Откуда:
Сообщений: 23
aleks2,

вот собственно результат
4298 -41293
4314 -8
4327 -41281
4383 -4
4444 41281
4779 41288
4832 41283
4886 41282
4918 41275
6022 -4
6025 -41289
6029 -41289
6036 -41288
6049 -4
6051 -4
6052 -4
6066 -4
6075 -41286
6076 -4
6094 -41283
6121 1
6344 41288
6345 41293
6366 41289
6495 41282
6534 -15
6552 41282
25 янв 13, 15:03    [13828429]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
aleks2
Когда я вижу таких монстреков для простейшей задачи - рука сама тянется к пистолету...
Не работает, однако...
+
declare @from datetime='2013-01-01', @to datetime='2013-01-22'
;with
DevicesOnHistory as( select * from(values
   (4787, 4298, convert(bit,1), convert(datetime,'21.01.2013 13:46:00',104)), (4749, 4314, 0, convert(datetime,'09.01.2013 10:40:13',104)),
   (4785, 4314, 1, convert(datetime,'17.01.2013 16:11:34',104)), (4752, 4327, 1, convert(datetime,'09.01.2013 10:43:10',104)),
   (4755, 4383, 0, convert(datetime,'10.01.2013 15:41:47',104)), (4766, 4383, 1, convert(datetime,'14.01.2013 10:41:59',104)),
   (4750, 4444, 0, convert(datetime,'09.01.2013 10:41:02',104)), (4779, 4779, 0, convert(datetime,'16.01.2013 12:53:30',104)),
   (4765, 4832, 0, convert(datetime,'11.01.2013 15:57:47',104)), (4756, 4886, 0, convert(datetime,'10.01.2013 15:44:46',104)),
   (4748, 4918, 0, convert(datetime,'03.01.2013 11:52:49',104)), (4759, 6022, 0, convert(datetime,'11.01.2013 15:09:07',104)),
   (4775, 6022, 1, convert(datetime,'15.01.2013 14:29:29',104)), (4776, 6022, 1, convert(datetime,'15.01.2013 15:10:28',104)),
   (4783, 6025, 1, convert(datetime,'17.01.2013 11:40:37',104)), (4782, 6029, 1, convert(datetime,'17.01.2013 11:40:11',104)),
   (4780, 6036, 1, convert(datetime,'16.01.2013 12:57:22',104)), (4761, 6049, 0, convert(datetime,'11.01.2013 15:54:52',104)),
   (4771, 6049, 1, convert(datetime,'15.01.2013 13:34:13',104)), (4772, 6049, 1, convert(datetime,'15.01.2013 13:34:12',104)),
   (4760, 6051, 0, convert(datetime,'11.01.2013 15:54:15',104)), (4768, 6051, 1, convert(datetime,'15.01.2013 12:28:15',104)),
   (4773, 6051, 0, convert(datetime,'15.01.2013 13:34:36',104)), (4774, 6051, 1, convert(datetime,'15.01.2013 13:34:40',104)),
   (4762, 6052, 0, convert(datetime,'11.01.2013 15:55:34',104)), (4769, 6052, 1, convert(datetime,'15.01.2013 13:08:44',104)),
   (4770, 6052, 1, convert(datetime,'15.01.2013 13:33:41',104)), (4763, 6066, 0, convert(datetime,'11.01.2013 15:56:10',104)),
   (4777, 6066, 1, convert(datetime,'15.01.2013 16:34:24',104)), (4767, 6075, 1, convert(datetime,'14.01.2013 15:37:57',104)),
   (4764, 6076, 0, convert(datetime,'11.01.2013 15:57:04',104)), (4778, 6076, 1, convert(datetime,'15.01.2013 16:34:32',104)),
   (4758, 6094, 1, convert(datetime,'11.01.2013 12:35:10',104)), (4751, 6121, 1, convert(datetime,'09.01.2013 10:42:04',104)),
   (4753, 6121, 0, convert(datetime,'10.01.2013 15:40:22',104)), (4781, 6344, 0, convert(datetime,'16.01.2013 18:44:10',104)),
   (4788, 6345, 0, convert(datetime,'21.01.2013 13:46:36',104)), (4784, 6366, 0, convert(datetime,'17.01.2013 12:55:44',104)),
   (4754, 6495, 0, convert(datetime,'10.01.2013 15:41:06',104)), (4747, 6534, 0, convert(datetime,'03.01.2013 11:52:13',104)),
   (4786, 6534, 1, convert(datetime,'18.01.2013 12:56:19',104)), (4757, 6552, 0, convert(datetime,'10.01.2013 18:29:49',104))
   )tmp(IDE, IDD, ST, DATE_ST)), 
-- базовый запрос
base as(
SELECT T.IDE ,T.IDD ,T.ST ,T.DATE_ST, row_number() over(partition by T.IDD order by T.DATE_ST asc) as N
FROM
DevicesOnHistory AS T
WHERE 1=1
AND T.DATE_ST >= '2013-01-01'
AND T.DATE_ST <= '2013-01-22' 
)
,
-- удаляем избыточность, оставляем только точки переключения состояния устройства
switchs as(
select *
from base b1 where not exists(select * from base b2 where b2.IDD=b1.IDD and b2.N=b1.N-1 and b2.ST=b1.ST )
)
,
-- дальше соображаем, что це таке "количество рабочих дней для каждого устройства"?
-- K = summa( Твыкл(i) - Tвкл(i) ) по всем периодам i "включилось-выключилось"
-- K = summa( Твыкл(i) ) - summa( Tвкл(i) )
workdays as (
   --select IDD, sum((case ST when 'True' then -1 else 1 end) * datediff(day, 0, DATE_ST)) as Workdays
   select IDD, sum((case ST when 1 then -1 else 1 end) * datediff(day, 0, DATE_ST)) as Workdays
   from switchs
   group by IDD
   )
select * from workdays;
-- некоторая засада в том, что начинаться должно с "включилось", а заканчиваться "выключилось", но это мелочи.
-- я уж не стал в них вдаваться...
IDD         Workdays
----------- -----------
4298 -41293
4314 -8
4327 -41281
4383 -4
4444 41281
4779 41288
...
25 янв 13, 15:17    [13828573]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
D.Solonskiy
Member

Откуда:
Сообщений: 23
Cygapb-007,
не понимаю вроде алгоритм простой взять все устройства из одной таблички и найти все диапазоны когда устройство не работает сложить и все ну и еще определить статус в котором находится устройство на данный момент и тогда в зависимости от статуса все дни в диапазоне будут рабочими или нет (((((
25 янв 13, 15:57    [13828983]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
D.Solonskiy
Cygapb-007,
не понимаю вроде алгоритм простой взять все устройства из одной таблички и найти все диапазоны когда устройство не работает сложить и все ну и еще определить статус в котором находится устройство на данный момент и тогда в зависимости от статуса все дни в диапазоне будут рабочими или нет (((((
Дальше копать?
+
declare @from datetime='2013-01-01', @to datetime='2013-01-22'
;with
DevicesOnHistory as( select * from(values
   (4787, 4298, convert(bit,1), convert(datetime,'21.01.2013 13:46:00',104)), (4749, 4314, 0, convert(datetime,'09.01.2013 10:40:13',104)),
   (4785, 4314, 1, convert(datetime,'17.01.2013 16:11:34',104)), (4752, 4327, 1, convert(datetime,'09.01.2013 10:43:10',104)),
   (4755, 4383, 0, convert(datetime,'10.01.2013 15:41:47',104)), (4766, 4383, 1, convert(datetime,'14.01.2013 10:41:59',104)),
   (4750, 4444, 0, convert(datetime,'09.01.2013 10:41:02',104)), (4779, 4779, 0, convert(datetime,'16.01.2013 12:53:30',104)),
   (4765, 4832, 0, convert(datetime,'11.01.2013 15:57:47',104)), (4756, 4886, 0, convert(datetime,'10.01.2013 15:44:46',104)),
   (4748, 4918, 0, convert(datetime,'03.01.2013 11:52:49',104)), (4759, 6022, 0, convert(datetime,'11.01.2013 15:09:07',104)),
   (4775, 6022, 1, convert(datetime,'15.01.2013 14:29:29',104)), (4776, 6022, 1, convert(datetime,'15.01.2013 15:10:28',104)),
   (4783, 6025, 1, convert(datetime,'17.01.2013 11:40:37',104)), (4782, 6029, 1, convert(datetime,'17.01.2013 11:40:11',104)),
   (4780, 6036, 1, convert(datetime,'16.01.2013 12:57:22',104)), (4761, 6049, 0, convert(datetime,'11.01.2013 15:54:52',104)),
   (4771, 6049, 1, convert(datetime,'15.01.2013 13:34:13',104)), (4772, 6049, 1, convert(datetime,'15.01.2013 13:34:12',104)),
   (4760, 6051, 0, convert(datetime,'11.01.2013 15:54:15',104)), (4768, 6051, 1, convert(datetime,'15.01.2013 12:28:15',104)),
   (4773, 6051, 0, convert(datetime,'15.01.2013 13:34:36',104)), (4774, 6051, 1, convert(datetime,'15.01.2013 13:34:40',104)),
   (4762, 6052, 0, convert(datetime,'11.01.2013 15:55:34',104)), (4769, 6052, 1, convert(datetime,'15.01.2013 13:08:44',104)),
   (4770, 6052, 1, convert(datetime,'15.01.2013 13:33:41',104)), (4763, 6066, 0, convert(datetime,'11.01.2013 15:56:10',104)),
   (4777, 6066, 1, convert(datetime,'15.01.2013 16:34:24',104)), (4767, 6075, 1, convert(datetime,'14.01.2013 15:37:57',104)),
   (4764, 6076, 0, convert(datetime,'11.01.2013 15:57:04',104)), (4778, 6076, 1, convert(datetime,'15.01.2013 16:34:32',104)),
   (4758, 6094, 1, convert(datetime,'11.01.2013 12:35:10',104)), (4751, 6121, 1, convert(datetime,'09.01.2013 10:42:04',104)),
   (4753, 6121, 0, convert(datetime,'10.01.2013 15:40:22',104)), (4781, 6344, 0, convert(datetime,'16.01.2013 18:44:10',104)),
   (4788, 6345, 0, convert(datetime,'21.01.2013 13:46:36',104)), (4784, 6366, 0, convert(datetime,'17.01.2013 12:55:44',104)),
   (4754, 6495, 0, convert(datetime,'10.01.2013 15:41:06',104)), (4747, 6534, 0, convert(datetime,'03.01.2013 11:52:13',104)),
   (4786, 6534, 1, convert(datetime,'18.01.2013 12:56:19',104)), (4757, 6552, 0, convert(datetime,'10.01.2013 18:29:49',104))
   )tmp(IDE, IDD, ST, EventDATE)), 
device as (
   select distinct h.IDD
	   from DevicesOnHistory h
   ),
start as (
   select h.IDD, h.EventDATE, h.ST, h.IDE, ROW_NUMBER() OVER (PARTITION BY h.IDD ORDER BY EventDATE) rn
	   from DevicesOnHistory h
	   where h.EventDATE between @from and @to
	),
startwork as ( -- определить начало работы устройства в заданном периоде
   select 
      s.IDD, 
      case when s.ST=1 then s.EventDATE else @from end EventDATE, 
      case when s.ST=1 then s.IDE else 0 end IDE,
      1 ST
   from start s
   where s.rn=1
   ),
swap as (
   select s.*, ISNULL(h.EventDATE,@to) EventDATE_next, h.IDE IDE_next, ISNULL(h.ST,s.ST) ST_next, 1 ord
	   from startwork s
      outer apply (
         select top(1) *
         from DevicesOnHistory h
         where h.IDD=s.IDD and h.IDE>s.IDE and h.ST!=s.ST
         order by h.EventDATE
      ) h
   union all
   select w.IDD, w.EventDATE_next, w.IDE_next, w.ST_next, ISNULL(h.EventDATE,@to), h.IDD, ISNULL(h.ST,w.ST), ord+1
	   from swap w
	   outer apply (
	      select *
	      from start h 
	      where h.IDD=w.IDD and h.IDE=w.IDE_next and h.ST!=w.ST
	   ) h
	   where w.IDE_next is not null
   )
select * 
	from swap w order by w.IDD, w.ord
IDD         EventDATE               IDE         ST          EventDATE_next          IDE_next    ST_next     ord
----------- ----------------------- ----------- ----------- ----------------------- ----------- ----------- -----------
4298 2013-01-21 13:46:00.000 4787 1 2013-01-22 00:00:00.000 NULL 1 1
4314 2013-01-01 00:00:00.000 0 1 2013-01-09 10:40:13.000 4749 0 1
4314 2013-01-09 10:40:13.000 4749 0 2013-01-09 10:40:13.000 4314 0 2
4314 2013-01-09 10:40:13.000 4314 0 2013-01-22 00:00:00.000 NULL 0 3
4327 2013-01-09 10:43:10.000 4752 1 2013-01-22 00:00:00.000 NULL 1 1
4383 2013-01-01 00:00:00.000 0 1 2013-01-10 15:41:47.000 4755 0 1
4383 2013-01-10 15:41:47.000 4755 0 2013-01-10 15:41:47.000 4383 0 2
25 янв 13, 16:07    [13829076]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
не, наврал...
сейчас исправлю..
25 янв 13, 16:13    [13829143]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
там, кстати, в таблице В могут быть накладки, из-за которых девайс считается дважды включенным без выключения (или наоброт)?
и если может - игнорировать повторные "переключения" ?
25 янв 13, 16:17    [13829175]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
D.Solonskiy
Member

Откуда:
Сообщений: 23
Cygapb-007,

извиняюсь за долгий ответ( да там могут быть ложные срабатывания
например из таблички примера
4760 6051 False 11.01.2013 15:54:15
4768 6051 True 15.01.2013 12:28:15
4773 6051 False 15.01.2013 13:34:36
4774 6051 True 15.01.2013 13:34:40
25 янв 13, 16:31    [13829277]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
отсчитывать по первому или по последнему ?
25 янв 13, 16:40    [13829345]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
D.Solonskiy
Member

Откуда:
Сообщений: 23
Cygapb-007,
по последнему
25 янв 13, 16:50    [13829420]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Ну вот что пока получается...
+ запрос
declare @from datetime='2013-01-01', @to datetime='2013-01-22'
;with
DevicesOnHistory as( select * from(values
   (4787, 4298, convert(bit,1), convert(datetime,'21.01.2013 13:46:00',104)), (4749, 4314, 0, convert(datetime,'09.01.2013 10:40:13',104)),
   (4785, 4314, 1, convert(datetime,'17.01.2013 16:11:34',104)), (4752, 4327, 1, convert(datetime,'09.01.2013 10:43:10',104)),
   (4755, 4383, 0, convert(datetime,'10.01.2013 15:41:47',104)), (4766, 4383, 1, convert(datetime,'14.01.2013 10:41:59',104)),
   (4750, 4444, 0, convert(datetime,'09.01.2013 10:41:02',104)), (4779, 4779, 0, convert(datetime,'16.01.2013 12:53:30',104)),
   (4765, 4832, 0, convert(datetime,'11.01.2013 15:57:47',104)), (4756, 4886, 0, convert(datetime,'10.01.2013 15:44:46',104)),
   (4748, 4918, 0, convert(datetime,'03.01.2013 11:52:49',104)), (4759, 6022, 0, convert(datetime,'11.01.2013 15:09:07',104)),
   (4775, 6022, 1, convert(datetime,'15.01.2013 14:29:29',104)), (4776, 6022, 1, convert(datetime,'15.01.2013 15:10:28',104)),
   (4783, 6025, 1, convert(datetime,'17.01.2013 11:40:37',104)), (4782, 6029, 1, convert(datetime,'17.01.2013 11:40:11',104)),
   (4780, 6036, 1, convert(datetime,'16.01.2013 12:57:22',104)), (4761, 6049, 0, convert(datetime,'11.01.2013 15:54:52',104)),
   (4771, 6049, 1, convert(datetime,'15.01.2013 13:34:13',104)), (4772, 6049, 1, convert(datetime,'15.01.2013 13:34:12',104)),
   (4760, 6051, 0, convert(datetime,'11.01.2013 15:54:15',104)), (4768, 6051, 1, convert(datetime,'15.01.2013 12:28:15',104)),
   (4773, 6051, 0, convert(datetime,'15.01.2013 13:34:36',104)), (4774, 6051, 1, convert(datetime,'15.01.2013 13:34:40',104)),
   (4762, 6052, 0, convert(datetime,'11.01.2013 15:55:34',104)), (4769, 6052, 1, convert(datetime,'15.01.2013 13:08:44',104)),
   (4770, 6052, 1, convert(datetime,'15.01.2013 13:33:41',104)), (4763, 6066, 0, convert(datetime,'11.01.2013 15:56:10',104)),
   (4777, 6066, 1, convert(datetime,'15.01.2013 16:34:24',104)), (4767, 6075, 1, convert(datetime,'14.01.2013 15:37:57',104)),
   (4764, 6076, 0, convert(datetime,'11.01.2013 15:57:04',104)), (4778, 6076, 1, convert(datetime,'15.01.2013 16:34:32',104)),
   (4758, 6094, 1, convert(datetime,'11.01.2013 12:35:10',104)), (4751, 6121, 1, convert(datetime,'09.01.2013 10:42:04',104)),
   (4753, 6121, 0, convert(datetime,'10.01.2013 15:40:22',104)), (4781, 6344, 0, convert(datetime,'16.01.2013 18:44:10',104)),
   (4788, 6345, 0, convert(datetime,'21.01.2013 13:46:36',104)), (4784, 6366, 0, convert(datetime,'17.01.2013 12:55:44',104)),
   (4754, 6495, 0, convert(datetime,'10.01.2013 15:41:06',104)), (4747, 6534, 0, convert(datetime,'03.01.2013 11:52:13',104)),
   (4786, 6534, 1, convert(datetime,'18.01.2013 12:56:19',104)), (4757, 6552, 0, convert(datetime,'10.01.2013 18:29:49',104)),
   (8759, 6022, 0, convert(datetime,'11.01.2013 15:29:07',104)), (9759, 6022, 0, convert(datetime,'11.01.2013 15:39:07',104))
   )tmp(IDE, IDD, ST, EventDATE)), 
--device as (
--   select distinct h.IDD
--	   from DevicesOnHistory h
--   ),
swap as (
   select 
      h.IDD, h.IDE, h.EventDATE, h.ST, 
      ROW_NUMBER() OVER (PARTITION BY h.IDD ORDER BY h.EventDATE) rn,
      COUNT(*) OVER (PARTITION BY h.IDD) cnt
   from DevicesOnHistory h
   where h.EventDATE between @from and @to
	),
all_swap as (
   select w.IDD, w.IDE, w.EventDATE, w.ST, w.rn, w.cnt
	   from swap w
	union all -- добавить начало периода
	select w.IDD, 0, @from, 1-w.ST,0, w.cnt
	   from swap w
	   where w.rn=1 and w.EventDATE>@from
	union all -- добавить конец периода
	select w.IDD, w.IDE+1, @to, 1-w.ST, w.rn+1, w.cnt
	   from swap w
      where w.cnt=w.rn and w.EventDATE<@to
   ),
ranges as (
   select 
      w.IDD, w.EventDATE, w.ST, 
      DATEDIFF(MINUTE, w.EventDATE, n.EventDATE) minutes, 
      n.ST ST_next, n.EventDATE EventDATE_next, 
      w.IDE, n.IDE IDE_next,
      w.rn+1 rn
   from all_swap w
   join all_swap n on n.IDD=w.IDD and n.rn=w.rn+1
   ),
press as ( -- сжатие диапазонов с ложными срабатываниями
   select * 
	from ranges r
	--where r.ST=r.ST_next
   )
select *
   -- from ranges r order by r.IDD, r.rn
   from press r
   where IDD in (6022)
   order by r.IDD, r.rn
IDD         EventDATE               ST          minutes     ST_next     EventDATE_next          IDE         IDE_next    rn
----------- ----------------------- ----------- ----------- ----------- ----------------------- ----------- ----------- --------------------
6022 2013-01-01 00:00:00.000 1 15309 0 2013-01-11 15:09:07.000 0 4759 1
6022 2013-01-11 15:09:07.000 0 20 0 2013-01-11 15:29:07.000 4759 8759 2
6022 2013-01-11 15:29:07.000 0 10 0 2013-01-11 15:39:07.000 8759 9759 3
6022 2013-01-11 15:39:07.000 0 5690 1 2013-01-15 14:29:29.000 9759 4775 4
6022 2013-01-15 14:29:29.000 1 41 1 2013-01-15 15:10:28.000 4775 4776 5
6022 2013-01-15 15:10:28.000 1 9170 0 2013-01-22 00:00:00.000 4776 4777 6
25 янв 13, 17:16    [13829545]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
D.Solonskiy
Member

Откуда:
Сообщений: 23
Cygapb-007,

Сейчас попробую)
25 янв 13, 17:18    [13829557]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
aleks2
Guest
Cygapb-007
aleks2
Когда я вижу таких монстреков для простейшей задачи - рука сама тянется к пистолету...
Не работает, однако...+[spoiler ][src]

Шоб утверждать: "ниработает" - нада более веские аргументы, чем кривой запрос.
25 янв 13, 17:33    [13829669]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
aleks2
Cygapb-007
пропущено...
Не работает, однако...+[spoiler ][src]

Шоб утверждать: "ниработает" - нада более веские аргументы, чем кривой запрос.
хмм... а свой же код уже не узнаете?
в вашем запросе ничего не менялось, только были вставлены в начало тестовые данные.

или это высказывание в качестве самокритики?
25 янв 13, 17:38    [13829709]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
D.Solonskiy
Member

Откуда:
Сообщений: 23
Cygapb-007,
Ура!!!
вообщем почти заработало только вот есть момент это скорее исключение может я чего не понимаю запрос был с первого по 25
дней получается 25 а по результату 24 я так понимаю если складывать и рабочие и не рабочие должно получится 25


6051 01.01.2013 0:00:00 1 10 0 11.01.2013 15:54:15 0 4760 1
6051 11.01.2013 15:54:15 0 4 1 15.01.2013 12:28:15 4760 4768 2
6051 15.01.2013 12:28:15 1 0 0 15.01.2013 13:34:36 4768 4773 3
6051 15.01.2013 13:34:36 0 0 1 15.01.2013 13:34:40 4773 4774 4
6051 15.01.2013 13:34:40 1 10 0 25.01.2013 0:00:00 4774 4775 5
25 янв 13, 17:46    [13829773]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
aleks2
Guest
Cygapb-007
aleks2
пропущено...

Шоб утверждать: "ниработает" - нада более веские аргументы, чем кривой запрос.
хмм... а свой же код уже не узнаете?
в вашем запросе ничего не менялось, только были вставлены в начало тестовые данные.

или это высказывание в качестве самокритики?


Испоганить можно любую идею. Особенно, если понимание отсутствует.

1. Научись приуготовлять данные.
set dateformat ymd
declare @from datetime='2013-01-01', @to datetime='2013-01-22'
set dateformat dmy
;with
DevicesOnHistory as( select * from(values
   (4787, 4298, convert(bit,1), convert(datetime,'21.01.2013 13:46:00',104)), (4749, 4314, 0, convert(datetime,'09.01.2013 10:40:13',104)),
   (4785, 4314, 1, convert(datetime,'17.01.2013 16:11:34',104)), (4752, 4327, 1, convert(datetime,'09.01.2013 10:43:10',104)),
   (4755, 4383, 0, convert(datetime,'10.01.2013 15:41:47',104)), (4766, 4383, 1, convert(datetime,'14.01.2013 10:41:59',104)),
   (4750, 4444, 0, convert(datetime,'09.01.2013 10:41:02',104)), (4779, 4779, 0, convert(datetime,'16.01.2013 12:53:30',104)),
   (4765, 4832, 0, convert(datetime,'11.01.2013 15:57:47',104)), (4756, 4886, 0, convert(datetime,'10.01.2013 15:44:46',104)),
   (4748, 4918, 0, convert(datetime,'03.01.2013 11:52:49',104)), (4759, 6022, 0, convert(datetime,'11.01.2013 15:09:07',104)),
   (4775, 6022, 1, convert(datetime,'15.01.2013 14:29:29',104)), (4776, 6022, 1, convert(datetime,'15.01.2013 15:10:28',104)),
   (4783, 6025, 1, convert(datetime,'17.01.2013 11:40:37',104)), (4782, 6029, 1, convert(datetime,'17.01.2013 11:40:11',104)),
   (4780, 6036, 1, convert(datetime,'16.01.2013 12:57:22',104)), (4761, 6049, 0, convert(datetime,'11.01.2013 15:54:52',104)),
   (4771, 6049, 1, convert(datetime,'15.01.2013 13:34:13',104)), (4772, 6049, 1, convert(datetime,'15.01.2013 13:34:12',104)),
   (4760, 6051, 0, convert(datetime,'11.01.2013 15:54:15',104)), (4768, 6051, 1, convert(datetime,'15.01.2013 12:28:15',104)),
   (4773, 6051, 0, convert(datetime,'15.01.2013 13:34:36',104)), (4774, 6051, 1, convert(datetime,'15.01.2013 13:34:40',104)),
   (4762, 6052, 0, convert(datetime,'11.01.2013 15:55:34',104)), (4769, 6052, 1, convert(datetime,'15.01.2013 13:08:44',104)),
   (4770, 6052, 1, convert(datetime,'15.01.2013 13:33:41',104)), (4763, 6066, 0, convert(datetime,'11.01.2013 15:56:10',104)),
   (4777, 6066, 1, convert(datetime,'15.01.2013 16:34:24',104)), (4767, 6075, 1, convert(datetime,'14.01.2013 15:37:57',104)),
   (4764, 6076, 0, convert(datetime,'11.01.2013 15:57:04',104)), (4778, 6076, 1, convert(datetime,'15.01.2013 16:34:32',104)),
   (4758, 6094, 1, convert(datetime,'11.01.2013 12:35:10',104)), (4751, 6121, 1, convert(datetime,'09.01.2013 10:42:04',104)),
   (4753, 6121, 0, convert(datetime,'10.01.2013 15:40:22',104)), (4781, 6344, 0, convert(datetime,'16.01.2013 18:44:10',104)),
   (4788, 6345, 0, convert(datetime,'21.01.2013 13:46:36',104)), (4784, 6366, 0, convert(datetime,'17.01.2013 12:55:44',104)),
   (4754, 6495, 0, convert(datetime,'10.01.2013 15:41:06',104)), (4747, 6534, 0, convert(datetime,'03.01.2013 11:52:13',104)),
   (4786, 6534, 1, convert(datetime,'18.01.2013 12:56:19',104)), (4757, 6552, 0, convert(datetime,'10.01.2013 18:29:49',104))
   )tmp(IDE, IDD, ST, DATE_ST))
   , 
-- базовый запрос
base as(
SELECT T.IDE ,T.IDD ,T.ST ,T.DATE_ST, row_number() over(partition by T.IDD order by T.DATE_ST asc) as N
FROM
DevicesOnHistory AS T
WHERE 1=1
AND T.DATE_ST >= @from
AND T.DATE_ST <= @to 
)
select IDD, COUNT(*) from base group by IDD order by COUNT(*) desc
6051	4
6052	3
6049	3
6022	3
4383	2
4314	2
6066	2
6121	2
6076	2
6534	2
6552	1
6094	1
6344	1
6345	1
6366	1
-----------------------
-- на чем тута считать ДНИ?
4760	6051	0	2013-01-11 15:54:15.000	1
4768	6051	1	2013-01-15 12:28:15.000	2
4773	6051	0	2013-01-15 13:34:36.000	3
4774	6051	1	2013-01-15 13:34:40.000	4
---- некоторая засада в том, что начинаться должно с "включилось", а заканчиваться "выключилось", но это мелочи.
25 янв 13, 17:49    [13829791]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
D.Solonskiy
Cygapb-007,
Ура!!!
вообщем почти заработало только вот есть момент это скорее исключение может я чего не понимаю запрос был с первого по 25
дней получается 25 а по результату 24 я так понимаю если складывать и рабочие и не рабочие должно получится 25

6051	01.01.2013 0:00:00	1	10	0	11.01.2013 15:54:15	0	4760	1
6051 11.01.2013 15:54:15 0 4 1 15.01.2013 12:28:15 4760 4768 2
6051 15.01.2013 12:28:15 1 0 0 15.01.2013 13:34:36 4768 4773 3
6051 15.01.2013 13:34:36 0 0 1 15.01.2013 13:34:40 4773 4774 4
6051 15.01.2013 13:34:40 1 10 0 25.01.2013 0:00:00 4774 4775 5
А, точно.. При конвертации последней даты получается она жа, но на самое утро, на 0:00:00 вместо 23:59:59
вот так точнее будет
declare @from datetime='2013-01-01', @to datetime=dateadd(SECOND,-1,dateadd(dd,1,'2013-01-22'))
select @from, @to
25 янв 13, 17:55    [13829826]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
aleks2
Cygapb-007
пропущено...
хмм... а свой же код уже не узнаете?
в вашем запросе ничего не менялось, только были вставлены в начало тестовые данные.

или это высказывание в качестве самокритики?


Испоганить можно любую идею. Особенно, если понимание отсутствует.

1. Научись приуготовлять данные.
Не понял вопроса... на этом, данные один в один из красиво развернутого, но без группировки в конце...
+
set dateformat ymd
declare @from datetime='2013-01-01', @to datetime='2013-01-22'
set dateformat dmy
;with
DevicesOnHistory as( select * from(values
   (4787, 4298, convert(bit,1), convert(datetime,'21.01.2013 13:46:00',104)), (4749, 4314, 0, convert(datetime,'09.01.2013 10:40:13',104)),
   (4785, 4314, 1, convert(datetime,'17.01.2013 16:11:34',104)), (4752, 4327, 1, convert(datetime,'09.01.2013 10:43:10',104)),
   (4755, 4383, 0, convert(datetime,'10.01.2013 15:41:47',104)), (4766, 4383, 1, convert(datetime,'14.01.2013 10:41:59',104)),
   (4750, 4444, 0, convert(datetime,'09.01.2013 10:41:02',104)), (4779, 4779, 0, convert(datetime,'16.01.2013 12:53:30',104)),
   (4765, 4832, 0, convert(datetime,'11.01.2013 15:57:47',104)), (4756, 4886, 0, convert(datetime,'10.01.2013 15:44:46',104)),
   (4748, 4918, 0, convert(datetime,'03.01.2013 11:52:49',104)), (4759, 6022, 0, convert(datetime,'11.01.2013 15:09:07',104)),
   (4775, 6022, 1, convert(datetime,'15.01.2013 14:29:29',104)), (4776, 6022, 1, convert(datetime,'15.01.2013 15:10:28',104)),
   (4783, 6025, 1, convert(datetime,'17.01.2013 11:40:37',104)), (4782, 6029, 1, convert(datetime,'17.01.2013 11:40:11',104)),
   (4780, 6036, 1, convert(datetime,'16.01.2013 12:57:22',104)), (4761, 6049, 0, convert(datetime,'11.01.2013 15:54:52',104)),
   (4771, 6049, 1, convert(datetime,'15.01.2013 13:34:13',104)), (4772, 6049, 1, convert(datetime,'15.01.2013 13:34:12',104)),
   (4760, 6051, 0, convert(datetime,'11.01.2013 15:54:15',104)), (4768, 6051, 1, convert(datetime,'15.01.2013 12:28:15',104)),
   (4773, 6051, 0, convert(datetime,'15.01.2013 13:34:36',104)), (4774, 6051, 1, convert(datetime,'15.01.2013 13:34:40',104)),
   (4762, 6052, 0, convert(datetime,'11.01.2013 15:55:34',104)), (4769, 6052, 1, convert(datetime,'15.01.2013 13:08:44',104)),
   (4770, 6052, 1, convert(datetime,'15.01.2013 13:33:41',104)), (4763, 6066, 0, convert(datetime,'11.01.2013 15:56:10',104)),
   (4777, 6066, 1, convert(datetime,'15.01.2013 16:34:24',104)), (4767, 6075, 1, convert(datetime,'14.01.2013 15:37:57',104)),
   (4764, 6076, 0, convert(datetime,'11.01.2013 15:57:04',104)), (4778, 6076, 1, convert(datetime,'15.01.2013 16:34:32',104)),
   (4758, 6094, 1, convert(datetime,'11.01.2013 12:35:10',104)), (4751, 6121, 1, convert(datetime,'09.01.2013 10:42:04',104)),
   (4753, 6121, 0, convert(datetime,'10.01.2013 15:40:22',104)), (4781, 6344, 0, convert(datetime,'16.01.2013 18:44:10',104)),
   (4788, 6345, 0, convert(datetime,'21.01.2013 13:46:36',104)), (4784, 6366, 0, convert(datetime,'17.01.2013 12:55:44',104)),
   (4754, 6495, 0, convert(datetime,'10.01.2013 15:41:06',104)), (4747, 6534, 0, convert(datetime,'03.01.2013 11:52:13',104)),
   (4786, 6534, 1, convert(datetime,'18.01.2013 12:56:19',104)), (4757, 6552, 0, convert(datetime,'10.01.2013 18:29:49',104))
   )tmp(IDE, IDD, ST, DATE_ST))
   , 
-- базовый запрос
base as(
SELECT T.IDE ,T.IDD ,T.ST ,T.DATE_ST, row_number() over(partition by T.IDD order by T.DATE_ST asc) as N
FROM
DevicesOnHistory AS T
WHERE 1=1
AND T.DATE_ST >= @from
AND T.DATE_ST <= @to 
)
select *--IDD, COUNT(*) 
from base 
--group by IDD order by COUNT(*) desc
--6051	4
--6052	3
--6049	3
--6022	3
--4383	2
--4314	2
--6066	2
--6121	2
--6076	2
--6534	2
--6552	1
--6094	1
--6344	1
--6345	1
--6366	1
-------------------------
---- на чем тута считать ДНИ?
--4760	6051	0	2013-01-11 15:54:15.000	1
--4768	6051	1	2013-01-15 12:28:15.000	2
--4773	6051	0	2013-01-15 13:34:36.000	3
--4774	6051	1	2013-01-15 13:34:40.000	4
------ некоторая засада в том, что начинаться должно с "включилось", а заканчиваться "выключилось", но это мелочи.
IDE         IDD         ST          DATE_ST                 N
----------- ----------- ----------- ----------------------- --------------------
4787 4298 1 2013-01-21 13:46:00.000 1
4749 4314 0 2013-01-09 10:40:13.000 1
4785 4314 1 2013-01-17 16:11:34.000 2
4752 4327 1 2013-01-09 10:43:10.000 1
4755 4383 0 2013-01-10 15:41:47.000 1
4766 4383 1 2013-01-14 10:41:59.000 2
...
Вот на этих данных и надо решить задачу
25 янв 13, 18:04    [13829867]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
D.Solonskiy
Member

Откуда:
Сообщений: 23
Cygapb-007,
нет все равно 24 получается(

6051 Подключено 01.01.2013 0:00:00 11.01.2013 15:54:15 10
6051 Отключено 11.01.2013 15:54:15 15.01.2013 12:28:15 4
6051 Подключено 15.01.2013 12:28:15 15.01.2013 13:34:36 0
6051 Отключено 15.01.2013 13:34:36 15.01.2013 13:34:40 0
6051 Подключено 15.01.2013 13:34:40 25.01.2013 23:59:59 10
25 янв 13, 18:07    [13829881]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
D.Solonskiy
Member

Откуда:
Сообщений: 23
Cygapb-007,
нет все равно 24 получается( может это из-за того что я в дни перевел?

выборка с 01.01.2013 0:00:00 по 25.01.2013 23:59:59

6051 Подключено 01.01.2013 0:00:00 11.01.2013 15:54:15 10 правильно 11 рабочих дней
6051 Отключено 11.01.2013 15:54:15 15.01.2013 12:28:15 4 правильно 3 не рабочих дней
6051 Подключено 15.01.2013 12:28:15 15.01.2013 13:34:36 0
6051 Отключено 15.01.2013 13:34:36 15.01.2013 13:34:40 0
6051 Подключено 15.01.2013 13:34:40 25.01.2013 23:59:59 10 правильно 11 рабочих дней

итого 25 дней
25 янв 13, 18:10    [13829893]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
D.Solonskiy
Cygapb-007,
нет все равно 24 получается(

6051	Подключено	01.01.2013 0:00:00	11.01.2013 15:54:15	10
6051 Отключено 11.01.2013 15:54:15 15.01.2013 12:28:15 4
6051 Подключено 15.01.2013 12:28:15 15.01.2013 13:34:36 0
6051 Отключено 15.01.2013 13:34:36 15.01.2013 13:34:40 0
6051 Подключено 15.01.2013 13:34:40 25.01.2013 23:59:59 10
Ааа, имеется в виду сумма по последнему столбцу... надо часы не учитывать чтобы точно все сошлось, ятд
25 янв 13, 18:19    [13829932]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
D.Solonskiy
Cygapb-007,
нет все равно 24 получается( может это из-за того что я в дни перевел?

выборка с 01.01.2013 0:00:00 по 25.01.2013 23:59:59

6051	Подключено	01.01.2013 0:00:00	11.01.2013 15:54:15	10    правильно 11 рабочих дней
6051 Отключено 11.01.2013 15:54:15 15.01.2013 12:28:15 4 правильно 3 не рабочих дней
6051 Подключено 15.01.2013 12:28:15 15.01.2013 13:34:36 0
6051 Отключено 15.01.2013 13:34:36 15.01.2013 13:34:40 0
6051 Подключено 15.01.2013 13:34:40 25.01.2013 23:59:59 10 правильно 11 рабочих дней

итого 25 дней

Наверное так:
Подключено=>дата2++ (дней=(дата2+1)-дата1=11)
Отключено => дата1++ (дней=дата2-(дата1+1)=3)
25 янв 13, 18:28    [13829972]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
aleks2
Guest
D.Solonskiy
Нужно посчитать количество рабочих дней для каждого устройства в заданном интервале времени.

Вот собственно как хранятся данные:
IDE - Идент. события
IDD - Идент. устройства
ST - Состояние устройства Включен/Выключен
DATE_ST - Время включения или отключения

1. Специально для слабонервных, я отфильтровал ДВА "устройства" IDD in ( 6051, 6052 )
2. Идиотское метание с форматом даты - НЕ мое.

set dateformat ymd
declare @from datetime='2013-01-01', @to datetime='2013-01-22'
set dateformat dmy
;with
DevicesOnHistory as( select * from(values
   (4787, 4298, convert(bit,1), convert(datetime,'21.01.2013 13:46:00',104)), (4749, 4314, 0, convert(datetime,'09.01.2013 10:40:13',104)),
   (4785, 4314, 1, convert(datetime,'17.01.2013 16:11:34',104)), (4752, 4327, 1, convert(datetime,'09.01.2013 10:43:10',104)),
   (4755, 4383, 0, convert(datetime,'10.01.2013 15:41:47',104)), (4766, 4383, 1, convert(datetime,'14.01.2013 10:41:59',104)),
   (4750, 4444, 0, convert(datetime,'09.01.2013 10:41:02',104)), (4779, 4779, 0, convert(datetime,'16.01.2013 12:53:30',104)),
   (4765, 4832, 0, convert(datetime,'11.01.2013 15:57:47',104)), (4756, 4886, 0, convert(datetime,'10.01.2013 15:44:46',104)),
   (4748, 4918, 0, convert(datetime,'03.01.2013 11:52:49',104)), (4759, 6022, 0, convert(datetime,'11.01.2013 15:09:07',104)),
   (4775, 6022, 1, convert(datetime,'15.01.2013 14:29:29',104)), (4776, 6022, 1, convert(datetime,'15.01.2013 15:10:28',104)),
   (4783, 6025, 1, convert(datetime,'17.01.2013 11:40:37',104)), (4782, 6029, 1, convert(datetime,'17.01.2013 11:40:11',104)),
   (4780, 6036, 1, convert(datetime,'16.01.2013 12:57:22',104)), (4761, 6049, 0, convert(datetime,'11.01.2013 15:54:52',104)),
   (4771, 6049, 1, convert(datetime,'15.01.2013 13:34:13',104)), (4772, 6049, 1, convert(datetime,'15.01.2013 13:34:12',104)),
   (4760, 6051, 0, convert(datetime,'11.01.2013 15:54:15',104)), (4768, 6051, 1, convert(datetime,'15.01.2013 12:28:15',104)),
   (4773, 6051, 0, convert(datetime,'15.01.2013 13:34:36',104)), (4774, 6051, 1, convert(datetime,'15.01.2013 13:34:40',104)),
   (4762, 6052, 0, convert(datetime,'11.01.2013 15:55:34',104)), (4769, 6052, 1, convert(datetime,'15.01.2013 13:08:44',104)),
   (4770, 6052, 1, convert(datetime,'15.01.2013 13:33:41',104)), (4763, 6066, 0, convert(datetime,'11.01.2013 15:56:10',104)),
   (4777, 6066, 1, convert(datetime,'15.01.2013 16:34:24',104)), (4767, 6075, 1, convert(datetime,'14.01.2013 15:37:57',104)),
   (4764, 6076, 0, convert(datetime,'11.01.2013 15:57:04',104)), (4778, 6076, 1, convert(datetime,'15.01.2013 16:34:32',104)),
   (4758, 6094, 1, convert(datetime,'11.01.2013 12:35:10',104)), (4751, 6121, 1, convert(datetime,'09.01.2013 10:42:04',104)),
   (4753, 6121, 0, convert(datetime,'10.01.2013 15:40:22',104)), (4781, 6344, 0, convert(datetime,'16.01.2013 18:44:10',104)),
   (4788, 6345, 0, convert(datetime,'21.01.2013 13:46:36',104)), (4784, 6366, 0, convert(datetime,'17.01.2013 12:55:44',104)),
   (4754, 6495, 0, convert(datetime,'10.01.2013 15:41:06',104)), (4747, 6534, 0, convert(datetime,'03.01.2013 11:52:13',104)),
   (4786, 6534, 1, convert(datetime,'18.01.2013 12:56:19',104)), (4757, 6552, 0, convert(datetime,'10.01.2013 18:29:49',104))
   )tmp(IDE, IDD, ST, DATE_ST))
   , 
-- базовый запрос
base as(
SELECT T.IDE ,T.IDD ,T.ST ,T.DATE_ST
, row_number() over(partition by T.IDD order by T.DATE_ST asc) as N
, row_number() over(partition by T.IDD order by T.DATE_ST desc) as N1
FROM
DevicesOnHistory AS T
WHERE 1=1
AND T.DATE_ST >= @from
AND T.DATE_ST <= @to 
and IDD in ( 6051, 6052 )
)
,
-- некоторая засада в том, что начинаться должно с "включилось", а заканчиваться "выключилось", но это мелочи.
-- специально для лохов эти мелочи отработаны
aligned as (
select * from base
union all
select IDE, IDD, 1 as ST, @from as DATE_ST, 0 N, null N1 from base where N=1 and ST = 0
union all
select IDE, IDD, 0 as ST, @to as DATE_ST, null N, 0 N1 from base where N1=1 and ST = 1
)
--select * from aligned order by IDD, DATE_ST
,
-- удаляем избыточность, оставляем только точки переключения состояния устройства
switchs as(
select *, datediff(minute, @from, DATE_ST) diff
from aligned b1 where not exists(select * from aligned b2 where b2.IDD=b1.IDD and b2.N=b1.N-1 and b2.ST=b1.ST )
)
--select * from switchs order by IDD, DATE_ST
,
-- дальше соображаем, что це таке "количество рабочих дней для каждого устройства"?
-- K = summa( Твыкл(i) - Tвкл(i) ) по всем периодам i "включилось-выключилось"
-- K = summa( Твыкл(i) ) - summa( Tвкл(i) )
workdays as (
   select IDD
        , sum((case ST when 1 then -1 else 1 end) * diff ) as WorkMinutes
        , sum((case ST when 1 then -1 else 1 end) * diff )/60/60.0 as Workdays
   from switchs
   group by IDD
   )
select * from workdays;
IDD     WorkMinutes  Workdays
6051	24686	     6.850000
6052	24647	     6.833333
25 янв 13, 18:28    [13829974]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
aleks2
D.Solonskiy
Нужно посчитать количество рабочих дней для каждого устройства в заданном интервале времени.

Вот собственно как хранятся данные:
IDE - Идент. события
IDD - Идент. устройства
ST - Состояние устройства Включен/Выключен
DATE_ST - Время включения или отключения

1. Специально для слабонервных, я отфильтровал ДВА "устройства" IDD in ( 6051, 6052 )
2. Идиотское метание с форматом даты - НЕ мое.

+ неужели так трудно не забивать обсуждение простынями? или культуры не хватает?
set dateformat ymd
declare @from datetime='2013-01-01', @to datetime='2013-01-22'
set dateformat dmy
;with
DevicesOnHistory as( select * from(values
   (4787, 4298, convert(bit,1), convert(datetime,'21.01.2013 13:46:00',104)), (4749, 4314, 0, convert(datetime,'09.01.2013 10:40:13',104)),
   (4785, 4314, 1, convert(datetime,'17.01.2013 16:11:34',104)), (4752, 4327, 1, convert(datetime,'09.01.2013 10:43:10',104)),
   (4755, 4383, 0, convert(datetime,'10.01.2013 15:41:47',104)), (4766, 4383, 1, convert(datetime,'14.01.2013 10:41:59',104)),
   (4750, 4444, 0, convert(datetime,'09.01.2013 10:41:02',104)), (4779, 4779, 0, convert(datetime,'16.01.2013 12:53:30',104)),
   (4765, 4832, 0, convert(datetime,'11.01.2013 15:57:47',104)), (4756, 4886, 0, convert(datetime,'10.01.2013 15:44:46',104)),
   (4748, 4918, 0, convert(datetime,'03.01.2013 11:52:49',104)), (4759, 6022, 0, convert(datetime,'11.01.2013 15:09:07',104)),
   (4775, 6022, 1, convert(datetime,'15.01.2013 14:29:29',104)), (4776, 6022, 1, convert(datetime,'15.01.2013 15:10:28',104)),
   (4783, 6025, 1, convert(datetime,'17.01.2013 11:40:37',104)), (4782, 6029, 1, convert(datetime,'17.01.2013 11:40:11',104)),
   (4780, 6036, 1, convert(datetime,'16.01.2013 12:57:22',104)), (4761, 6049, 0, convert(datetime,'11.01.2013 15:54:52',104)),
   (4771, 6049, 1, convert(datetime,'15.01.2013 13:34:13',104)), (4772, 6049, 1, convert(datetime,'15.01.2013 13:34:12',104)),
   (4760, 6051, 0, convert(datetime,'11.01.2013 15:54:15',104)), (4768, 6051, 1, convert(datetime,'15.01.2013 12:28:15',104)),
   (4773, 6051, 0, convert(datetime,'15.01.2013 13:34:36',104)), (4774, 6051, 1, convert(datetime,'15.01.2013 13:34:40',104)),
   (4762, 6052, 0, convert(datetime,'11.01.2013 15:55:34',104)), (4769, 6052, 1, convert(datetime,'15.01.2013 13:08:44',104)),
   (4770, 6052, 1, convert(datetime,'15.01.2013 13:33:41',104)), (4763, 6066, 0, convert(datetime,'11.01.2013 15:56:10',104)),
   (4777, 6066, 1, convert(datetime,'15.01.2013 16:34:24',104)), (4767, 6075, 1, convert(datetime,'14.01.2013 15:37:57',104)),
   (4764, 6076, 0, convert(datetime,'11.01.2013 15:57:04',104)), (4778, 6076, 1, convert(datetime,'15.01.2013 16:34:32',104)),
   (4758, 6094, 1, convert(datetime,'11.01.2013 12:35:10',104)), (4751, 6121, 1, convert(datetime,'09.01.2013 10:42:04',104)),
   (4753, 6121, 0, convert(datetime,'10.01.2013 15:40:22',104)), (4781, 6344, 0, convert(datetime,'16.01.2013 18:44:10',104)),
   (4788, 6345, 0, convert(datetime,'21.01.2013 13:46:36',104)), (4784, 6366, 0, convert(datetime,'17.01.2013 12:55:44',104)),
   (4754, 6495, 0, convert(datetime,'10.01.2013 15:41:06',104)), (4747, 6534, 0, convert(datetime,'03.01.2013 11:52:13',104)),
   (4786, 6534, 1, convert(datetime,'18.01.2013 12:56:19',104)), (4757, 6552, 0, convert(datetime,'10.01.2013 18:29:49',104))
   )tmp(IDE, IDD, ST, DATE_ST))
   , 
-- базовый запрос
base as(
SELECT T.IDE ,T.IDD ,T.ST ,T.DATE_ST
, row_number() over(partition by T.IDD order by T.DATE_ST asc) as N
, row_number() over(partition by T.IDD order by T.DATE_ST desc) as N1
FROM
DevicesOnHistory AS T
WHERE 1=1
AND T.DATE_ST >= @from
AND T.DATE_ST <= @to 
and IDD in ( 6051, 6052 )
)
,
-- некоторая засада в том, что начинаться должно с "включилось", а заканчиваться "выключилось", но это мелочи.
-- специально для лохов эти мелочи отработаны
aligned as (
select * from base
union all
select IDE, IDD, 1 as ST, @from as DATE_ST, 0 N, null N1 from base where N=1 and ST = 0
union all
select IDE, IDD, 0 as ST, @to as DATE_ST, null N, 0 N1 from base where N1=1 and ST = 1
)
--select * from aligned order by IDD, DATE_ST
,
-- удаляем избыточность, оставляем только точки переключения состояния устройства
switchs as(
select *, datediff(minute, @from, DATE_ST) diff
from aligned b1 where not exists(select * from aligned b2 where b2.IDD=b1.IDD and b2.N=b1.N-1 and b2.ST=b1.ST )
)
--select * from switchs order by IDD, DATE_ST
,
-- дальше соображаем, что це таке "количество рабочих дней для каждого устройства"?
-- K = summa( Твыкл(i) - Tвкл(i) ) по всем периодам i "включилось-выключилось"
-- K = summa( Твыкл(i) ) - summa( Tвкл(i) )
workdays as (
   select IDD
        , sum((case ST when 1 then -1 else 1 end) * diff ) as WorkMinutes
        , sum((case ST when 1 then -1 else 1 end) * diff )/60/60.0 as Workdays
   from switchs
   group by IDD
   )
select * from workdays;

IDD     WorkMinutes  Workdays
6051 24686 6.850000
6052 24647 6.833333
Это типа задачка "найдите 10 отличий" между первым неработающим 13828245 и последним работающим запросами?
25 янв 13, 18:57    [13830094]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
D.Solonskiy
Member

Откуда:
Сообщений: 23
aleks2,

Я не понял устройство работало если посчитать вручную работало 22 дня если считать вручную с 2013-01-01 по 2013-01-25 а получается чуть более 8
25 янв 13, 18:59    [13830106]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить