asked on

select transactions by hour - show 0 if no transactions in an hour

Hi Guys,

I need to select a count(*) of records from a transactions table ( let's call it transaction_table) by hour. It sounds pretty simple

SELECT COUNT(*)  no_of_tr,
  TO_CHAR(dstamp,'DD/MM/YYYY HH24')  dstamp
FROM transaction_table GROUP TO_CHAR(dstamp,'DD/MM/YYYY HH24')

Open in new window

no_of_tr dstamp
24        11/05/2019 00
1        11/05/2019 01
1        11/05/2019 02
118        11/05/2019 06
61        11/05/2019 07

What I want to achieve is to show no_of_tr = 0 when there were no transactions in a given hour, It needs to look like this

no_of_tr dstamp
24        11/05/2019 00
1        11/05/2019 01
1        11/05/2019 02
0        11/05/2019 03
0        11/05/2019 04
0        11/05/2019 05
118        11/05/2019 06
61        11/05/2019 07

Any idea how to do this ?

johnsone

This seems to work for me. Maybe there is a better solution that doesn't pass the data twice, but I cannot think of it right now.

WITH min_max_dates(max_dstamp, min_dstamp) 
     AS (SELECT Max(dstamp), 
                Min(dstamp) 
         FROM   transaction_table), 
     hour_list (dstamp) 
     AS (SELECT min_max_dates.min_dstamp + ( ( LEVEL - 1 ) / 24 ) 
         FROM   dual, 
                min_max_dates 
         CONNECT BY min_max_dates.min_dstamp + ( ( LEVEL - 1 ) / 24 ) <= 
                    min_max_dates.max_dstamp) 
SELECT Nvl(t.cnt, 0)                                no_of_tr, 
       To_char(hour_list.dstamp, 'DD/MM/YYYY HH24') dstamp 
FROM   hour_list 
       left outer join (SELECT Trunc(dstamp, 'hh24') dstamp, 
                               Count(1)              cnt 
                        FROM   transaction_table 
                        GROUP  BY Trunc(dstamp, 'hh24')) t 
                    ON hour_list.dstamp = t.dstamp 
ORDER  BY hour_list.dstamp;

Open in new window

If you have fixed dates as start and end times, that does make it a bit easier and definitely doesn't have to pass the data twice.

Mat Smith

ASKER

Thanks Johnsone,

Yes, there are kind of fixed dates, but this won't be easier ( I think).

There are 3 patterns I want this query to cope with, without users entering any start and end dates/time it should always select today's transactions and ...

When the query runs between
1. 6am and 2pm then it should cover 6,7,8,9,10,11,12,13
2. 2pm and 10pm then it should cover 14,15,16,17,18,19,20,21
3. when running the query between 10pm and 6am then 22,23,00,01,02,03,04,05

I'm just looking at your query... I get ORA-01878: specified field not found in datetime or interval...
Just trying to work out which part causes it.

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mat Smith

ASKER

Johnsone,

I am truly impressed.

It does work and you have saved me days of work.

much appreciated.

How come you did with so much ease ???

johnsone

Been doing this for a long time. Plus, I always like playing with dates. The CONNECT BY query using DUAL to generate a list is really the key. Once you know how to do that, you can apply it to tons of things.

Not sure how large your TRANSACTION_TABLE is, but be sure that there is an index on the DSTAMP column. It will certainly help the query out.