SQL help with time difference 2

[ridhirao22]

Using Oracle 11g

How could I write a sql to find # of orders between order date and shipped date fields in the below hrs block. Any help is appreciated.
Eg:
#orders between 0-12 hours.
#orders between 13-24 hours
#orders between 24-48 hours
#orders between 49-72 hours
#orders between 73-96 hours
#orders between 97-120 hours
#orders between >120 hours

Thank,
RR

 

[SantaMufasa]

RR,

Could you please post several "INSERT INTO..." of sample data for "ORDER_DATE" and "SHIPPED_DATE" so that we don't need to spend extra time "making up" some test data?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

Since I had to move on to another activity, I created the following sample data:

col order_date format a19
col shipped_date format a19
col y heading "Elapsed|Hours" format 9,999.9
select to_char(order_date,'yyyy-mm-dd hh24:mi:ss') order_date
       ,to_char(shipped_date,'yyyy-mm-dd hh24:mi:ss') shipped_date
       ,(shipped_date-order_date)*24 y
   from orders
/

                                         Elapsed
ORDER_DATE          SHIPPED_DATE           Hours
------------------- ------------------- --------
2011-06-10 03:27:10 2011-06-10 13:03:10      9.6
2011-06-09 05:51:10 2011-06-09 13:03:10      7.2
2011-06-09 03:27:10 2011-06-10 13:03:10     33.6
2011-06-07 05:51:10 2011-06-09 13:03:10     55.2
2011-05-11 13:03:10 2011-06-09 03:27:10    686.4
2011-04-20 13:03:10 2011-06-07 05:51:10  1,144.8

6 rows selected.

Here is code that does what you wanted:

select 'Between '||range_beg||' and '||range_end||' hrs.' x
      ,cnt
  from (select r.*,count(*) cnt
          from ranges r
              ,orders o
         where (shipped_date-order_date)*24 between range_beg+(1-(1/24/60/60)) and range_end
         group by range_beg,range_end)
 order by range_beg
/

Elapsed-time Ranges               CNT
-------------------------- ----------
Between 0 and 12 hrs.               2
Between 25 and 48 hrs.              1
Between 49 and 72 hrs.              1
Between 121 and 99999 hrs.          2

4 rows selected.

Let us know if this meets your needs.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

Sorry, RR,

I overlooked providing the RANGES table that looks like this:

select * from ranges;

 RANGE_BEG  RANGE_END
---------- ----------
         0         12
        13         24
        25         48
        49         72
        73         96
        97        120
       121      99999

7 rows selected.

If there are no ORDERS that have elapsed times to delivery that fall within a range, then that range does not display.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

Sorry...another correction: the code that reads:

...between range_beg+(1-(1/24/60/60)) and range_end...

should, instead, read:

...between range_beg and range_end+(1-(1/24/60/60))...

The part that reads ..."+(1-(1/24/60/60))" adds a day ["+1"] to the end of the range, then subtracts one second from that day ["-(1/24/60/60)"] resulting in the end of the range as "<end of range> at 11:59:59 p.m.".

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[ridhirao22]

Thank you so much for the quick response and Sorry for the delayed reply.

I don't have write access to create a range table.
Is there any other alternative for me to do this?

TIA,

 

[ridhirao22]

Thank you again Mufasa. and a star for you.
I hard coded and did a union.

 

[SantaMufasa]

Excellent tweak, RR, given your restriction against creating a table.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Ygor]

Shame that the WIDTH_BUCKET function doesn't quite fit the requirements. It's got such a great name!

SQL> SELECT decode(wb, 11, 'Over 120 hrs', 'Between ' || (wb - 1) * 12 || ' and ' || wb * 12 || ' hours.') rng,
  2         COUNT(0) cnt
  3    FROM (SELECT width_bucket((shipped_date - order_date) * 24, 0, 120, 10) wb FROM orders)
  4   GROUP BY wb
  5   ORDER BY wb
  6  /
 
RNG                                   CNT
------------------------------ ----------
Between 0 and 12 hours.                 2
Between 24 and 36 hours.                1
Between 48 and 60 hours.                1
Over 120 hrs                            2

 

[Ygor]

Oh. And you don't need a range table.

 

[SantaMufasa]

Clever, Ygor. Until you showed it here, I was not aware of Oracle's WIDTH_BUCKET function. Hava

!

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[ridhirao22]

Thank you, Ygor. I tried this new function sometime back and didn't get the same results as I need. So for this requirement I didn't try it again.

A Star for you too.

Question I used Union but I tried changing it to Case when statement rather do the same sql in union. I get almost same results but for greater than 120 hr I get difference, Why would that be? I couldn't figure it out.

Thanks!