Home » SQL & PL/SQL » SQL & PL/SQL » Date Query (Oracle 10g)
Date Query [message #412554] Fri, 10 July 2009 00:10 Go to next message
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 #412556 is a reply to message #412554] Fri, 10 July 2009 00:13 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
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 

What is input & desired results?
Re: Date Query [message #412559 is a reply to message #412556] Fri, 10 July 2009 00:23 Go to previous messageGo to next message
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 #412563 is a reply to message #412559] Fri, 10 July 2009 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use analytic, use aggregate function.

Regards
Michel
Re: Date Query [message #412566 is a reply to message #412563] Fri, 10 July 2009 00:39 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Whether using analytic function will degrade performance.
Re: Date Query [message #412569 is a reply to message #412566] Fri, 10 July 2009 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, sure!

Regards
Michel
Re: Date Query [message #412577 is a reply to message #412569] Fri, 10 July 2009 01:11 Go to previous messageGo to next message
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') startdate,
next_day(D_ENTRYDATE,'SAT') endate,
sum(n_units) over (partition by to_char(D_ENTRYDATE,'ww') order by to_char(D_ENTRYDATE,'ww'))  sm  
from PRODUCTIVITY 
ORDER BY WEEK 


But here how can i use aggregate function because i only want group by week but want to select startdate and endate.
Re: Date Query [message #412579 is a reply to message #412569] Fri, 10 July 2009 01:12 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Number Format (merged 3)
Next Topic: Superscript data
Goto Forum:
  


Current Time: Tue Dec 06 06:45:38 CST 2016

Total time taken to generate the page: 0.09945 seconds