sqlsybasesqlanywhereisql

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


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?


Solution

  • 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