| Returning the weeks involved by going back from sydate [message #207882] |
Thu, 07 December 2006 04:21  |
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 #207900 is a reply to message #207882] |
Thu, 07 December 2006 04:57  |
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 #207907 is a reply to message #207882] |
Thu, 07 December 2006 05:22 |
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 #207914 is a reply to message #207882] |
Thu, 07 December 2006 05:55 |
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 #207919 is a reply to message #207914] |
Thu, 07 December 2006 06:04 |
 |
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
|
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 |
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 #208598 is a reply to message #207882] |
Mon, 11 December 2006 08:39 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
