Is it possible to fine tune the following query that displays hourly count?

Database
Enthusiast

Is it possible to fine tune the following query that displays hourly count?

Hi all,

My apologies for asking someting like this.

I have formed a query that goes back exact 7 days (referring column CRT_DTTM) and take count(*) for each hour for that day.

As I have done it with very primitive knowledge from online resources, the query turned out to be bulky (and probably ugly).

So I am curious to know if there is any way to tune it using loop or something on same lines.

Another reason I am trying to do this is due to possibility that I might have to go back further to do the same task for past several weeks.

I the query I have done it for past 1 week only.

This is the query I am using:

WITH TEMP_TAB (w1_0,w1_1,w1_2,w1_3,w1_4,w1_5,w1_6,w1_7,w1_8,w1_9,w1_10,w1_11,w1_12,w1_13,w1_14,w1_15,w1_16,w1_17,w1_18,w1_19,w1_20,w1_21,w1_22,w1_23,w0_0) AS
(
SELECT
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY AS w1_0,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '1' HOUR as w1_1,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '2' HOUR as w1_2,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '3' HOUR as w1_3,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '4' HOUR as w1_4,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '5' HOUR as w1_5,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '6' HOUR as w1_6,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '7' HOUR as w1_7,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '8' HOUR as w1_8,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '9' HOUR as w1_9,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '10' HOUR as w1_10,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '11' HOUR as w1_11,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '12' HOUR as w1_12,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '13' HOUR as w1_13,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '14' HOUR as w1_14,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '15' HOUR as w1_15,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '16' HOUR as w1_16,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '17' HOUR as w1_17,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '18' HOUR as w1_18,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '19' HOUR as w1_19,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '20' HOUR as w1_20,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '21' HOUR as w1_21,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '22' HOUR as w1_22,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '7' DAY + INTERVAL '23' HOUR as w1_23,
CAST(CURRENT_DATE AS TIMESTAMP(3)) - INTERVAL '6' DAY AS w0_0
)
SELECT ST_NM,
SUM(CASE WHEN CRT_DTTM>=w1_0 AND CRT_DTTM<w1_1 THEN 1 ELSE 0 END) AS w1h1,
SUM(CASE WHEN CRT_DTTM>=w1_1 AND CRT_DTTM<w1_2 THEN 1 ELSE 0 END) AS w1h2,
SUM(CASE WHEN CRT_DTTM>=w1_2 AND CRT_DTTM<w1_3 THEN 1 ELSE 0 END) AS w1h3,
SUM(CASE WHEN CRT_DTTM>=w1_3 AND CRT_DTTM<w1_4 THEN 1 ELSE 0 END) AS w1h4,
SUM(CASE WHEN CRT_DTTM>=w1_4 AND CRT_DTTM<w1_5 THEN 1 ELSE 0 END) AS w1h5,
SUM(CASE WHEN CRT_DTTM>=w1_5 AND CRT_DTTM<w1_6 THEN 1 ELSE 0 END) AS w1h6,
SUM(CASE WHEN CRT_DTTM>=w1_6 AND CRT_DTTM<w1_7 THEN 1 ELSE 0 END) AS w1h7,
SUM(CASE WHEN CRT_DTTM>=w1_7 AND CRT_DTTM<w1_8 THEN 1 ELSE 0 END) AS w1h8,
SUM(CASE WHEN CRT_DTTM>=w1_8 AND CRT_DTTM<w1_9 THEN 1 ELSE 0 END) AS w1h9,
SUM(CASE WHEN CRT_DTTM>=w1_9 AND CRT_DTTM<w1_10 THEN 1 ELSE 0 END) AS w1h10,
SUM(CASE WHEN CRT_DTTM>=w1_10 AND CRT_DTTM<w1_11 THEN 1 ELSE 0 END) AS w1h11,
SUM(CASE WHEN CRT_DTTM>=w1_11 AND CRT_DTTM<w1_12 THEN 1 ELSE 0 END) AS w1h12,
SUM(CASE WHEN CRT_DTTM>=w1_12 AND CRT_DTTM<w1_13 THEN 1 ELSE 0 END) AS w1h13,
SUM(CASE WHEN CRT_DTTM>=w1_13 AND CRT_DTTM<w1_14 THEN 1 ELSE 0 END) AS w1h14,
SUM(CASE WHEN CRT_DTTM>=w1_14 AND CRT_DTTM<w1_15 THEN 1 ELSE 0 END) AS w1h15,
SUM(CASE WHEN CRT_DTTM>=w1_15 AND CRT_DTTM<w1_16 THEN 1 ELSE 0 END) AS w1h16,
SUM(CASE WHEN CRT_DTTM>=w1_16 AND CRT_DTTM<w1_17 THEN 1 ELSE 0 END) AS w1h17,
SUM(CASE WHEN CRT_DTTM>=w1_17 AND CRT_DTTM<w1_18 THEN 1 ELSE 0 END) AS w1h18,
SUM(CASE WHEN CRT_DTTM>=w1_18 AND CRT_DTTM<w1_19 THEN 1 ELSE 0 END) AS w1h19,
SUM(CASE WHEN CRT_DTTM>=w1_19 AND CRT_DTTM<w1_20 THEN 1 ELSE 0 END) AS w1h20,
SUM(CASE WHEN CRT_DTTM>=w1_20 AND CRT_DTTM<w1_21 THEN 1 ELSE 0 END) AS w1h21,
SUM(CASE WHEN CRT_DTTM>=w1_21 AND CRT_DTTM<w1_22 THEN 1 ELSE 0 END) AS w1h22,
SUM(CASE WHEN CRT_DTTM>=w1_22 AND CRT_DTTM<w1_23 THEN 1 ELSE 0 END) AS w1h23,
SUM(CASE WHEN CRT_DTTM>=w1_23 AND CRT_DTTM<w0_0 THEN 1 ELSE 0 END) AS w1h24
FROM XDW_V.INB_CALL_NRT_RPT INNER JOIN TEMP_TAB
ON 1=1
WHERE CRT_DTTM>=w1_0
AND ST_NM IS NOT NULL
GROUP BY ST_NM ORDER BY ST_NM;