SQL Query required ot get records count on hourly basis-CodePudding

I am trying to get records from 29th April,2022 on hourly basis from Oracle DB, however with the below query I am getting records count older than 29th April as well(all previous records count as well). Can you help fine tune the query?

SELECT DISTINCT
    COUNT(*),
    STATUS,
    TO_CHAR(LOAD_DATE,'DD-MON-YY HH24')
FROM
    TARGET_HIST
WHERE
    STATUS = 'A'
    AND TO_CHAR(LOAD_DATE, 'DD-MON-YY HH24:MI:SS') > '29-APR-22 00:00:00'
GROUP BY
    STATUS,
    TO_CHAR(LOAD_DATE,'DD-MON-YY HH24')
ORDER BY
    STATUS,
    TO_CHAR(LOAD_DATE,'DD-MON-YY HH24');

CodePudding user response：

Try this. Since you didn't provide any sample data I didn't test it for you


select trunc(load_date,'HH') "HOUR", count(*)
  from target_hist
where status='A' AND
load_date between to_date('29/04/2022','DD/MM/YYYY') and to_date('29/04/2022 23:59:59','DD/MM/YYYY HH24:MI:SS');
Group by trunc(load_date,'HH')
Order by trunc(load_date,'HH')

CodePudding user response：

Two problem with the same reason:

In your WHERE clause you look for rows after '29-APR-22 00:00:00', but you get rows before that.
In your ORDER BY clause you get the dates sorted in a mangled order.

This is because you have converted the datetimes to strings where '29-APR-22' comes after '01-MAY-22', but before '30-JAN-22', because '2' comes after '1' and before '3'.

If you want to sort and compare datetimes, then use datetimes. You can truncate them down to the hour with TRUNC(load_date, 'hh').

select
  trunc(load_date, 'hh') as load_hour,
  status,
  count(*)
from target_hist
where status = 'A'
and load_date >= date '2022-04-29'
group by trunc(load_date, 'hh'), status
order by trunc(load_date, 'hh'), status;

Leave it to your app to display the datetime in the format the user wants to see it. If you need a particular format, e.g. for exporting the data into a file, you can apply TO_CHAR on the truncated datetime TO_CHAR(trunc(load_date, 'hh'),'DD-MON-YY HH24') in the select clause (and only there).

Please note that I have removed DISTINCT from the query, because there are no duplicates to remove. And I am using a date literal in the WHERE clause. And >=in order to include midnight.

This query considers all days since April 29. If you want this day only, then add and load_date < date '2022-04-30'.