Group by given date ranges

[Welshbird]

OK, I have two tables

SQL>desc oilatum_outlet
Name                   Null?    Type
------------------------------- ------------------------
SOURCE                          VARCHAR2(6)
ONEKEY_PHARMACY_CODE            NUMBER
RXDATE                          DATE
GSU                             NUMBER
BRAND                           VARCHAR2(20)
PACK                            VARCHAR2(50)
COUNTING_UNITS                  NUMBER
PACK_UNITS                      NUMBER
DTE_LD                          DATE      

SQL>desc vw_stf_week
Name                 Null?    Type         
-------------------- -------- -------------
ST_WEEK                       DATE         
END_WEEK                      DATE

and vw_sft_week has data that looks like this:

ST_WEEK   END_WEEK
--------- ---------
23-AUG-07 29-AUG-07
30-AUG-07 05-SEP-07
06-SEP-07 12-SEP-07
13-SEP-07 19-SEP-07
20-SEP-07 26-SEP-07
27-SEP-07 03-OCT-07
04-OCT-07 10-OCT-07
11-OCT-07 17-OCT-07
18-OCT-07 24-OCT-07
25-OCT-07 31-OCT-07

Each row in the other table has a date, and I've been asked to summarise the data into groups of these weeks.

Right now, I'm not sure I know where to start. If anyone can point me at a cunning plan I'd be hugely grateful.

Thanks,

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[hoinz]

Hello,

in your table vw_sft_week all ranges are one week, from Thursday to Wednesday.
Will this be true in call cases?
If so, you could ignore this table at all. Just group by previous Thursday.
And there have been threads about finding the last Monday, or whatever day of the week, e.g.
thread759-1417403

hope this helps

 

[Welshbird]

Yes - all weeks go from Thursday to Wednesday.

I've tried adding a column on the end of the table and updating it as follows:

UPDATE oilatum_test
SET end_week =  ( SELECT vw_stf_week.end_week
FROM vw_Stf_week
WHERE oilatum_test.rxdate between vw_stf_week.st_week and vw_stf_week.end_week)

but some rows end up being null and I can't see why.

I had looked at the other thread, but I can't seem to see how I group by lots of weeks for that.


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

Ah... my vw_stf_week doesn't begin and end at midnight, which isn't helping I think.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[hoinz]

As for your idea: Yes, I think you are right; the time part in column rxdate will need an extra treatment.

And for my idea:
I didn't check the details, but something like this for a start:
select trunc(rxdate-2,'DY')+2 as prev_thursday, sum(whatever)
from table
group by trunc(rxdate-2,'DY')+2

But it should be sufficent to have one working solution!

 

[Welshbird]

OK - Panic over.

Was being blonde...

Truncated both dates in the week lookup, then used

sql>UPDATE oilatum_test
 SET end_week =  ( SELECT stf_week.end_week
 FROM Stf_week
 WHERE oilatum_test.rxdate >= stf_week.st_week and oilatum_test.rxdate < stf_week.end_week+1)

to update. The nulls were where the date feel on the end_week date, and having truncated it it was losing all of those date. THe +1 sorted that.

Phew!

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SantaMufasa]

Was being blonde...

None of the photos that I've ever seen of you have you being "blonde". <grin>

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

 

[Welshbird]

Hehehe.



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]