dates for the past 8 weeks plus 4 weeks into the future 1

[amillia]

I need the dates from the field week_ending for the past 8 weeks also I need the next 4 week dates and I have to have it under the same field name because I am using it with coldfusion charts. Can anyone help.

Some of my code:

SELECT shop, (Five_S_tbl.week_ending and Five_S_Tbl.week_ending + 7 and Five_S_Tbl.week_ending + 14) as End_week, five_s_score, last_audit_score
FROM Five_S_Tbl
where shop = '722'
and week_ending > (TO_DATE(sysdate) - 61)
order by week_ending

 

[SantaMufasa]

Amillia,

I need some clarification from you about your specifications.

I need the dates...for the past 8 weeks also I need the next 4 week dates

What is the starting-reference date to calculate these 12 dates? For example, do you want to begin from the current date (SYSDATE)? And what is the day of the week that you wish to use for your "Week_ending" date...Friday, Saturday, Sunday?


For simply the "12 dates" portion of your need, here is a query that produces those dates, presuming that you want to use SYSDATE as your beginning reference, and Saturdays are the week-ending days:

select next_day(sysdate,'SAT')-56+(7*(rownum-1))
from all_objects
where rownum <= 12;

14-OCT-06
21-OCT-06
28-OCT-06
04-NOV-06
11-NOV-06
18-NOV-06
25-NOV-06
02-DEC-06
09-DEC-06
16-DEC-06
23-DEC-06
30-DEC-06

12 rows selected.

We can help you further if you post a sample of how you want the above 12 dates (8 past, 4 future) to correlate with your other expressions in your SELECT statement.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[amillia]

This is difficult to even understand, sorry but Let me try to clarify. Week_ending is actually a name of a field in a table and is one day which will be filled in one time on every Monday. So for this Field Week_ending I need the Week_ending and the other fields for the past 8 weeks plus a projected week_ending date for each of the next 4 weeks. And it all starts from the most current week_ending date that has been entered into the database. Obviously there is no data to go along with the next 4 weeks so they want the data from week 8 to be reprojected over for the next 4 weeks for pupose of the coldfusion graph. I hope this helps thank you for helping me.

 

[SantaMufasa]

Amillia, I am rather a visual person on things like this. It would help enormously if you can please post a DESCRIBE of the table involved and an excerpt of sample data in that table that represents a full-cycle example of your 8 week_ending values and how you want the future 4 week_ending projections to appear.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[amillia]

sample data from table

SCORE SHOP WEEK_ENDING
0.75 722 11/21/06
0.1 722 11/28/06
2 722 11/14/06
1.5 722 11/07/06
1.7 722 10/31/06
1.7 722 10/24/06
1.4 722 10/17/06
1.22 722 10/10/06
1.4 722 10/03/06
1.5 722 09/27/06
1.12 722 12/05/06

What I need output from the query

SCORE SHOP WEEK_ENDING
1.4 722 10/17/06
1.7 722 10/24/06
1.7 722 10/31/06
1.5 722 11/07/06
2 722 11/14/06
0.75 722 11/21/06
0.1 722 11/28/06
1.12 722 12/05/06
1.12 722 12/12/06
1.12 722 12/19/06
1.12 722 12/26/06
1.12 722 01/02/06

 

[SantaMufasa]

Amillia, The following code should do what you want:

select Score,Shop,Week_ending
  from (select rownum rn, x.* from Five_S_Tbl x
         where week_ending <= (select max(week_ending) from Five_S_Tbl
                                where shop = '722')
           and shop = 722) y
 where rn <= 7
union 
select Score,Shop,Week_ending+((rownum-1)*7)
  from (select rownum rn, d.* from Five_S_Tbl d where shop = '722') a
      ,(select max(week_ending) max_dt from Five_S_Tbl
                               where shop = '722') b
      ,(select * from all_objects where rownum <= 5) c
 where a.week_ending = max_dt
 order by 3;

SCORE       SHOP WEEK_ENDI
----- ---------- ---------
  1.4        722 17-OCT-06
  1.7        722 24-OCT-06
  1.7        722 31-OCT-06
  1.5        722 07-NOV-06
    2        722 14-NOV-06
  .75        722 21-NOV-06
   .1        722 28-NOV-06
 1.12        722 05-DEC-06
 1.12        722 12-DEC-06
 1.12        722 19-DEC-06
 1.12        722 26-DEC-06
 1.12        722 02-JAN-07

Plus, it works for whatever is your most current week_ending without hardcoding for any specific dates.

Let us know if this is satisfactory.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[amillia]

When I ran it it is skipping a day.

Here is the table data

SCORE SHOP WEEK_ENDING
1.5 722 10/9/2006
1.6 722 10/16/2006
1.65 722 10/23/2006
1.45 722 10/30/2006
1.85 722 11/6/2006
1.95 722 11/13/2006
1.75 722 11/20/2006
1.85 722 11/27/2006
1.95 722 12/4/2006

Here is the output of the query

SCORE SHOP WEEK_ENDING
1.5 722 10/9/2006
1.6 722 10/16/2006
1.65 722 10/23/2006
1.45 722 10/30/2006
1.85 722 11/6/2006
1.95 722 11/13/2006
1.75 722 11/20/2006
1.95 722 12/4/2006
1.95 722 12/11/2006
1.95 722 12/18/2006
1.95 722 12/25/2006
1.95 722 1/1/2007

By the way that query is amazing.

 

[amillia]

I changed the <=7 to <=8 and it works. Thank you very much for this. Excellent.