Is it possible to improve Terada query to get min, max and avg from result?

Database
Enthusiast

Is it possible to improve Terada query to get min, max and avg from result?

I have composed a Teradata (V 14.10.3.09) query with help from the forum and modified it a bit to get count(*) of past six weeks, for present hour and same day of week.

For eaxample if current_timestamp is 2015-08-25 08:21:43.210000+00:00

Then what I am fetching is count(*) when table_column CRT_DTTM has value between 2015-08-25 08:00:00.000000 and 2015-08-25 08:21:43.210000+00:00

And count(*) when table_column CRT_DTTM has value between

  1. 2015-08-18 08:00:00.000000 and 2015-08-18 09:00:00.000000
  2. 2015-08-11 08:00:00.000000 and 2015-08-11 09:00:00.000000
  3. 2015-08-04 08:00:00.000000 and 2015-08-04 09:00:00.000000
  4. 2015-07-28 08:00:00.000000 and 2015-07-28 09:00:00.000000
  5. 2015-07-21 08:00:00.000000 and 2015-07-21 09:00:00.000000
  6. 2015-07-14 08:00:00.000000 and 2015-07-14 09:00:00.000000

Query I am using is:

WITH TEMP_TAB (w0s,w0e,w1s,w1e,w2s,w2e,w3s,w3e,w4s,w4e,w5s,w5e,w6s,w6e) AS
(
SELECT
TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24') AS w0s
,CURRENT_TIMESTAMP AS w0e
,(TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24')) - INTERVAL '7' DAY AS w1s
,(TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24')) - INTERVAL '7' DAY + INTERVAL '60' MINUTE AS w1e
,(TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24')) - INTERVAL '14' DAY AS w2s
,(TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24')) - INTERVAL '14' DAY + INTERVAL '60' MINUTE AS w2e
,(TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24')) - INTERVAL '21' DAY AS w3s
,(TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24')) - INTERVAL '21' DAY + INTERVAL '60' MINUTE AS w3e
,(TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24')) - INTERVAL '28' DAY AS w4s
,(TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24')) - INTERVAL '28' DAY + INTERVAL '60' MINUTE AS w4e
,(TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24')) - INTERVAL '35' DAY AS w5s
,(TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24')) - INTERVAL '35' DAY + INTERVAL '60' MINUTE AS w5e
,(TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24')) - INTERVAL '42' DAY AS w6s
,(TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24')) - INTERVAL '42' DAY + INTERVAL '60' MINUTE AS w6e
)
SELECT ST_NM
,SUM(CASE WHEN CRT_DTTM>=w0s AND CRT_DTTM<w0e THEN 1 ELSE 0 END) AS This_hr
,SUM(CASE WHEN CRT_DTTM>=w1s AND CRT_DTTM<w1e THEN 1 ELSE 0 END) AS Calls_W1
,SUM(CASE WHEN CRT_DTTM>=w2s AND CRT_DTTM<w2e THEN 1 ELSE 0 END) AS Calls_W2
,SUM(CASE WHEN CRT_DTTM>=w3s AND CRT_DTTM<w3e THEN 1 ELSE 0 END) AS Calls_W3
,SUM(CASE WHEN CRT_DTTM>=w4s AND CRT_DTTM<w4e THEN 1 ELSE 0 END) AS Calls_W4
,SUM(CASE WHEN CRT_DTTM>=w5s AND CRT_DTTM<w5e THEN 1 ELSE 0 END) AS Calls_W5
,SUM(CASE WHEN CRT_DTTM>=w6s AND CRT_DTTM<w6e THEN 1 ELSE 0 END) AS Calls_W6
FROM XDW_V.INB_CALL_NRT_RPT INNER JOIN TEMP_TAB
ON 1=1
WHERE CRT_DTTM>=w6s
AND ST_NM IS NOT NULL
GROUP BY ST_NM ORDER BY ST_NM;

Now what I am trying to add is that, out of the 6 counts I have got for last six weeks, skip min and max values and take the average of remaining 4 counts.

Is that possible by upgrading/modifying this query or will I have to write an entirely new query? If someone can help me with the query or give some tips then it'll be of great help.