Roll up 7 days

[Crystalguru]

hello,

I need to have the following query give me the sum for the last7days based on a sunday date.
select
c.property_id
,ts.date_day
,sum(ts.occupied)
,c.property_unit_count as Unit_Count
,(sum(ts.occupied)/c.property_unit_count)*100 as WA_Occupancy
from
s.time_series_by_unit_type ts
,s.communities c
where ts.property_id = c.property_id
and c.property_id = 19
and TS.DATE_DAY >= TO_DATE('1/1/2002','MM/DD/YYYY')
group by
ts.date_day
,c.property_id
,c.property_unit_count

Data example:
for Sunday 2/9/2003,
the sum(occupied) should be 2882
Getting that value from
2/9=408
2/8=410
2/7=410
2/6=412
2/5=415
2/4=414
2/3=413
---------
2882

Any help would be appreciated.

thanks

 

[dnoeth]

You should include only one of the 4 options ;-)

For more info look at the link Rudy posted:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements4a.htm#48515

Search for "ISO Standard Date Format Elements"

And you can download any software/manual from Oracle's Technet (after registering):

http://technet.oracle.com/

Dieter

 

[Crystalguru]

Whoops! You can see why I am still learning. Thanks!

The technet site and rudy's site will come in handy for future reference. thanks again,