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

 

[r937]

perhaps this will get you started:

Grouping output by week

http://tinyurl.com/4ble

rudy

 

[Crystalguru]

Thanks! The article is exactly what I was looking for. I will also bookmark the website.

 

[r937]

thanks for the kind words

if you meant that you're going to bookmark searchdatabase.com, that's good, it's a great site, and you can happily get lost in there...

if you want to see specific articles, i.e. SQL questions that i've answered, you will find this page much easier to use, as it has links to over 200 answers --

http://r937.com/sqlate.cfm

rudy

 

[Crystalguru]

I will bookmark both! I need all the help I can get. I am browsing the websites now. Just running into some difficulty, still.

I still cannot figure out how to group all the dates into the Sunday dates.
2/9=408
2/8=410
2/7=410
2/6=412
2/5=415
2/4=414
2/3=413
---------
Total 2882 and display the date 2/9/03.

thanks

 

[r937]

have you thought about the oracle week function?

 

[Crystalguru]

Every DATeTIMe fuction I have looked at has been either DAY or MONTH. Where can I find this WEEK function? Maybe the website??

 

[r937]

yup

IW is one of the options of TO_CHAR

IW gives Week of year (1-52 or 1-53) based on the ISO standard

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

rudy

 

[Crystalguru]

Great the week function works. I grouped by that number and thought I was finished, except for one problem. Since I have data from 2001-2003, it would sum all the 01,02,03 weeks together. Which is NOT what I wanted. So, I stripped the year out of the date field (01,02,03) and concatenated with the week. I.E. (0101 =first week in Jan 2001 and so on)

But here's my problem:

DATE_DAY WK#YR
12/31/01 = 01 01
01/01/2002 = 01 02
GROUP BY CONCAT(WK#YR)
Although, 12/31 falls into 2001, it is included with Jan2002's week ONE. This continues for all years.

How am I to handle this?

 

[r937]

if you don't like the ISO weeks, you are basically left to your own devices to define your own week UDF (user-defined function), and i have no clue how you do that in oracle

of course, you could build a solution like i outlined in one of those searchdatabase.com articles...


rudy

 

[Crystalguru]

Rats! I am so close to getting this completed. I'm not sure about UDF's in Oracle either. More of a crystal-gal with UDF's. I will take another look at the article and see if it helps.
thanks

 

[r937]

crystal? as in crystaltech? or crystal reports?

the problem with "weeks" is that december 31 has this annoying habit of falling on days other than saturday

so you are going to have to deal with that one way or another

in my opinion, the ISO week is the *best* way to deal with that

otherwise, you will have incomplete -- and therefore un-comparable -- weeks at the beginning and end of each calendar year

ask your company's internal auditors or accountants what they do

i betcha they have all kinds of good ideas for ya

;-)

 

[Crystalguru]

Crystalguru = Crystal reports!

As of right now, December is more than annoying! I am writing the SQL to pull into a crystal report. errh

I like the ISO weeks function just have to figure out how to include the roll=over december dates...

I will definately ask around the office.
thanks

 

[Crystalguru]

Thought I would show you some data. After seeing it, you may come up with a brilliant plan!

Hope hope hope



id date_day wk# YR sum units %
19 12/30/2002 1 02 421 446 94.39
19 12/31/2002 1 02 414 446 92.82 19 1/1/2003 1 03 415 446 93.04
19 1/2/2003 1 03 415 446 93.04
19 1/3/2003 1 03 417 446 93.49
19 1/4/2003 1 03 417 446 93.49
19 1/5/2003 1 03 418 446 93.72

 

[r937]

well, duh!

are those week numbers in some table somewhere, or did you just make them up for illutrative purposes here?

the solution is GROUP BY YR, wk#

 

[Crystalguru]

I actually got the week numbers from the week function. I didn't make them up- really.

Good thought BUT...it will group all of January 2002 week one with December 2002 week 1. Which isn't correct, Dec2002 is week one of 2003...

I thought I had it too!

 

[dnoeth]

Did you look at "Datetime Format Elements" in the SQL manual?
The next entry after 'IW'?
It's 'I'/'IY'/'IYY'/'IYYY' and that's the ISO year ;-)

And so:
2002-12-31 --> 2003W01
2003-01-01 --> 2003W01

Dieter

 

[karluk]

You can most likely get this to work with additional tweaking. However, it's probably simpler to identify your weeks with the "next_day" function. All the days in a week from Monday to the following Sunday have the property that the next Monday is the same. Therefore, if you take the function

trunc(next_day(ts.date_day, 'MONDAY'))

and use it in your group by clause, you should get the result you want. I'm assuming, of course, that you don't mind having the end of December and the beginning of January included in the same week. That's the natural way to do it, but accountants might frown on grouping days from different years together.

 

[r937]

karluk, that's neat!

i had not seen NEXT_DAY (but then, i'm not a regular oracle user)

in this context, its use should be documented, i.e. if the purpose of the query is to produce aggregates by week, "trunc(next_day(xxx,'MONDAY'))" will take effort to understand later

thanks

http://rudy.ca/contact.cfm

 

[Crystalguru]

Karluk! Ah ha, I knew there was a way! Yes, the trunc(next_day(ts.date_day,'MONDAY')) worked fabulously (is that a word?, oh well, I am just happy! )

Dnoeth, I tried using the ISO year, but was unable to decipher the data as returned. I.e. (ts.date_day,'I/IY/IYY/IYYY) results: 3/03/003/2003. I am assuming:
I: year #
IY: year and 1 digit
IYY: year and 2 digit
IYYY: whole year.

You mentioned a SQL manual for these Datetime format elements? Could you please tell me where I could get my hands on that manual??!! thanks.
Thanks to everyone (Rudy, thanks for hanging in there with me!) that helped me!