SQL Server Fill 24 columns from time period

Question

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. As you can seen 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.

Answer 1

Here's a SELECT - you can wrap an INSERT or UPDATE etc around it to populate your table. I'm sure there are other (maybe better ways) of doing this too :)

DECLARE @START DATETIME = '04:15:00';
DECLARE @END DATETIME = '13:50:00';

DECLARE @VALUE INT = 2;


WITH Numbers(N) AS
(
SELECT 0 N
UNION ALL
SELECT N+1 N FROM Numbers
WHERE N<23
)
SELECT * FROM (
    SELECT CONCAT('Hour',N) Period, CASE WHEN N BETWEEN DATEPART(HOUR, @START) AND DATEPART(HOUR, @END) THEN @VALUE ELSE 0 END VALUE FROM Numbers
    ) SRC
PIVOT 
(
    MAX(VALUE) FOR Period IN ([Hour0],[Hour1],[Hour2],[Hour3],[Hour4],[Hour5],[Hour6],[Hour7],[Hour8],[Hour9],[Hour10],[Hour11],[Hour12],[Hour13],[Hour14],[Hour15],[Hour16],[Hour17],[Hour18],[Hour19],[Hour20],[Hour21],[Hour22],[Hour23])
) Pvt


Hour0       Hour1       Hour2       Hour3       Hour4       Hour5       Hour6       Hour7       Hour8       Hour9       Hour10      Hour11      Hour12      Hour13      Hour14      Hour15      Hour16      Hour17      Hour18      Hour19      Hour20      Hour21      Hour22      Hour23
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
0           0           0           0           2           2           2           2           2           2           2           2           2           2           0           0           0           0           0           0           0           0           0           0

(1 row(s) affected)

EDIT: Hint on wrapping this with an insert (untested) See Combining INSERT INTO and WITH/CTE

WITH Numbers(N) AS
(
SELECT 0 N
UNION ALL
SELECT N+1 N FROM Numbers
WHERE N<23
)
INSERT INTO MyTable
(
  Hour0,
  Hour1,
   -- etc ...
)
SELECT * FROM (
    SELECT CONCAT('Hour',N) Period, CASE WHEN N BETWEEN DATEPART(HOUR, @START) AND DATEPART(HOUR, @END) THEN @VALUE ELSE 0 END VALUE FROM Numbers
) SRC
PIVOT 
(
    MAX(VALUE) FOR Period IN ([Hour0],[Hour1],[Hour2],[Hour3],[Hour4],[Hour5],[Hour6],[Hour7],[Hour8],[Hour9],[Hour10],[Hour11],[Hour12],[Hour13],[Hour14],[Hour15],[Hour16],[Hour17],[Hour18],[Hour19],[Hour20],[Hour21],[Hour22],[Hour23])
) Pvt

Regarding your comment on compatibility levels and PIVOT:

Is PIVOT supported with compatibility_level 80?