1

Topic: Help to write request

Kind time of days.
There is a table with indications of the counter of the machine on:
(06-30 16-00 1 change
(16-30 23-59 2 change
(00-01 06-30 3 change

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

[spoiler]

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);

[/spoiler]
I try to write request but not so it turns out:

SELECT TOP 7 *
FROM (SELECT OnlyDate, MAX (Sorter) - MIN (Sorter) AS I_smena
FROM dbo. TotalCounters_Test1 WHERE (OnlyTime BETWEEN ' 6:30:00 AM ' AND ' 4:00:00 PM ')
GROUP BY OnlyDate) T1
left join (SELECT OnlyDate, MAX (Sorter) - MIN (Sorter) AS II_smena
FROM dbo. TotalCounters_Test1 WHERE (OnlyTime BETWEEN ' 4:00:00 PM ' AND ' 11:59:59 PM ')
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 ' 0:00:01 AM ' AND ' 6:30:00 AM ')
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
*/

It is necessary to receive the table:
OnlyDate!! I_smena!! II_smena!! III_smena!! Total (I+II+III)
ps. III_smena it is the data for next days. Working days begin from 06.30 mornings and come to an end at 06.30 next day
In advance I thank for the help.

2

Re: Help to write request

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

Re: Help to write request

3unknown;
Thanks but that that the totals not correct turn out.
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
It is necessary for me a difference between counter indications on the end of change and the beginning of change and the third change is a night shift but next calendar days:

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

4

Re: Help to write request

5

Re: Help to write request

yuri7811;
0:00:00 AM at you does not get to one change, and 6:30 - in both and 16:00 - in two adjacent. It and is necessary? Or all the same it is necessary to come to the identical approach in all intersections of changes?
sum in request 3unknown it is necessary to replace on max - min, boundaries to place taking into account told above
Well and group by then it is necessary in style onlydate - case when onlytime <= ' 6:30:00 AM ' then 1 else 0 end

6

Re: Help to write request

If indications need to be considered at 6:30 at count of indications both for 1 change, and for 3 changes of the last days a variant with group by in such type not , it is necessary will or double these lines for each of groups, or to consider 3 change by separate request

7

Re: Help to write request

Rabbit-bore;
There is no really a change are not intersected
1 06.31-15.59
2 16.01-23.59
3 00.01-06.29
And it is possible more in detail as changeover sum on max-min will look and that produces an error

8

Re: Help to write request

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)

Only with boundaries  is more exact, and that at you now minute of an interval between changes does not get anywhere

9

Re: Help to write request

Rabbit-bore;
Tried
(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_
Returns the same value as
max (case when onlytime> ' 06:31:00.0000000 ' and onlytime <= ' 16:00:00.0000000 ' then sorter else 0 end)
Tried separately
min (case when onlytime> ' 06:31:00.0000000 ' and onlytime <= ' 16:00:00.0000000 ' then sorter else 0 end) as I_
Returns 0?????

10

Re: Help to write request

yuri7811;
You the full text of request result, and a data set, if it not the same, as in the first message

11

Re: Help to write request

Stop. Realized a jamb.
Remove else 0 of brackets in both cases
And on a case when for change there is no indication, envelop max-min in isnull

12

Re: Help to write request

Rabbit-bore;
Request such:
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
And the table of a dataful post in the beginning

13

Re: Help to write request

yuri7811;
Something at you dataful not so.
If to sort the data on id, 23:59 for some reason everywhere are carried to the following date
If indications have to increase monotonically it is not clear why in 11 number suddenly there is a saltus on-400
And as a whole - I suppose, you need something similar

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)

14

Re: Help to write request

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 an a1 on a.t_date <a1.t_date
group by a.t_date, a.sorter
) b
group by cast (t_date as date)

15

Re: Help to write request

3unknown;
In a fantastic way works (did not understand yet as). And it is possible  to make that the column 3 change drifted on one line downwards and accordingly  was considered adequately.
Thanks huge

16

Re: Help to write request

yuri7811 wrote:

in a fantastic way works (did not understand yet as).

Seemingly, the magic is necessary to you.
Also what for you "on one line downwards"?
If 3 change has to be carried to the previous calendar date - that you see this result in my request.