Group by date + Dynamic time

Question

Group by date + Dynamic time

Navigation

#1 by (0 votes)

0

I have the following structure:

name  fecha               valor
b1    12/07/2017 19:45     200
b1    12/07/2017 19:50     220
b1    12/07/2017 19:55     221
...
b1    13/07/2017 00:59     425

It is a table where records are kept every 5 minutes for each name (b1, b2, b3 ... etc). I need the records to be grouped by hour range ie 08:30 (present day) - > 01:00 (next day) and generate a result like this:

b1                       12/07/2017             1066

Example: Day 12 contemplates me from 07/12/2017 08:30 am - > 07/13/2017 01:00 am

Keeping in mind that the dates are dynamic.

sql sql-server

asked by Daniel Espinoza 17.08.2017 в 20:14

source

1 answer

Help to validate form before sending Make php post to service rest c #

score 0 · Answer 1

Simulate the code in a temporary table just make the changes you need

Temporal table

create table #tabla (
Name varchar(50),
Fecha datetime,
Valor int)

Records

INSERT INTO #Tabla SELECT 'A', '2017-08-01 10:45', 500
INSERT INTO #Tabla SELECT 'A', '2017-08-01 11:45', 500
INSERT INTO #Tabla SELECT 'A', '2017-08-01 12:45', 180
INSERT INTO #Tabla SELECT 'A', '2017-08-01 13:45', 500
INSERT INTO #Tabla SELECT 'A', '2017-08-01 14:45', 500
INSERT INTO #Tabla SELECT 'A', '2017-08-01 15:45', 352
INSERT INTO #Tabla SELECT 'A', '2017-08-01 16:45', 500
INSERT INTO #Tabla SELECT 'A', '2017-08-01 17:45', 500
INSERT INTO #Tabla SELECT 'A', '2017-08-01 18:45', 352
INSERT INTO #Tabla SELECT 'A', '2017-08-01 19:45', 500
INSERT INTO #Tabla SELECT 'A', '2017-08-01 20:45', 352
INSERT INTO #Tabla SELECT 'A', '2017-08-01 21:45', 500
INSERT INTO #Tabla SELECT 'A', '2017-08-01 23:45', 100
INSERT INTO #Tabla SELECT 'A', '2017-08-02 00:45', 100
INSERT INTO #Tabla SELECT 'A', '2017-08-02 01:45', 450
INSERT INTO #Tabla SELECT 'A', '2017-08-02 02:45', 450
INSERT INTO #Tabla SELECT 'A', '2017-08-02 05:45', 300
INSERT INTO #Tabla SELECT 'A', '2017-08-02 13:45', 200
INSERT INTO #Tabla SELECT 'A', '2017-08-02 10:45', 352
INSERT INTO #Tabla SELECT 'A', '2017-08-02 11:45', 500
INSERT INTO #Tabla SELECT 'A', '2017-08-02 12:45', 352
INSERT INTO #Tabla SELECT 'A', '2017-08-02 13:45', 500
INSERT INTO #Tabla SELECT 'A', '2017-08-02 14:45', 500
INSERT INTO #Tabla SELECT 'A', '2017-08-02 15:45', 352
INSERT INTO #Tabla SELECT 'A', '2017-08-02 16:45', 500
INSERT INTO #Tabla SELECT 'A', '2017-08-02 17:45', 180
INSERT INTO #Tabla SELECT 'A', '2017-08-02 18:45', 352
INSERT INTO #Tabla SELECT 'A', '2017-08-02 19:45', 500
INSERT INTO #Tabla SELECT 'A', '2017-08-02 20:45', 352
INSERT INTO #Tabla SELECT 'A', '2017-08-02 21:45', 500
INSERT INTO #Tabla SELECT 'A', '2017-08-02 23:45', 352
INSERT INTO #Tabla SELECT 'A', '2017-08-03 00:45', 352
INSERT INTO #Tabla SELECT 'A', '2017-08-03 01:45', 352

Select

select Fecha2 = CASE WHEN DATEPART(HOUR, Fecha) = 0 THEN FORMAT(DATEADD(DAY, -1, Fecha), 'yyyy-MM-dd') ELSE FORMAT(Fecha, 'yyyy-MM-dd') END, SUM(Valor) from #Tabla GROUP BY CASE WHEN DATEPART(HOUR, Fecha) = 0 THEN FORMAT(DATEADD(DAY, -1, Fecha), 'yyyy-MM-dd') ELSE FORMAT(Fecha, 'yyyy-MM-dd') END

delete table

drop table #Tabla

I hope it works for you