Sum values of each element by hour from date to date in SQL

Question

I was searching a lot, but I could not find the answer. Maybe someone can give me some hints: I have a table that contains following columns and data (just an example):

+-------+------------------+----+--------+--------+
| Type  |    InsertDate    | ID | Value1 | Value2 |
+-------+------------------+----+--------+--------+
|  Data | 2019-04-29 14:30 | 01 |    2   |    1   |
|  Info | 2019-04-29 14:30 | 01 |    3   |    3   |
|  Data | 2019-04-29 14:42 | 01 |    5   |    5   |
|  Data | 2019-04-29 14:42 | 02 |    8   |    5   |
|  Info | 2019-04-28 09:30 | 02 |    1   |    7   |
|  Data | 2019-04-28 12:30 | 02 |    8   |    0   |
|  Data | 2019-04-28 12:10 | 02 |    3   |    1   |
+-------+------------------+----+--------+--------+

Now what I want to achive is to sum Value1 hourly for each ID where Type is Data in the configured time. I.e. from 28.04 to 29.04 I would like to have an output like:

+----+------------------+------------------+
| ID | 12:00 28-04-2019 | 14:00 29-04-2019 | 
+----+------------------+------------------+
| 01 |        0         |        7         |
| 02 |       11         |        8         |
+----+------------------+------------------+

I found a lot of exaples how to sum Value1 hourly for one ID, but I need to have an output for multiple IDs.

What I have right now is the query below:

DECLARE @cols NVARCHAR(20000)

SELECT 
    ID,   
    DATEADD(hour, DATEDIFF(hour, '00:00:00', InsertDate), '00:00:00') as    TruncatedToHour, 
    sum(Value1) as data 
INTO t3
FROM table1 
    WHERE Type = 'Data' AND InsertDate > '2019-04-28 00:00'
GROUP BY 
    ID,
    DATEADD(hour, DATEDIFF(hour, '00:00:00', InsertDate), '00:00:00')

SELECT @cols = LIST(DISTINCT cast(TruncatedToHour as nvarchar(1000)), ', ') FROM t3

This query returns all needed data (but not properly formated) and is dynamiclly creating column names for the needed table structure (@cols). Now I need to create needed table structure and fill it with proper data. Anyone can help?

Not sure what the logic is for the time values in your columns and I think you should have 12 not 11 for ID 02. First you just group everything by ID and date. Then a dynamic cross tab or dynamic pivot to get the columns. — Sean Lange, May 02 '19 at 15:26
@SeanLange I've done the first part with grouping everything by ID and date (I've edited question with my query). Now I'm struggling with second part - dynamic table. Can you give me some more hints how to do this in Sybase? — KonradR, May 06 '19 at 11:22
@SeanLange I found the solution on my own and posted it as answer. — KonradR, May 07 '19 at 07:58

KonradR · Accepted Answer · 2019-05-07T11:02:59.663

Ok, I find out the solution which works for me. I'm definately not the Sybase expert, but I want to share this solution with the community. Maybe this will help someone or some expert will improve it better.

My solution (I added comments to explain what the code is doing):

DECLARE @delimiter char(1), @quotechar char(1), @pattern varchar(500), @piece varchar(500), @pos int, @datefrom TEXT
DECLARE @cols TEXT
DECLARE @query TEXT

SET @datefrom = '2019-04-26 00:00' --//---> Define the 'date from' here
SET @quotechar = CHAR (39)
SET @delimiter = ','

--//Group everything by id and date and store it to temp table #t3
SELECT 
    ID,   
    DATEADD(minute, -DATEPART(minute, InsertDate), InsertDate) as TruncatedToHour, 
    sum(Value1) as data 
INTO #t3
FROM table1 
    WHERE Type = 'Data' AND InsertDate >= @datefrom
GROUP BY 
    ID,
    DATEADD(minute, -DATEPART(minute, InsertDate), InsertDate)

--//Select distinct the dates and hours from #t3 to create columns for the result table
--//and return them as delimited list
SELECT @cols = LIST(DISTINCT cast(TruncatedToHour as TEXT), ',') FROM #t3

--//Prepare the query which will create the result table
SET @pattern = '%' + @delimiter + '%'
SET @pos =  patindex(@pattern , @cols)
SELECT @query = 'SELECT ID, max (case when TruncatedToHour = ' + @quotechar
--//Loop through all column names from @cols and add them to the query
WHILE @pos <> 0
BEGIN
 SET @piece = LEFT(@cols, @pos - 1)
 --// In @piece we have now a single column name
 SELECT @query = @query + @piece + @quotechar + ' then data else null end) ' +@quotechar +@piece + @quotechar + ', max (case when TruncatedToHour = ' + @quotechar
 SET @cols = stuff(@cols, 1, @pos, '')
 SET @pos =  patindex(@pattern , @cols)
END

SELECT @query = @query + ' INTO #t4 FROM #t3 GROUP BY ID ORDER BY ID'
SELECT @query = REPLACE(@query, ', max (case when TruncatedToHour = ' + @quotechar + ' ', ' ')

--//Execute the @query and collect the data from #t4
EXECUTE(@query)
SELECT * FROM #t4 ORDER BY ID

Sum values of each element by hour from date to date in SQL

1 Answers1