Home » SQL & PL/SQL » SQL & PL/SQL » Juliandate Query + last full week
Juliandate Query + last full week [message #220890] Thu, 22 February 2007 11:23 Go to next message
fmrock
Messages: 45
Registered: December 2006
Member
One of my coworkers created a report that pulls from our oracle DB.

The report takes around 8hours to run and starts at 3am every monday.

The date field the report uses is a Juliandate format, and so Crystal when it pulls the last full week based on that date column, has to pull back all records that match and the once the result set is back, filter out the records.

I would like to create this in a query instead of having to pull back everything and then filter. This should speed up the report a great deal.

There is a column called "DATEUPDATED" how would i pull based on this column for the last full week?
Re: Juliandate Query + last full week [message #220899 is a reply to message #220890] Thu, 22 February 2007 11:59 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Please define what you mean by "last week."
Re: Juliandate Query + last full week [message #220903 is a reply to message #220890] Thu, 22 February 2007 12:20 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
Last sunday till saturday
For example last week if run on Monday would be

02/11/2007 to 02/17/2007
Re: Juliandate Query + last full week [message #221360 is a reply to message #220890] Mon, 26 February 2007 08:03 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
Would it be possible to figure out the startdate and end date, no matter what day we run the query this week?
Re: Juliandate Query + last full week [message #221369 is a reply to message #221360] Mon, 26 February 2007 08:20 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Can you provide us with:
  1. A simple create table script
  2. Sample data
  3. Expected output based on that sample data
Don't forget to enclose SQL statements in [code] and [/code] tags.

MHE
Re: Juliandate Query + last full week [message #221401 is a reply to message #220890] Mon, 26 February 2007 10:24 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
This system is a 3rd party vendor and we only have the ability to query the system, so I dont think i can get the SQL to build a create script.

Also, the tables that this report needs to use houses 10-15 million records.

I pulled some quick sample data

The first Number is julian date which is what is stored in the database, and the second was what I converted in a Crystal report to display to the end user.

DATEUPDATED - Date_Updated
2453873 - 5/17/2006
2453662 - 10/18/2005

What I am trying to acomplish is instead of pulling all databack to the report, I want to add date statements to the WHERE clause.

I have the dates hard coded in this statement

AND (to_char(to_date(tablename.DATEUPDATED,'J'),'yyyymmdd') between '20070211' and '20070217')



[Updated on: Mon, 26 February 2007 10:26]

Report message to a moderator

Re: Juliandate Query + last full week [message #221628 is a reply to message #220890] Tue, 27 February 2007 16:53 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
AND tablename.DATEUPDATED between next_day(trunc(sysdate)-13,'SUN') and next_day(trunc(sysdate),-6,'SAT')

Re: Juliandate Query + last full week [message #222067 is a reply to message #220890] Thu, 01 March 2007 10:36 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
Thanks Bill B.

What about the last full month. So for if the report is run in March, it allways brings back Feb.
Re: Juliandate Query + last full week [message #222075 is a reply to message #220890] Thu, 01 March 2007 11:39 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
The query will always find the previous Sunday through Saturday period. It doesn't matter what month it is run in.
Re: Juliandate Query + last full week [message #222254 is a reply to message #220890] Fri, 02 March 2007 09:14 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
Yes and thats great.

But something else, how could you pull up the last full month. So for example, if the query is run anytime in March, would allways pull februarys data.
Re: Juliandate Query + last full week [message #222256 is a reply to message #220890] Fri, 02 March 2007 09:19 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
AND tablename.DATEUPDATED between add_months(trunc(sysdate,'MONTH')-1) and TRUNC(SYSDATE,'MONTH') - 1/86400
Re: Juliandate Query + last full week [message #222265 is a reply to message #222256] Fri, 02 March 2007 10:14 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
Bill B wrote on Fri, 02 March 2007 10:19
AND tablename.DATEUPDATED between add_months(trunc(sysdate,'MONTH')-1) and TRUNC(SYSDATE,'MONTH') - 1/86400



I am getting invalid number of arguments on the add_months function.

Also why do you divide by 86400?
Re: Juliandate Query + last full week [message #222266 is a reply to message #222265] Fri, 02 March 2007 10:17 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Sorry missed a comma

AND tablename.DATEUPDATED between add_months(trunc(sysdate,'MONTH'),-1) and TRUNC(SYSDATE,'MONTH') - 1/86400


The minus sets the range from midnight of the first day of the month and the "1/86400" sets it to one second before midnight on the last day of the month.

Re: Juliandate Query + last full week [message #222268 is a reply to message #220890] Fri, 02 March 2007 10:26 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
Thank you so much. Works perfect and now my end user will not have to put in any paramaters for this report.

Re: Juliandate Query + last full week [message #407127 is a reply to message #222268] Mon, 08 June 2009 10:08 Go to previous message
PRODQ
Messages: 1
Registered: June 2009
Location: Banbury, Oxfordshire
Junior Member
Yes - thanks v much - have been looking for this for quite some time!
Previous Topic: Invalid state when running first time after compile, error with schema name, permissns proc vs user
Next Topic: executing a select on two tables and getting the updated only
Goto Forum:
  


Current Time: Wed Dec 07 18:30:20 CST 2016

Total time taken to generate the page: 0.24992 seconds