Home » SQL & PL/SQL » SQL & PL/SQL » Date Query (Oracle 10g)
Date Query [message #412554] |
Fri, 10 July 2009 00:10  |
rakeshramm
Messages: 175 Registered: September 2006 Location: Oracle4u.com
|
Senior Member |

|
|
CREATE TABLE PRODUCTIVITY
(
N_PRID NUMBER(10),
N_JOBID NUMBER(10) NOT NULL,
N_TYPE NUMBER(10) NOT NULL,
N_EMPID NUMBER(10) CONSTRAINT NN_PRODUCTIVITY_EMPID NOT NULL,
D_ENTRYDATE DATE,
D_SYSDAT DATE DEFAULT sysdate,
N_UNITS INTEGER DEFAULT 0
)
Insert into PRODUCTIVITY
(N_PRID, N_JOBID, N_TYPE, N_EMPID, D_ENTRYDATE,
D_SYSDAT, N_UNITS)
Values
(15, 301, 1, 281, TO_DATE('07/08/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/09/2009 18:02:50', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into PRODUCTIVITY
(N_PRID, N_JOBID, N_TYPE, N_EMPID, D_ENTRYDATE,
D_SYSDAT, N_UNITS)
Values
(16, 282, 1, 502, TO_DATE('07/09/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/09/2009 18:16:01', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into PRODUCTIVITY
(N_PRID, N_JOBID, N_TYPE, N_EMPID, D_ENTRYDATE,
D_SYSDAT, N_UNITS)
Values
(17, 282, 1, 502, TO_DATE('07/08/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/09/2009 18:16:11', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into PRODUCTIVITY
(N_PRID, N_JOBID, N_TYPE, N_EMPID, D_ENTRYDATE,
D_SYSDAT, N_UNITS)
Values
(18, 282, 1, 502, TO_DATE('07/07/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/09/2009 18:17:47', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into PRODUCTIVITY
(N_PRID, N_JOBID, N_TYPE, N_EMPID, D_ENTRYDATE,
D_SYSDAT, N_UNITS)
Values
(19, 282, 1, 502, TO_DATE('07/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/09/2009 18:18:16', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into PRODUCTIVITY
(N_PRID, N_JOBID, N_TYPE, N_EMPID, D_ENTRYDATE,
D_SYSDAT, N_UNITS)
Values
(20, 282, 1, 502, TO_DATE('07/02/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/09/2009 18:18:23', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into PRODUCTIVITY
(N_PRID, N_JOBID, N_TYPE, N_EMPID, D_ENTRYDATE,
D_SYSDAT, N_UNITS)
Values
(21, 282, 1, 502, TO_DATE('07/03/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/09/2009 18:18:29', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into PRODUCTIVITY
(N_PRID, N_JOBID, N_TYPE, N_EMPID, D_ENTRYDATE,
D_SYSDAT, N_UNITS)
Values
(23, 282, 1, 502, TO_DATE('06/30/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/09/2009 18:18:46', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into PRODUCTIVITY
(N_PRID, N_JOBID, N_TYPE, N_EMPID, D_ENTRYDATE,
D_SYSDAT, N_UNITS)
Values
(24, 282, 1, 502, TO_DATE('06/26/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/09/2009 18:18:53', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into PRODUCTIVITY
(N_PRID, N_JOBID, N_TYPE, N_EMPID, D_ENTRYDATE,
D_SYSDAT, N_UNITS)
Values
(25, 282, 1, 502, TO_DATE('06/25/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/09/2009 18:19:08', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into PRODUCTIVITY
(N_PRID, N_JOBID, N_TYPE, N_EMPID, D_ENTRYDATE,
D_SYSDAT, N_UNITS)
Values
(26, 282, 1, 502, TO_DATE('06/18/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/09/2009 18:19:18', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into PRODUCTIVITY
(N_PRID, N_JOBID, N_TYPE, N_EMPID, D_ENTRYDATE,
D_SYSDAT, N_UNITS)
Values
(27, 282, 4, 502, TO_DATE('07/09/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/09/2009 18:19:38', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into PRODUCTIVITY
(N_PRID, N_JOBID, N_TYPE, N_EMPID, D_ENTRYDATE,
D_SYSDAT, N_UNITS)
Values
(1, 61, 2, 281, TO_DATE('07/09/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/09/2009 15:19:40', 'MM/DD/YYYY HH24:MI:SS'), 0);
Insert into PRODUCTIVITY
(N_PRID, N_JOBID, N_TYPE, N_EMPID, D_ENTRYDATE,
D_SYSDAT, N_UNITS)
Values
(28, 282, 4, 502, TO_DATE('07/08/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/09/2009 18:26:46', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into PRODUCTIVITY
(N_PRID, N_JOBID, N_TYPE, N_EMPID, D_ENTRYDATE,
D_SYSDAT, N_UNITS)
Values
(6, 301, 1, 281, TO_DATE('07/09/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/09/2009 17:45:55', 'MM/DD/YYYY HH24:MI:SS'), 1);
COMMIT;
This is my query ,in this quey i am getting differnt dates in same week ,whats the problem in this .Please give your valuable information.
select distinct to_char(D_ENTRYDATE,'ww') week,next_day(D_ENTRYDATE-7,'SUN'),
next_day(D_ENTRYDATE,'SAT') ST,
sum(n_units) over (partition by to_char(D_ENTRYDATE,'ww') order by to_char(D_ENTRYDATE,'ww')) sm
from PRODUCTIVITY
ORDER BY WEEK
[Updated on: Fri, 10 July 2009 00:32] by Moderator Report message to a moderator
|
|
|
|
Re: Date Query [message #412559 is a reply to message #412556] |
Fri, 10 July 2009 00:23   |
rakeshramm
Messages: 175 Registered: September 2006 Location: Oracle4u.com
|
Senior Member |

|
|
SELECT DISTINCT To_char(d_entrydate,'ww') week,
Next_day(d_entrydate - 7,'SUN'),
Next_day(d_entrydate,'SAT') st,
Sum(n_units) OVER(PARTITION BY To_char(d_entrydate,'ww') ORDER BY To_char(d_entrydate,'ww')) sm
FROM productivity
ORDER BY week
Desired Result : Want to output the week ,start-date and end-date of the week and sum of n_units field of that week
[Updated on: Fri, 10 July 2009 00:32] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Re: Date Query [message #412579 is a reply to message #412569] |
Fri, 10 July 2009 01:12   |
ayush_anand
Messages: 417 Registered: November 2008
|
Senior Member |
|
|
SQL> select to_char(to_date('1/1/2009','mm/dd/yyyy'),'ww') week from dual
WE
--
01
SQL> select to_char(to_date('1/7/2009','mm/dd/yyyy'),'ww') week from dual
WE
--
01
It depends on how weeks are calculated in Oracle.(calculated from 1st Jan to 7th as the first week..not on basis of sundays to satudays ..SO please change your logic)
[Updated on: Fri, 10 July 2009 01:40] Report message to a moderator
|
|
|
Re: Date Query [message #412586 is a reply to message #412577] |
Fri, 10 July 2009 01:41  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | But here how can i use aggregate function because i only want group by week but want to select startdate and endate.
|
Just try it.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Tue Feb 11 12:05:14 CST 2025
|