Home » SQL & PL/SQL » SQL & PL/SQL » Last Full 52 Weeks
Last Full 52 Weeks [message #329173] Tue, 24 June 2008 07:25 Go to next message
fmrock
Messages: 45
Registered: December 2006
Member
I have the following code to pull the data based on a date.

	AND PDATE between to_number(to_char(to_date('20070101','yyyymmdd'),'j')) and to_number(to_char(to_date('20071231','yyyymmdd'),'j'))


PDATE is a number (julian date) and indexed.

How would be the best way to pull the last full 52 weeks?

Thanks for all your help.
Re: Last Full 52 Weeks [message #329174 is a reply to message #329173] Tue, 24 June 2008 07:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Standard Disclaimer: Store dates as dates, not varchars, or numbers.

PDATE BETWEEN to_number(to_char((date,'J'))-(52*7) AND to_number(to_char((date,'J'))
Re: Last Full 52 Weeks [message #329176 is a reply to message #329174] Tue, 24 June 2008 07:43 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
Thanks JRowbottom,

I wish the column could be a date column, but its a third party system and we only have access to query it.

I am looking to have the query run for the last full 52 weeks, so if the query was run any time this week, it allways pulls the same date range.

For example, if it was run on 6/24 or 6/25 or 6/26 then ending date would be 6/21
Re: Last Full 52 Weeks [message #329179 is a reply to message #329176] Tue, 24 June 2008 07:47 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You want something like
PDATE BETWEEN to_number(to_char(trunc(date,'W'),'J')-(52*7) AND to_number(to_char(trunc(date,'W'),'J')

The 'W' format mask lets you truncate the date to the first day of the week.
This is NLS parameter specific.
Previous Topic: Merge two Queries (Sub Query)?
Next Topic: Continous LAG when null
Goto Forum:
  


Current Time: Sun Dec 11 08:30:23 CST 2016

Total time taken to generate the page: 0.09224 seconds