The 2020-12-28 23:59:59 5
The 2020-12-29 00:29:59 20
The 100 06:29:59 2020-12-29
The 200 06:59:59 2020-12-29
The 260 07:29:59 2020-12-29
.
The 360 09:29:59 2020-12-29
.
The 560 14:29:59 2020-12-29
.
The 760 18:59:59 2020-12-29
.
The 1000 23:59:59 2020-12-29
Data, the data is increasing, every half an hour to a record, the above is only 1 day record
Now every month to check every 00:00 to 7:30pm, 7:30-9:30, 9:30 - passion, passion - at 19:00 at 19:00 - during the 00:00 cumulants, namely
00:00 to 7:30pm at 7:30-9:30 9:30 - passion comes at 19:00 at 19:00-00:00
The 2020-12-01... . . . .
.
The 2020-12-28... . . . .
The 240 255 100 200 200 2020-12-29
Could you tell me how to achieve with a SQL statement, thank you
CodePudding user response:
The CREATE TABLE # t
(
A DATETIME,
INT b
)
INSERT INTO # T VALUES (' 2020-12-28 23:59:59, 5)
INSERT INTO # T VALUES (' 2020-12-29 00:29:59, 20)
INSERT INTO # T VALUES (' 2020-12-29 06:29:59, 100)
INSERT INTO # T VALUES (' 2020-12-29 06:59:59, 200)
INSERT INTO # T VALUES (' 2020-12-29 07:29:59, 260)
INSERT INTO # T VALUES (' 2020-12-29 09:29:59, 360)
INSERT INTO # T VALUES (' 2020-12-29 14:29:59, 560)
INSERT INTO # T VALUES (' 2020-12-29 18:59:59, 760)
INSERT INTO # T VALUES (' 2020-12-29 23:59:59, 1000)
- sql2012
The SELECT a1,
The SUM (CASE WHEN a2 & gt; AND a2='00:00' & lt; THEN b ELSE 0 END 07:30) AS '00:00-07:30,
The SUM (CASE WHEN a2 & gt; AND a2='07:30' & lt; THEN b ELSE 0 END 09:30) AS '07:30-09:30,
The SUM (CASE WHEN a2 & gt; AND a2='09:30' & lt; 'passion' THEN b ELSE 0 END) AS '09:30 - passion,
The SUM (CASE WHEN a2 & gt;='passion' AND a2 & lt; THEN b ELSE 0 END at 19:00) AS 'passion - at 19:00,
The SUM (CASE WHEN a2 & gt; THEN b ELSE 0='number' END) AS 'number - 00:00'
The FROM
(
SELECT the CONVERT (VARCHAR (10), a, 121) AS a1, CONVERT (VARCHAR (5), a, 108) AS a2, b - ISNULL (LAG OVER the ORDER BY (a), (b) (0) AS the FROM # t b
) a
GROUP BY a1
- sql2008
;
WITH ct
AS
(
SELECT *, ROW_NUMBER () OVER the ORDER BY (a) as id FROM # t
)
The SELECT a1,
The SUM (CASE WHEN a2 & gt; AND a2='00:00' & lt; THEN b ELSE 0 END 07:30) AS '00:00-07:30,
The SUM (CASE WHEN a2 & gt; AND a2='07:30' & lt; THEN b ELSE 0 END 09:30) AS '07:30-09:30,
The SUM (CASE WHEN a2 & gt; AND a2='09:30' & lt; 'passion' THEN b ELSE 0 END) AS '09:30 - passion,
The SUM (CASE WHEN a2 & gt;='passion' AND a2 & lt; THEN b ELSE 0 END at 19:00) AS 'passion - at 19:00,
The SUM (CASE WHEN a2 & gt; THEN b ELSE 0='number' END) AS 'number - 00:00'
The FROM
(
SELECT the CONVERT (VARCHAR (10), a.a, 121) AS a1, CONVERT (VARCHAR (5), a.a, 108) AS a2, a. - ISNULL (b.b, 0) AS b
The FROM ct ON b.i d=Anderson, a LEFT JOIN ct b d - 1
) a
GROUP BY a1
DROP TABLE # t
CodePudding user response:
The CREATE TABLE # A (
DTIME DATETIME,
DATA INT
)
# INSERT INTO A VALUES (' 2020-12-28 23:59:59, 5)
# INSERT INTO A VALUES (' 2020-12-29 00:29:59, 20)
# INSERT INTO A VALUES (' 2020-12-29 06:29:59, 100)
# INSERT INTO A VALUES (' 2020-12-29 06:59:59, 200)
# INSERT INTO A VALUES (' 2020-12-29 07:29:59, 260)
# INSERT INTO A VALUES (' 2020-12-29 09:29:59, 360)
# INSERT INTO A VALUES (' 2020-12-29 14:29:59, 560)
# INSERT INTO A VALUES (' 2020-12-29 18:59:59, 760)
# INSERT INTO A VALUES (' 2020-12-29 23:59:59, 1000)
SELECT the CONVERT (DATE, a. d. TIME, 21) [DATETIME],
- make a copy of the five times
ISNULL (((SELECT TOP B.D ATA FROM # 1 A, B
WHERE B.D TIMEThe ORDER BY B.D TIME DESC)
- (SELECT TOP B.D ATA FROM # 1 A, B
WHERE B.D TIMEThe ORDER BY B.D TIME DESC)), 0) '0:00-7:30'
-
The FROM # aa
GROUP BY the CONVERT (DATE, a. d. TIME, 21)
DROP TABLE # A
This seem less... Look at the trouble
CodePudding user response:
1 # is OK, another way of thinking:Use the pivot filter and ranks, will be a reduction of the former data
DECLARE @ t TABLE (dt) DATETIME NOT NULL, val INT the NOT NULL)
INSERT INTO @ t (dt, val) VALUES (' 2020-12-28 23:59:59, 5)
, (the 2020-12-29 00:29:59, 20), (' 06:29:59 2020-12-29 '100)
, (' 06:59:59 2020-12-29 ', 200), (260) '07:29:59 2020-12-29',
, (' 09:29:59 2020-12-29 ', 360), (560) '14:29:59 2020-12-29',
, (' 18:59:59 2020-12-29 ', 760), (1000) 'the 2020-12-29 23:59:59,
The SELECT conviction yourself ate, COALESCE (p. [07:29] - LAG ([23:59], 1, 0) OVER (ORDER BY conviction yourself ate), 0) AS [00:00-07:30],
ISNULL (p. [09:29], 0) - ISNULL (p. [07:29], 0) AS [07:30-09:30],
ISNULL (p. [land], 0) - ISNULL (p. [09:29], 0) AS [09:30 - most],
ISNULL (p. [18:59], 0) - ISNULL (p. [land], 0) AS [at 19:00 passion -],
ISNULL (p. [23:59], 0) - ISNULL (p. [18:59], 0) AS [at 19:00-00:00)
The FROM (
SELECT the CAST (dt) AS the DATE AS of [DATE], CONVERT (VARCHAR (5), dt, 108) AS [Time], val FROM @ t) t
nullnullnull