Home » SQL & PL/SQL » SQL & PL/SQL » Creating a Total per hour
Creating a Total per hour [message #202399] Thu, 09 November 2006 09:46 Go to next message
Messages: 12
Registered: July 2005
Location: Ipswich
Junior Member
What I am trying to do is to write a select statement which will display for every hour in the day a total number of shipments (even if the total is zero). My select statement works if something has been shipped in that hour (I am grouping by to_date(dstamp,'DD/MON/YYYY HH24'). It does not work if nothing has been shipped as I have nothing to group by. I think what I need to do is to create an outer join to a table of dates and times i.e 09/NOV/2006 00
09/NOV/2006 01 etc.

Select Statement:

(SELECT TO_CHAR(itl.dstamp, 'DD/MON/YYYY HH24') || ':00' AS dstamp,
COUNT(NVL(itl.KEY,0)) AS No_Full_Picks
WHERE itl.site_id='COR01'
AND itl.owner_id='AOWNER'
AND itl.client_id='AClient'
AND itl.container_id IS NULL
AND itl.pallet_id IS NULL
AND itl.v_config_id=sc.config_id
AND itl.update_qty = NVL(sc.ratio_1_to_2,0)
AND itl.code = 'Shipment'
AND (itl.dstamp BETWEEN TO_DATE('09/AUG/2006'||'000000','DD/MON/YYYYHH24MISS')
AND TO_DATE('09/NOV/2006'||'235959','DD/MON/YYYYHH24MISS'))
GROUP BY TO_CHAR(itl.dstamp, 'DD/MON/YYYY HH24'), itl.client_id

Thanks in advance for your help.
Re: Creating a Total per hour [message #202401 is a reply to message #202399] Thu, 09 November 2006 09:53 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need either:
1) Plsql
2) A table of dates/times
3) A row generator, such as
select hour,day
from  (select level hour from dual connect by level <= 24)
     ,(select level day from dual connect by level <= 31)
Previous Topic: SQL Loader
Next Topic: Converting Date of Birth to Age
Goto Forum:

Current Time: Wed Aug 23 14:34:36 CDT 2017

Total time taken to generate the page: 0.01586 seconds