Home » SQL & PL/SQL » SQL & PL/SQL » Returning the weeks involved by going back from sydate
Returning the weeks involved by going back from sydate [message #207882] Thu, 07 December 2006 04:21 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi there,

I have a table called PRTIMEPERIOD that lists all of our weekly timeentry periods with PRSTART (start date of period - Saturday) and PRFINISH (end date of period - Friday)

I can return the period start of the current week by using:

SELECT PRTIMEPERIOD.PRSTART
FROM 
NIKU.PRTIMEPERIOD
WHERE
sysdate >= PRTIMEPERIOD.PRSTART
AND sysdate <= PRTIMEPERIOD.PRFINISH


what I want to be able to do is list the periods that are contained in the past X number of weeks (X being a parameter I would later use in a report)

so If I wanted to go back 4 weeks from today
it would list the PRSTART values for the previous 4 weeks.

can anyone show me how to do this?

thanks,
Matt


Re: Returning the weeks involved by going back from sydate [message #207888 is a reply to message #207882] Thu, 07 December 2006 04:31 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Try using
trunc(sysdate) - 4 * 7 >= PRTIMEPERIOD.PRSTART

By
Vamsi
Re: Returning the weeks involved by going back from sydate [message #207900 is a reply to message #207882] Thu, 07 December 2006 04:57 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

this returns no results.
I'm thinking its because it the query is looking at the PRFINSH AND the PRSTART being the same record.

Not sure how I can seperate this to ask for the period starting from the truns(sysdate) -4*7
and then the period ending in the sysdate <= PRFINISH


any other ideas?

Re: Returning the weeks involved by going back from sydate [message #207903 is a reply to message #207900] Thu, 07 December 2006 05:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Surely you should also have
trunc(sysdate) - 4*7 <= PRFINISH
Re: Returning the weeks involved by going back from sydate [message #207907 is a reply to message #207882] Thu, 07 December 2006 05:22 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

no, because that would then only return the period that is 4 weeks ago.

here's an example of the table values:

PRSTART PRFINISH
11/11/06 17/11/06
18/11/06 24/11/06
25/11/06 01/12/06
02/12/06 08/12/06

as today is the 7th, the current weekly period is the bottom one.
If I want to return the previous 4 weeks' periods, then I want the query to return the values in the example above.
the "sysdate -4*7" will put me within the week with a PRSTART of 11/11/06
but the PRFINISH needs to be THIS week.


Re: Returning the weeks involved by going back from sydate [message #207909 is a reply to message #207907] Thu, 07 December 2006 05:44 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
If you want all the periods then try ONLY
trunc(sysdate) - 4*7 <= PRFINISH

Don't use
trunc(sysdate) - 4 * 7 >= PRTIMEPERIOD.PRSTART


By
Vamsi

[Updated on: Thu, 07 December 2006 05:45]

Report message to a moderator

Re: Returning the weeks involved by going back from sydate [message #207914 is a reply to message #207882] Thu, 07 December 2006 05:55 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

thanks, but doing this will then return the period that is 4 weeks ago and all previous periods.

the PRFINISH must be the one that is for this week.
so if I look at the PRFINISH that is greater than today (7th December) I will get the 8th - according to my examples below.

I also want all periods going back in time until the PRSTART is 4 weeks away (or however many weeks I require)



Re: Returning the weeks involved by going back from sydate [message #207917 is a reply to message #207914] Thu, 07 December 2006 05:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Use
AND PRFINISH-7 < SYSDATE

Re: Returning the weeks involved by going back from sydate [message #207919 is a reply to message #207914] Thu, 07 December 2006 06:04 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I think you need this
trunc(PRFINISH) between trunc(sysdate) - 4*7 and trunc(sysdate)

If not, PLEASE put sample data and explain what exactly you want. Don't be lazy on that. At least 2 to 3 weeks data before / after your period range.

Edited one: I read it differently. If you want to print this week also then use trunc(sysdate) + 7 , else use trunc(sysdate)

By
Vamsi

[Updated on: Thu, 07 December 2006 06:51]

Report message to a moderator

Re: Returning the weeks involved by going back from sydate [message #207924 is a reply to message #207882] Thu, 07 December 2006 06:42 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

ok, here goes


a list of data from the PRTIMEPERIOD table.
(there are other fields, but these are the ones I need)


PRSTART PRFINISH
28/10/06 03/11/06
04/11/06 10/11/06
11/11/06 17/11/06
18/11/06 24/11/06
25/11/06 01/12/06
02/12/06 08/12/06
09/12/06 15/12/06
16/12/06 22/12/06
23/12/06 29/12/06


I need to return the weekly periods for the current week (based on the sysdate at the time the query is run)
and the periods for a given number of weeks backwards.
so If I runt he query today for the past 4 weeks, I will return the following PRSTART values

02/12/06
25/11/06
18/11/06
11/11/06
04/11/06

does this make sense?

thanks,
Matt




Re: Returning the weeks involved by going back from sydate [message #207925 is a reply to message #207924] Thu, 07 December 2006 06:55 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
In that case
JRowbottom
Quote:
Use
AND PRFINISH-7 < SYSDATE
is working fine. Right?
I mean
trunc(sysdate) - 4*7 <= PRFINISH AND PRFINISH-7 < SYSDATE
or
trunc(PRFINISH) between trunc(sysdate) - 4*7 and trunc(sysdate) + 7
By
Vamsi
Re: Returning the weeks involved by going back from sydate [message #207926 is a reply to message #207882] Thu, 07 December 2006 06:59 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

perfect!

thank you VERY much!
I owe you one!

Razz
Re: Returning the weeks involved by going back from sydate [message #208598 is a reply to message #207882] Mon, 11 December 2006 08:39 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

New query using the same tables.


I have to query another table now, which has records with a daily 'slice_date'

the table called prj_blb_slices, has a field containing a date.

I want to query this field with the timeperiod table above, so that the time period PRSTART that each record in prj_blb_slices related to.

e.g.

slice_date PRSTART (from table, above)

03/12/2006 02/12/2006
04/12/2006 02/12/2006
05/12/2006 02/12/2006
06/12/2006 02/12/2006
30/11/2006 25/11/2006
26/11/2006 25/11/2006

I am not sure how to acheive this.

I can run this for one particular date:
select slice_date, prstart

from
niku.prj_blb_slices,
niku.prtimeperiod

where slice_date >= prtimeperiod.prstart
and slice_date <= prtimeperiod.prfinish
and slice_date = to_date('03-12-2006', 'dd-mm-yyyy')


can anybosy advise?
Thanks,
Matt

Re: Returning the weeks involved by going back from sydate [message #208922 is a reply to message #207882] Tue, 12 December 2006 10:15 Go to previous message
k7nixen
Messages: 7
Registered: December 2006
Junior Member
if you remove last row
"and slice_date = to_date('03-12-2006', 'dd-mm-yyyy')" ,
it should work fine?!

Or did i misunderstand what you asked?

[Updated on: Tue, 12 December 2006 10:16]

Report message to a moderator

Previous Topic: Distinct is killing the performance...
Next Topic: I'm trying to get the time difference from 2 column
Goto Forum:
  


Current Time: Mon Dec 05 20:58:07 CST 2016

Total time taken to generate the page: 0.19536 seconds