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

I am getting the records from 29th April till SYSDATEbut not in order ASC or DESC ,however it is jumbled like 30th first, then 29th and then 2nd may. I need date in order , 29, 30, 1st, 2nd.

select distinct
  count(*),
  STATUS,
  TO_CHAR(LOAD_DATE,'DD-MON-YY HH24') AS LOAD_DATE
from TARGET_HIST
where
  STATUS='A'
  and
  LOAD_DATE > TO_DATE('29-APR-22 00:00:00','DD-MON-YY HH24:MI:SS')
GROUP BY
  STATUS,
  LOAD_DATE
ORDER BY
  LOAD_DATE,STATUS;

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');

CodePudding user response：

Your problem is that the result rows don't get sorted in the order you want. This is because you are sorting strings where '29-APR-22 10' comes after '01-MAY-22 10', because '2' comes after '0'.

If you want to sort 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.