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

Откуда:
Сообщений: 23
Нужно посчитать количество рабочих и не рабочих дней для каждого устройства в заданном интервале времени.
Я так думаю что проще всего сделать это сл. образом посчитать дни в заданном периоде и принять их как рабочие,
а затем посчитать все периоды когда устройство не работало и отнять в теории все просто (

я не специалист в TSQL но вот получился такой запрос:
select t1.DeviceOnLine,t1.DeviceID,t1.EventDate,
case
when t1.DeviceOnLine = 0 THEN (select top 1 EventDate from HD_DevicesOnLineHistory where EventDate > t1.EventDate and EventDate > '2013-01-01' and DeviceOnLine = 1 and DeviceID = t1.DeviceID order by DeviceID ,EventID )
--when t1.DeviceOnLine = 1 THEN (select top 1 EventDate from HD_DevicesOnLineHistory where EventDate > t1.EventDate and EventDate > '2013-01-01' and DeviceOnLine = 0 and DeviceID = t1.DeviceID )
else NULL
END as EnableDate

from HD_DevicesOnLineHistory as t1
where
--and t1.DeviceId = 6051
--AND
-- t1.DeviceOnLine = 0
t1.EventDate >= '2013-01-01'
AND t1.EventDate <= '2013-01-24'
order by DeviceID ,EventID
и если результат вывести во временную табличку и там посчитать то все работает, но не во всех случаях....
может есть более простой способ это сделать заранее благодарен)

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


Вот базовый запрос:
SELECT T.IDE ,T.IDD ,T.ST ,T.DATE_ST
FROM
DevicesOnHistory AS T
WHERE 1=1
AND T.DATE_ST >= '2013-01-01'
AND T.DATE_ST <= '2013-01-22'
ORDER BY T.IDD,T.IDE

IDE IDD ST DATE_ST
4787 4298 True 21.01.2013 13:46:00
4749 4314 False 09.01.2013 10:40:13
4785 4314 True 17.01.2013 16:11:34
4752 4327 True 09.01.2013 10:43:10
4755 4383 False 10.01.2013 15:41:47
4766 4383 True 14.01.2013 10:41:59
4750 4444 False 09.01.2013 10:41:02
4779 4779 False 16.01.2013 12:53:30
4765 4832 False 11.01.2013 15:57:47
4756 4886 False 10.01.2013 15:44:46
4748 4918 False 03.01.2013 11:52:49
4759 6022 False 11.01.2013 15:09:07
4775 6022 True 15.01.2013 14:29:29
4776 6022 True 15.01.2013 15:10:28
4783 6025 True 17.01.2013 11:40:37
4782 6029 True 17.01.2013 11:40:11
4780 6036 True 16.01.2013 12:57:22
4761 6049 False 11.01.2013 15:54:52
4771 6049 True 15.01.2013 13:34:13
4772 6049 True 15.01.2013 13:34:12
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
4762 6052 False 11.01.2013 15:55:34
4769 6052 True 15.01.2013 13:08:44
4770 6052 True 15.01.2013 13:33:41
4763 6066 False 11.01.2013 15:56:10
4777 6066 True 15.01.2013 16:34:24
4767 6075 True 14.01.2013 15:37:57
4764 6076 False 11.01.2013 15:57:04
4778 6076 True 15.01.2013 16:34:32
4758 6094 True 11.01.2013 12:35:10
4751 6121 True 09.01.2013 10:42:04
4753 6121 False 10.01.2013 15:40:22
4781 6344 False 16.01.2013 18:44:10
4788 6345 False 21.01.2013 13:46:36
4784 6366 False 17.01.2013 12:55:44
4754 6495 False 10.01.2013 15:41:06
4747 6534 False 03.01.2013 11:52:13
4786 6534 True 18.01.2013 12:56:19
4757 6552 False 10.01.2013 18:29:49
25 янв 13, 11:57    [13826572]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
-- select  CHARINDEX('True','True'), CHARINDEX('True','False')
;with 
device as (
   select distinct h.IDE
      from DevicesOnHistory h
   ) 
select d.IDE, a.dateMIN [from], a.dateMAX [to], a.have1 totalWORK, a.total-a.have1 totalSTOP
   from device d  
   cross apply (
      select 
         min(h.EventDATE) dateMIN, MAX(h.EventDATE) dateMAX, COUNT(*) total, 
         -- SUM(h.ST) have1 -- если h.ST = 1 или 0
         SUM(CHARINDEX('True',h.ST)) have1 -- если h.ST = 'True' или 'False'
      from DevicesOnHistory h
      where h.IDE=d.IDE and h.EventDATE between '2013-01-01' and '2013-01-22' 
   ) a
order by d.IDE
25 янв 13, 12:47    [13826936]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

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

Откуда:
Сообщений: 23
Cygapb-007
-- select  CHARINDEX('True','True'), CHARINDEX('True','False')
;with 
device as (
   select distinct h.IDE
      from DevicesOnHistory h
   ) 
select d.IDE, a.dateMIN [from], a.dateMAX [to], a.have1 totalWORK, a.total-a.have1 totalSTOP
   from device d  
   cross apply (
      select 
         min(h.EventDATE) dateMIN, MAX(h.EventDATE) dateMAX, COUNT(*) total, 
         -- SUM(h.ST) have1 -- если h.ST = 1 или 0
Вот тут честно говоря не понял зачем SUM на битовое поле там либо 1 либо 0 ....попробовал прогнать на рел. таблички ругается(
         SUM(CHARINDEX('True',h.ST)) have1 -- если h.ST = 'True' или 'False'
      from DevicesOnHistory h
      where h.IDE=d.IDE and h.EventDATE between '2013-01-01' and '2013-01-22' 
   ) a
order by d.IDE
25 янв 13, 13:00    [13827039]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
что хранится в ST ? char('True'/'False')? или 0/1 ?
если 0/1 - то снять комментарий со строки выше, а неверную закомментировать

идет подсчет количества отработанных дней (st=1)
25 янв 13, 13:06    [13827095]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
D.Solonskiy
Member

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

Поправил

вот что получилось:

;with
device as (
select distinct h.DeviceID
from HD_DevicesOnLineHistory h
)
select d.DeviceID, a.dateMIN [from], a.dateMAX [to], a.have1 totalWORK, a.total-a.have1 totalSTOP
from device d
cross apply (
select
min(h.EventDate) dateMIN, MAX(h.EventDate) dateMAX, COUNT(*) total,
sum(h.DeviceOnLine) have1 -- если h.ST = 1 или 0
Вот тут ругается!!!!
-- SUM(CHARINDEX('True',h.DeviceOnLine)) have1 -- если h.ST = 'True' или 'False'
from HD_DevicesOnLineHistory h
where h.DeviceID=d.DeviceID and h.EventDate between '2013-01-01' and '2013-01-22'
) a
order by d.DeviceID
Возвращает если с CHARINDEX:
662 NULL NULL NULL NULL
1000 NULL NULL NULL NULL
1001 NULL NULL NULL NULL
2773 NULL NULL NULL NULL
2786 NULL NULL NULL NULL
2792 NULL NULL NULL NULL
2909 NULL NULL NULL NULL
ну и так далее....
25 янв 13, 13:10    [13827135]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

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

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

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

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

да там есть еще такой нюанс допустим день отключения считается рабочим днем ну и соответственно получается такая штука
если запрашивают отчет с 01-01-2013 по 25-01-2013 года, а там находится по данному устройству две строки
15-01-2013 10:00 включено
15-01-2013 10:01 выключено
это означает что итого 24 дня не рабочих и 1 день рабочий ....вот где то так
DeviceOnLine
0
1
25 янв 13, 13:25    [13827327]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
D.Solonskiy
Member

Откуда:
Сообщений: 23
Cygapb-007,
Да и выводить дату начала и конца мне кажется не получится на мой не проф. взгляд так как периодов может быть больше чем один... если только не будет повторяющихся строк по DeviceID....
25 янв 13, 13:30    [13827377]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
D.Solonskiy, да, впрямую на bit ругается...
...SUM(cast(h.DeviceOnLine as int)) have1...
25 янв 13, 13:32    [13827402]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
D.Solonskiy
Cygapb-007,
Да и выводить дату начала и конца мне кажется не получится на мой не проф. взгляд так как периодов может быть больше чем один... если только не будет повторяющихся строк по DeviceID....
Ээээ.. Период один по условию.
Просто в этом периоде разные устройства опрашиваются в разные моменты времени.
В запросе для каждого устройства отображается доверительный диапазон дат, за который точно можно сказать, что было с устройством.
Если не нужен - в топку, быстрее будет работать:)
25 янв 13, 13:36    [13827451]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
D.Solonskiy
Member

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

Вот результат не понимаю что-то не то (((
;with
device as (
select distinct h.DeviceID
from HD_DevicesOnLineHistory h
)
select d.DeviceID, a.dateMIN [from], a.dateMAX [to], a.have1 totalWORK, a.total-a.have1 totalSTOP
from device d
cross apply (
select
min(h.EventDate) dateMIN, MAX(h.EventDate) dateMAX, COUNT(*) total,
SUM(cast(h.DeviceOnLine as int)) have1 -- если h.ST = 1 или 0
from HD_DevicesOnLineHistory h
where h.DeviceID=d.DeviceID and h.EventDate between '2013-01-01' and '2013-01-22'
) a
order by d.DeviceID

662 NULL NULL NULL NULL
1000 NULL NULL NULL NULL
1001 NULL NULL NULL NULL
2773 NULL NULL NULL NULL
2786 NULL NULL NULL NULL
2792 NULL NULL NULL NULL
2909 NULL NULL NULL NULL
2915 NULL NULL NULL NULL
4001 NULL NULL NULL NULL
4002 NULL NULL NULL NULL
4003 NULL NULL NULL NULL
4004 NULL NULL NULL NULL
25 янв 13, 13:41    [13827490]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
бррр...
+ запрос
declare @DevicesOnHistory table(IDE int, DeviceID int, DeviceOnLine bit, EventDATE datetime)
insert @DevicesOnHistory 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)),
   (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)),
   (4786, 6534, 1, convert(datetime,'18.01.2013 12:56:19',104)), (4757, 6552, 0, convert(datetime,'10.01.2013 18:29:49',104))
-- select * from @DevicesOnHistory h
;with
device as (
   select distinct h.DeviceID
      from @DevicesOnHistory h
   ) 
select d.DeviceID, a.dateMIN [from], a.dateMAX [to], a.have1 totalWORK, a.total-a.have1 totalSTOP
   from device d  
   cross apply (
      select min(h.EventDATE) dateMIN, MAX(h.EventDATE) dateMAX, COUNT(*) total, SUM(cast(h.DeviceOnLine as int)) have1 
         from @DevicesOnHistory h
         where h.DeviceID=d.DeviceID and h.EventDATE between '2013-01-01' and '2013-01-22' 
   ) a
   order by d.DeviceID
DeviceID    from                    to                      totalWORK   totalSTOP
----------- ----------------------- ----------------------- ----------- -----------
4298 2013-01-21 13:46:00.000 2013-01-21 13:46:00.000 1 0
4314 2013-01-09 10:40:13.000 2013-01-09 10:40:13.000 0 1
6049 2013-01-15 13:34:12.000 2013-01-15 13:34:13.000 2 0
6051 2013-01-11 15:54:15.000 2013-01-15 13:34:40.000 2 2
6534 2013-01-18 12:56:19.000 2013-01-18 12:56:19.000 1 0
6552 2013-01-10 18:29:49.000 2013-01-10 18:29:49.000 0 1
что у меня не так с данными?
25 янв 13, 13:44    [13827540]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
D.Solonskiy
Member

Откуда:
Сообщений: 23
Cygapb-007
D.Solonskiy
Cygapb-007,
Да и выводить дату начала и конца мне кажется не получится на мой не проф. взгляд так как периодов может быть больше чем один... если только не будет повторяющихся строк по DeviceID....
Ээээ.. Период один по условию.
Просто в этом периоде разные устройства опрашиваются в разные моменты времени.
В запросе для каждого устройства отображается доверительный диапазон дат, за который точно можно сказать, что было с устройством.
Если не нужен - в топку, быстрее будет работать:)


Там в примере исходных данных были повторяющиеся диапазоны, но это я дурак не указал явно(

вот то что у меня получилось в виде запроса, но я не могу обработать всякого рода исключения из правил(

ALTER PROCEDURE [dbo].[RP_Report_100806]
-- @VehicleID INT,
-- @DateFrom DATETIME,
-- @DateTo DATETIME
AS
BEGIN
SET NOCOUNT ON

DECLARE @datestart DATETIME = '2013-01-01 00:00:59' --GETDATE();
DECLARE @dateend DATETIME = getdate();

DECLARE @daycount INT = DATEDIFF(DAY,@datestart,@dateend);
DECLARE @DAYWORK INT = 0;
DECLARE @DAYNOWORK INT = 0;


DECLARE @online int = NULL;
DECLARE @devId int = NULL;
DECLARE @lastdate DATETIME = NULL;
DECLARE @enddate DATETIME = NULL;

DECLARE @Counter INT = 0



DECLARE @TReport TABLE (
tDeviceOnLine INT,
tDeviceId INT NOT NULL,
tDateStart DATETIME,
tDateEnd INT
)

DECLARE @T2Report TABLE (
tDevice INT,
tDayEnable INT,
tDayDisable INT
)

DECLARE Vehicles CURSOR LOCAL READ_ONLY FAST_FORWARD
FOR

select t1.DeviceOnLine,t1.DeviceID,t1.EventDate,
case
when t1.DeviceOnLine = 0 THEN (select top 1 EventDate from HD_DevicesOnLineHistory where EventDate > t1.EventDate and EventDate > @datestart and DeviceOnLine = 1 and DeviceID = t1.DeviceID )
--when t1.DeviceOnLine = 1 THEN (select top 1 EventDate from HD_DevicesOnLineHistory where EventDate < t1.EventDate and EventDate < @datestart and DeviceOnLine = 0 and DeviceID = t1.DeviceID )
else NULL
END as EnableDate

from HD_DevicesOnLineHistory as t1
where 1=1

--t1.DeviceId = 4314--6051
AND
t1.DeviceOnLine = 0
AND t1.EventDate >= @datestart
AND t1.EventDate <= @dateend
order by deviceid,EventID

OPEN Vehicles

BEGIN
FETCH NEXT FROM Vehicles
into @online, @devID, @lastdate, @enddate

WHILE (@@FETCH_STATUS = 0)
BEGIN
/*
IF @Counter = 0
SET @DAYWORK = DATEDIFF(DAY, @datestart,@lastdate);
SET @datestart = @lastdate;

IF @Counter > 0
SET @DAYWORK += DATEDIFF(DAY, @datestart,@lastdate);
*/
IF @enddate IS NOT NULL
begin
SET @DAYNOWORK += DATEDIFF(DAY, @lastdate,@enddate)-1
end
IF @enddate IS NULL
begin
SET @DAYNOWORK += DATEDIFF(DAY, @lastdate,@dateend)
end

SET @Counter += 1


INSERT INTO @TReport (tDeviceOnLine,tDeviceId, tDateStart,tDateEnd)
values(@online,@devID,@lastdate,@daycount-@DAYNOWORK)
SET @DAYWORK = 0;
SET @DAYNOWORK = 0;

FETCH NEXT FROM Vehicles
into @online, @devID, @lastdate, @enddate
END
/*
SET @DAYWORK = @daycount - (@DAYNOWORK-1);

--Сумируем данные по ТС
INSERT INTO @T2Report (
tDevice ,
tDayEnable ,
tDayDisable)
values(@devID,@DAYWORK+1,@DAYNOWORK-1)
SET @DAYWORK = 0;
SET @DAYNOWORK = 0;
*/

END;
CLOSE Vehicles
DEALLOCATE Vehicles

SELECT DISTINCT * FROM @TReport

RETURN @DAYNOWORK

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

Откуда:
Сообщений: 1677
на самом деле все проще, но уж очень захотелось cross apply воткнуть))
+ данные
declare @DevicesOnHistory table(IDE int, DeviceID int, DeviceOnLine bit, EventDATE datetime)
insert @DevicesOnHistory 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)),
   (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)),
   (4786, 6534, 1, convert(datetime,'18.01.2013 12:56:19',104)), (4757, 6552, 0, convert(datetime,'10.01.2013 18:29:49',104))
-- select * from @DevicesOnHistory h
;with sel as (
   select h.DeviceID, COUNT(*) total, SUM(cast(h.DeviceOnLine as int)) have1 
   from @DevicesOnHistory h
   where h.EventDATE between '2013-01-01' and '2013-01-22' 
   group by h.DeviceID
   )
select s.DeviceID, s.have1 work, s.total-s.have1 stop
from sel s
order by DeviceID
25 янв 13, 13:52    [13827626]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
D.Solonskiy
Нужно посчитать количество рабочих и не рабочих дней для каждого устройства в заданном интервале времени.
Я так думаю что проще всего сделать это сл. образом посчитать дни в заданном периоде и принять их как рабочие,
а затем посчитать все периоды когда устройство не работало и отнять
Считает именно так, как запрошено, только вместо суммирования простоев считает суммирование замеров со статусом "Работает"

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

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

Откуда:
Сообщений: 23
вот теперь считает, но не дни работы и не работы, а кол-во включений и отключений я так понял )
я по прежнему не понял как из этого можно посчитать дни(
а должно то получится
вот так
DECLARE @datestart DATETIME = '2013-01-01 00:00:59' --GETDATE();
DECLARE @dateend DATETIME ='2013-01-25 00:00:59';

DECLARE @DAY_WORK_CNT INT = DATEDIFF(DAY,@datestart,@dateend);
DECLARE @DAYWORK INT = 0;
DECLARE @DAYNOWORK INT = 0;
.
.
.
ну и затем уже в результате
DeviceID | DAYWORK | DAYNOWORK
6051 23 3

извиняюсь за свое не понимание, но правда очень нужно разобраться(


Cygapb-007
на самом деле все проще, но уж очень захотелось cross apply воткнуть))+
+ данные
declare @DevicesOnHistory table(IDE int, DeviceID int, DeviceOnLine bit, EventDATE datetime)
insert @DevicesOnHistory 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)),
   (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)),
   (4786, 6534, 1, convert(datetime,'18.01.2013 12:56:19',104)), (4757, 6552, 0, convert(datetime,'10.01.2013 18:29:49',104))
-- select * from @DevicesOnHistory h
;with sel as (
   select h.DeviceID, COUNT(*) total, SUM(cast(h.DeviceOnLine as int)) have1 
   from @DevicesOnHistory h
   where h.EventDATE between '2013-01-01' and '2013-01-22' 
   group by h.DeviceID
   )
select s.DeviceID, s.have1 work, s.total-s.have1 stop
from sel s
order by DeviceID
25 янв 13, 14:05    [13827778]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

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

Откуда:
Сообщений: 23
Cygapb-007
D.Solonskiy
Нужно посчитать количество рабочих и не рабочих дней для каждого устройства в заданном интервале времени.
Я так думаю что проще всего сделать это сл. образом посчитать дни в заданном периоде и принять их как рабочие,
а затем посчитать все периоды когда устройство не работало и отнять
Считает именно так, как запрошено, только вместо суммирования простоев считает суммирование замеров со статусом "Работает"

Проверка делается раз в день или чаще?


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

Откуда:
Сообщений: 1677
D.Solonskiy
Cygapb-007
пропущено...
Считает именно так, как запрошено, только вместо суммирования простоев считает суммирование замеров со статусом "Работает"

Проверка делается раз в день или чаще?


Нет это будет делаться раз в месяц как итоговый подсчет вот почему мне нужны в итоге всего одна строчка с устройством и днями рабочими и не рабочими(
Уберите из 13827540 колонки с датами. Задача решена?
25 янв 13, 14:13    [13827875]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
ладно, переформулирую сам то, что решает запрос:

На всех устройствах раз в день делается замер состояния - работает или нет
Если замер показал, что работает - в этот день считается, что устройство работает (положительный замер),
иначе это день простоя

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

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

Ок, есть устройство это устройство включают и отключают , а так же могут вообще отключить и не включать в течении месяца и более ну или наоборот.... список всех устройств находится в таблице A, задача состоит в следующем в заданном интервале посчитать дни для каждого устройства сколько дней оно работало и соответственно не работало в таблице В содержатся данные по включению и выключению. А так же если устройство из таблицы А не найдено в таблице В в заданном интервале значит требуется найти последний статус устройства в таблице В и посчитать в соответствии со статусом дни работа/не работа с начала выбранного периода. Рабочими днями считаются дни включения и отключения, если включение происходило более чем на 1 мин
Вот так поставлена задача(
25 янв 13, 14:27    [13828043]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL Помогите составить запрос вычисления кол-ва рабочих дней и не рабочих  [new]
aleks2
Guest
Когда я вижу таких монстреков для простейшей задачи - рука сама тянется к пистолету...

;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;
-- некоторая засада в том, что начинаться должно с "включилось", а заканчиваться "выключилось", но это мелочи.
-- я уж не стал в них вдаваться...
25 янв 13, 14:45    [13828245]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить