I need to write such query:
SELECT CAST (date_time_column AS DATE) as 'Date',
AVG(CASE WHEN FORMAT(date_time_column, 'HH:mm') = '00:00' then my_values ELSE NULL end) as '00:00',
........
AVG(CASE WHEN FORMAT(date_time_column, 'HH:mm') = '23:59' then my_values ELSE NULL end) as '23:59'
FROM table
where date_time_column > '2021-08-12'
GROUP BY CAST (date_time_column AS DATE)
What is a way to avoid writing 1440 lines in a query?
Try the below method (Change the variables to match your table and field)
/*Generate all minutes in a day in a string variable*/
Declare @timeRange varchar(max)=null
declare @startdate Datetime='2021-08-12 00:00';
; WITH cte AS
(
SELECT 1 i, @startdate AS resultDate
UNION ALL
SELECT i + 1, DATEADD(minute, i, @startdate )
FROM cte
WHERE DATEADD(minute, i, @startdate ) < DateAdd(day,1,@startdate)
)
SELECT @timeRange=Coalesce(@timeRange +',' + '['+Format(resultDate,'HH:mm')+']','['+Format(resultDate,'HH:mm')+']') FROM cte
OPTION (MAXRECURSION 2000);
/* (Change These variables to match your table & fields */
declare @filterQuery varchar(300)=' where {date_time_column}>''2021-01-01''';
declare @dateTimeColumn varchar(30)='{date_time_column}';
declare @valueColumn varchar(30)='{value_column}';
declare @myTable varchar(20)='{my_table}';
/*Generate Pivot Query */
DECLARE @query AS NVARCHAR(MAX);
set @query= '
SELECT *
FROM (SELECT Cast('+ @dateTimeColumn +' as Date) Resultdate,
FORMAT(' + @dateTimeColumn + ', ''HH:mm'') DateMinute,
' + @valueColumn + ' FROM ' + @myTable + ' ' + @filterQuery +'
) FormattedData
PIVOT( AVG('+ @valueColumn + ')
FOR DateMinute IN ('+ @timeRange +')
) AS pivotTable
';
/*Execute Generated Query*/
execute(@query)
What you want to do is to return your data in rows, not columns. That is a row for every minute rather than a column for every minute.
Try it something like this:
WITH
cteNums AS
(
Select TOP (1440) ROW_NUMBER() OVER(order by (Select Null)) - 1 as num
From sys.all_columns
Order By num
)
, cteMinutes AS
(
Select FORMAT(CAST(num/cast(1440.0 as real) as DATETIME), N'HH:mm') as Minutes
From cteNums
)
select CAST(t.date_time_column AS DATE) as 'Date',
m.Minutes,
AVG(CASE WHEN FORMAT(t.date_time_column, 'HH:mm') = m.Minute then t.my_values ELSE NULL end) as AvgValues
FROM cteMinutes m
LEFT JOIN [table] t ON m.Minutes = FORMAT(t.date_time_column, 'HH:mm')
where t.date_time_column > '2021-08-12'
GROUP BY CAST(t.date_time_column AS DATE), m.Minutes
ORDER BY CAST(t.date_time_column AS DATE), m.Minutes
;
I cannot, of course, test this as no test data or table definitions were provided.
