Home » SQL & PL/SQL » SQL & PL/SQL » Query - Period by Day? (Oracle 10)
Query - Period by Day? [message #285052] Mon, 03 December 2007 03:53 Go to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

I need some assitance in generating the following query. I've been asked to show Sales by time period by day, e.g.: -

8-9 9-10 10-11 11-12 12-13 13-14 14-15 15-16 16-17 17-18

01/11
02/11
03/11
04/11

Pulling out the Sales by day isn't a problem. What I'm not sure on is how to split it by the hour?

The data is held in two tables; Transacations & Trans_Products.

Transactions table: -

ID
Date (Accounting)
Trans_Date (Date & Time)
Trans Number
Trans_Value

Trans_Products

ID
Trans_ID
Product_ID
Qty
Value

Could someone please advise me on the best way to acheive the above?

Thank You & Regards

[Updated on: Mon, 03 December 2007 03:54]

Report message to a moderator

Re: Query - Period by Day? [message #285055 is a reply to message #285052] Mon, 03 December 2007 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> select sysdate, to_number(to_char(sysdate,'HH24')) hour from dual;
SYSDATE                   HOUR
------------------- ----------
03/12/2007 11:08:12         11

1 row selected.

Regards
Michel
Re: Query - Period by Day? [message #285057 is a reply to message #285052] Mon, 03 December 2007 04:12 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> select extract(year from hiredate) year, 
  2         count(decode(deptno,10,1)) dept10,
  3         count(decode(deptno,20,1)) dept20,
  4         count(decode(deptno,30,1)) dept30
  5  from emp
  6  group by extract(year from hiredate)
  7  order by 1
  8  /
      YEAR     DEPT10     DEPT20     DEPT30
---------- ---------- ---------- ----------
      1980          0          1          0
      1981          2          2          6
      1982          1          0          0
      1987          0          2          0

4 rows selected.

Regards
Michel
Previous Topic: To Call Procedure from another procedure
Next Topic: Packege
Goto Forum:
  


Current Time: Fri Feb 14 23:52:54 CST 2025