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?
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