I am trying to to compare number of transaction happened in current hour with yesterday same hour and last week same day same hour, below sql works perfectly if requirment is for the entire day but for hours it doesn't work.

SELECT
SUM(
    CASE
    WHEN request_time >= trunc(sysdate)
         AND request_time <= sysdate THEN
    perf_count
    ELSE
    NULL
    END
) AS total_today,
SUM(
    CASE
    WHEN request_time >= trunc(sysdate) - INTERVAL '1' DAY
         AND request_time < trunc(sysdate) THEN
    perf_count
    ELSE
    NULL
    END
) AS total_yesterday,
SUM(
    CASE
    WHEN request_time >= trunc(sysdate - 7)
         AND request_time < trunc(sysdate - 6) THEN
    perf_count
    ELSE
    NULL
    END
) AS total_last_week
FROM
perf_fact
WHERE
request_time >= add_months(
    trunc(
        sysdate, 'MM'
    ), - 1
)
AND request_time <= sysdate
;

Sample Data

Expected Output

CodePudding user response：

Here's how I'd do it:

SELECT SUM(CASE WHEN (TRUNC(SYSDATE,'HH')=TRUNC(request_time,'HH')) THEN perf_count ELSE 0 END) this_hour,
       SUM(CASE WHEN (TRUNC(SYSDATE-1,'HH')=TRUNC(request_time,'HH')) THEN perf_count ELSE 0 END) yesterday_same_hour,
       SUM(CASE WHEN (TRUNC(SYSDATE-7,'HH')=TRUNC(request_time,'HH')) THEN perf_count ELSE 0 END) last_week_same_hour
  FROM perf_fact

Or even more concisely with DECODE:

SELECT 
   SUM(DECODE(TRUNC(SYSDATE,'HH'),TRUNC(request_time,'HH'),perf_count,0)) this_hour,
   SUM(DECODE(TRUNC(SYSDATE-1,'HH'),TRUNC(request_time,'HH'),perf_count,0)) yesterday_same_hour,
   SUM(DECODE(TRUNC(SYSDATE-7,'HH'),TRUNC(request_time,'HH'),perf_count,0)) last_week_same_hour
FROM perf_fact