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
tookep
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_DATE(itl.dstamp,'DD/MON/YYYY HH24:MI'),
itl.client_id,
itl.No_Full_Picks
FROM
(SELECT TO_CHAR(itl.dstamp, 'DD/MON/YYYY HH24') || ':00' AS dstamp,
itl.client_id,
COUNT(NVL(itl.KEY,0)) AS No_Full_Picks
FROM INVENTORY_TRANSACTION itl, SKU_CONFIG sc
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
JRowbottom
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: simple sql question (Oracle 9.2)
Next Topic: ora-010002 fetch out of sequence.
Goto Forum:
  


Current Time: Mon Dec 05 18:48:40 CST 2016

Total time taken to generate the page: 0.05438 seconds