SQL Server Fill 24 columns from time period

Ignition
1

At work I am having a production overview board which is showing the parts produced per hour since midnight. It is organized as a table for each line we are running.

1673×98 7.42 KB
As you can see from the image it uses different colors to represent wether the goal has been achieved or not. Also another 3 colors are used to display planned events such as Planned Downtime, Planned Maintenance work, Planned Changeover. The screen is being populated from an SQL DB and to display the planned events in colors another SQL table is used. Its structure is like

Depending on the event different integer is used to populate the columns from 00 to 23. E.g. if we have planned downtime the number will be 1, planned DT will be 2 and etc. and that will colour the specified cell in picture 1.

I would like to ask if there is way to get 2 dates e.g. start date & end date and populate the hour columns which are within the date/time period? In other words I could not find the SQL query that could let me do it.

2

Whilst (I think) it is possible to write a script to generate the hours by given date range I would go about this differently.

Have a column in your db called time and another event_id.

Then use SQL to group by time for the hour and event_id (called string in my example attached).