Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 помогите написать запрос  [new]
yuri7811
Member

Откуда:
Сообщений: 19
Доброго времени суток.
Есть таблица с показаниями счетчика машины на:
(06-30 16-00) 1 смена
(16-30 23-59) 2 смена
(00-01 06-30) 3 смена


CREATE TABLE [dbo].[TotalCounters_Test1](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OnlyDate] [date] NULL ,
	[OnlyTime] [time](7) NULL ,
	[Sorter] [int] NULL)



+
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160810',112),Convert( VarChar( 10 ), '23:59:00.0000000', 114 ), 1543) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160810',112),Convert( VarChar( 10 ), '00:01:00.0000000', 114 ), 1543) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160810',112),Convert( VarChar( 10 ), '06:30:00.0000000', 114 ), 1678) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160810',112),Convert( VarChar( 10 ), '06:31:00.0000000', 114 ), 1678) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160810',112),Convert( VarChar( 10 ), '16:00:00.0000000', 114 ), 1800) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160810',112),Convert( VarChar( 10 ), '16:01:00.0000000', 114 ), 1800) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160811',112),Convert( VarChar( 10 ), '23:59:00.0000000', 114 ), 1967) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160811',112),Convert( VarChar( 10 ), '00:01:00.0000000', 114 ), 1967) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160811',112),Convert( VarChar( 10 ), '06:30:00.0000000', 114 ), 1567) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160811',112),Convert( VarChar( 10 ), '06:31:00.0000000', 114 ), 1567) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160811',112),Convert( VarChar( 10 ), '16:00:00.0000000', 114 ), 1624) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160811',112),Convert( VarChar( 10 ), '16:01:00.0000000', 114 ), 1624) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160812',112),Convert( VarChar( 10 ), '23:59:00.0000000', 114 ), 1789) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160812',112),Convert( VarChar( 10 ), '00:01:00.0000000', 114 ), 1789) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160812',112),Convert( VarChar( 10 ), '06:30:00.0000000', 114 ), 1900) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160812',112),Convert( VarChar( 10 ), '06:31:00.0000000', 114 ), 1900) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160812',112),Convert( VarChar( 10 ), '16:00:00.0000000', 114 ), 2078) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160812',112),Convert( VarChar( 10 ), '16:01:00.0000000', 114 ), 2078) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160813',112),Convert( VarChar( 10 ), '23:59:00.0000000', 114 ), 2300) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160813',112),Convert( VarChar( 10 ), '00:01:00.0000000', 114 ), 2300) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160813',112),Convert( VarChar( 10 ), '06:30:00.0000000', 114 ), 2456) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160813',112),Convert( VarChar( 10 ), '06:31:00.0000000', 114 ), 2456) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160813',112),Convert( VarChar( 10 ), '16:00:00.0000000', 114 ), 2567) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160813',112),Convert( VarChar( 10 ), '16:01:00.0000000', 114 ), 2567) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160814',112),Convert( VarChar( 10 ), '23:59:00.0000000', 114 ), 2677) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160814',112),Convert( VarChar( 10 ), '00:01:00.0000000', 114 ), 2677) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160814',112),Convert( VarChar( 10 ), '06:30:00.0000000', 114 ), 2832) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160814',112),Convert( VarChar( 10 ), '06:31:00.0000000', 114 ), 2832) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160814',112),Convert( VarChar( 10 ), '16:00:00.0000000', 114 ), 2993) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160814',112),Convert( VarChar( 10 ), '16:01:00.0000000', 114 ), 2993) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160815',112),Convert( VarChar( 10 ), '23:59:00.0000000', 114 ), 3190) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160815',112),Convert( VarChar( 10 ), '00:01:00.0000000', 114 ), 3190) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160815',112),Convert( VarChar( 10 ), '06:30:00.0000000', 114 ), 3342) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160815',112),Convert( VarChar( 10 ), '06:31:00.0000000', 114 ), 3342) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160815',112),Convert( VarChar( 10 ), '16:00:00.0000000', 114 ), 3523) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160815',112),Convert( VarChar( 10 ), '16:01:00.0000000', 114 ), 3523) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160816',112),Convert( VarChar( 10 ), '23:59:00.0000000', 114 ), 3700) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160816',112),Convert( VarChar( 10 ), '00:01:00.0000000', 114 ), 3700) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160816',112),Convert( VarChar( 10 ), '06:30:00.0000000', 114 ), 3802) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160816',112),Convert( VarChar( 10 ), '06:31:00.0000000', 114 ), 3802) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160816',112),Convert( VarChar( 10 ), '16:00:00.0000000', 114 ), 3983) ;
INSERT INTO TotalCounters_Test1 VALUES ( convert(DateTime,'20160816',112),Convert( VarChar( 10 ), '16:01:00.0000000', 114 ), 3983) ;



пытаюсь написать запрос но не очень получается:

SELECT TOP 7 *
FROM        (SELECT        OnlyDate, MAX(Sorter) - MIN(Sorter) AS I_smena
            FROM dbo.TotalCounters_Test1  WHERE  (OnlyTime BETWEEN '06:30:00' AND '16:00:00')
			GROUP BY OnlyDate) T1

left join  (SELECT        OnlyDate, MAX(Sorter) - MIN(Sorter) AS II_smena
            FROM dbo.TotalCounters_Test1 WHERE (OnlyTime BETWEEN '16:00:00' AND '23:59:59')
			GROUP BY OnlyDate) T2 on T2.OnlyDate=T1.OnlyDate

 left join (SELECT        OnlyDate, MAX(Sorter) - MIN(Sorter) AS III_smena
            FROM dbo.TotalCounters_Test1  WHERE (OnlyTime BETWEEN '00:00:01' AND '06:30:00')
			GROUP BY OnlyDate) T3	on T3.OnlyDate=T1.OnlyDate
 order by T1.OnlyDate desc


/*
 left join	(SELECT OnlyDate, T1.Sorter_I + T2.Sorter_II + T3.Sorter_III AS Day_Total 
            FROM dbo.TotalCounters_Test1 AS p 
			  ) T4  on T4.OnlyDate=T3.OnlyDate
*/


нужно получить таблицу:

OnlyDate !! I_smena !! II_smena !! III_smena !! Total (I+II+III)

ps. III_smena это данные за следующие сутки . рабочие сутки начинаются с 06.30 утра и заканчиваются в 06.30 следующего дня

заранее благодарю за помощь.

Сообщение было отредактировано: 3 дек 16, 17:05
3 дек 16, 16:37    [19963150]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
select onlydate
,sum(case when onlytime between '06:30:00.0000000' and '16:00:00.0000000' then sorter else 0 end) I_smena 
,sum(case when onlytime between '16:30:00.0000000' and '23:59:00.0000000' then sorter else 0 end) II_smena
,sum(case when onlytime between '00:01:00.0000000' and '06:29:00.0000000' then sorter else 0 end) III_smena
,sum(sorter) Total
from [dbo].[TotalCounters_Test1] 
group by onlydate
3 дек 16, 17:38    [19963330]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
yuri7811
Member

Откуда:
Сообщений: 19
3unknown,


спасибо но что то суммы не правильные получаются.


onlydate I_ II_ III_ Total
2016-08-05 424 125 312 1099
2016-08-06 1373 350 351 2607
2016-08-07 2266 645 641 4353
2016-08-08 2987 900 900 5842
2016-08-09 3866 1144 1144 7554
2016-08-10 5156 1543 1543 10042
2016-08-11 4758 1967 1967 10316
2016-08-12 5878 1789 1789 11534
2016-08-13 7479 2300 2300 14646
2016-08-14 8657 2677 2677 17004
2016-08-15 10207 3190 3190 20110
2016-08-16 11587 3700 3700 22970



мне нужно разница между показаниями счетчика на конец смены и начало смены причем третья смена это ночная смена но следующие календарные сутки :


OnlyDate I_smena
2016-08-05 50
2016-08-06 112
2016-08-07 67
2016-08-08 89
2016-08-09 167
2016-08-10 122
2016-08-11 57
2016-08-12 178
2016-08-13 111
2016-08-14 161
2016-08-15 181
2016-08-16 181



OnlyDate II_smena
2016-08-05 113
2016-08-06 183
2016-08-07 156
2016-08-08 155
2016-08-09 256
2016-08-10 257
2016-08-11 343
2016-08-12 289
2016-08-13 267
2016-08-14 316
2016-08-15 333
2016-08-16 283


OnlyDate III_smena
2016-08-05 60
2016-08-06 69
2016-08-07 92
2016-08-08 66
2016-08-09 89
2016-08-10 135
2016-08-11 400
2016-08-12 111
2016-08-13 156
2016-08-14 155
2016-08-15 152
2016-08-16 102

К сообщению приложен файл. Размер - 14Kb
3 дек 16, 18:27    [19963455]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
yuri7811
Member

Откуда:
Сообщений: 19


К сообщению приложен файл. Размер - 87Kb
3 дек 16, 18:38    [19963481]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
кролик-зануда
Guest
yuri7811,

00:00:00 у вас не попадает ни в одну смену, а 6:30 - в обе и 16:00 - в две смежных. это так и надо? или все таки надо прийти к одинаковому подходу во всех пересечениях смен?
sum в запросе 3unknown надо заменить на max - min, границы расставить с учетом сказанного выше
ну и group by тогда нужен в стиле onlydate - case when onlytime<='06:30:00' then 1 else 0 end
3 дек 16, 18:50    [19963521]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
кролик-зануда
Guest
если показания на 6:30 нужно учитывать при подсчете показаний как для 1 смены, так и для 3 смены предыдущих суток, то вариант с group by в таком виде не прокатит, надо будет либо дублировать эти строки для каждой из групп, либо считать 3 смену отдельным запросом
3 дек 16, 18:57    [19963549]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
yuri7811
Member

Откуда:
Сообщений: 19
кролик-зануда,
нет реально смены не пересекаются

1- 06.31-15.59
2- 16.01-23.59
3- 00.01-06.29


а можно поподробнее как будет выглядеть замена sum на max-min а то выдает ошибку
3 дек 16, 19:20    [19963643]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
кролик-зануда
Guest
yuri7811,

max(case when onlytime>'06:30:00.0000000' and onlytime<='16:00:00.0000000' then sorter else 0 end)
-min(case when onlytime>'06:30:00.0000000' and onlytime<='16:00:00.0000000' then sorter else 0 end)


только с границами разберитесть точнее, а то у вас теперь минута промежутка между сменами никуда не попадает
3 дек 16, 19:36    [19963709]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
yuri7811
Member

Откуда:
Сообщений: 19
кролик-зануда,


пробовал
(max(case when onlytime>'06:31:00.0000000' and onlytime<='16:00:00.0000000' then sorter else 0 end) - min(case when onlytime>'06:31:00.0000000' and onlytime<='16:00:00.0000000' then sorter else 0 end)) as I_смена
возвращает то же значение что и
max(case when onlytime>'06:31:00.0000000' and onlytime<='16:00:00.0000000' then sorter else 0 end)

пробовал отдельно
min(case when onlytime>'06:31:00.0000000' and onlytime<='16:00:00.0000000' then sorter else 0 end) as I_смена

возвращает 0 ?????
3 дек 16, 20:02    [19963826]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
кролик-зануда
Guest
yuri7811,

вы полный текст запроса приведите, и набор данных, если это не тот же, что и в первом сообщении
3 дек 16, 20:09    [19963851]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
кролик-зануда
Guest
стоп. осознал косяк.
уберите else 0 из скобок в обоих случаях
а на случай, когда за смену нет ни одного показания, оберните max-min в isnull
3 дек 16, 20:14    [19963857]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
yuri7811
Member

Откуда:
Сообщений: 19
кролик-зануда,

запрос такой:

select onlydate
,max(case when onlytime>'06:31:00.0000000' and onlytime<='16:00:00.0000000' then sorter else 0 end) - min(case when onlytime>'06:31:00.0000000' and onlytime<='16:00:00.0000000' then sorter else 0 end) as I_smena
,max(case when onlytime>'16:01:00.0000000' and onlytime<='23:59:59.0000000' then sorter else 0 end) - min(case when onlytime>'16:01:00.0000000' and onlytime<='23:59:00.000000' then sorter else 0 end) as II_smena
,max(case when onlytime>'00:00:01.0000000' and onlytime<='06:29:00.0000000' then sorter else 0 end) - min(case when onlytime>'00:00:01.0000000' and onlytime<='06:29:59.0000000' then sorter else 0 end) as III_smena
,sum(sorter) Total
from [dbo].[TotalCounters_Test1]
group by onlydate


а таблица с данными в начале поста
3 дек 16, 20:15    [19963861]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
кролик-зануда
Guest
yuri7811,

что-то у вас с данными не так.
если отсортировать данные по id, 23:59 почему-то везде отнесены к следующей дате
если показания должны монотонно возрастать, то непонятно, почему в 11 числе вдруг происходит скачок на -400

а в целом - полагаю, вам нужно что-то подобное
select dateadd(dd,case when OnlyTime<='06:30:00.0000000' then -1 else 0 end,OnlyDate)
      ,isnull(max(case when onlytime>'06:30:00.0000000' and onlytime<='16:00:00.0000000' then sorter  end) - min(case when onlytime>'06:30:00.0000000' and onlytime<='16:00:00.0000000' then sorter  end),0) as I_smena
      ,isnull(max(case when onlytime>'16:00:00.0000000' then sorter  end) - min(case when onlytime>'16:00:00.0000000' then sorter  end),0) as II_smena
      ,isnull(max(case when onlytime<='06:30:00.0000000' then sorter  end) - min(case when onlytime<='06:30:00.0000000' then sorter  end),0) as III_smena
      ,isnull(max(sorter)-min(sorter),0) Total
  from TotalCounters_Test1 
 group by dateadd(dd,case when OnlyTime<='06:30:00.0000000' then -1 else 0 end,OnlyDate) 
3 дек 16, 20:55    [19963942]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
with a
as(
select onlydate
,sorter,cast(onlydate as datetime) + cast(min(onlytime) as datetime) as t_date
from [dbo].[TotalCounters_Test1]
group by onlydate,sorter
)
select cast(t_date as date) as onlydate
,sum(case when cast(t_date as time) between '06:30:00.0000000' and '15:59:00.0000000' then dif else 0 end) I_smena 
,sum(case when cast(t_date as time) between '16:00:00.0000000' and '23:59:00.0000000' then dif else 0 end) II_smena
,sum(case when cast(t_date as time) between '00:00:00.0000000' and '06:29:00.0000000' then dif else 0 end) III_smena
,sum(dif) Total
from(
SELECT 
a.t_date
,min(a1.t_date) t
,a.sorter
,(select sorter from a where t_date = min(a1.t_date)) - a.sorter as dif
 FROM a
 join a a1 on  a.t_date<a1.t_date
 group by a.t_date,a.sorter
 ) b
 group by cast(t_date as date)
3 дек 16, 21:18    [19963988]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
yuri7811
Member

Откуда:
Сообщений: 19
3unknown,

классно работает (пока не разобрался как). а можно както сделать чтобы колонка 3 смена сместилась на одну строчку вниз и соответственно тотал считался адекватно.
спасибо огромное
3 дек 16, 21:59    [19964077]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
кролик-зануда
Guest
yuri7811
классно работает (пока не разобрался как).

вам, похоже, магия нужна.

и что для вас " на одну строчку вниз"?
если 3 смену надо относить к предыдущей календарной дате - то вы увидите этот результат в моем запросе.
3 дек 16, 22:27    [19964190]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить