Hourly Count for last 30 Days

You can try something like this, (not tested)

;WITH CTE AS (
           SELECT DATEPART(HOUR, PART_TST_TS) AS [Hour of Day] , SERL_NBR, DATEDIFF(day, PART_TST_TS, GETDATE()) [Day Day]
           FROM JCIMWhitby.dbo.REPL_TRH REPL_TRH
           WHERE (REPL_TRH.PART_LOT_DSC Like 'FRT Audit%') AND DATEDIFF(day, PART_TST_TS, GETDATE()) <= 30
           Union ALL
           
           SELECT Hour, NULL
           From @Hours
)
SELECT * FROM CTE
PIVOT
(
 COUNT(SERL_NBR) FOR [Day Day] in ([0],[1],[2],[3],[4]) -- ..... 0 - Today, 1 - Yesterday....
) PVT

---

Regards, RSingh