Home » SQL & PL/SQL » SQL & PL/SQL » Weekly bucket (merged 3)
Weekly bucket (merged 3) [message #432579] Wed, 25 November 2009 00:44 Go to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
how can i create a Weekly Bucket with a date that gives me bucket for last 6 weeks

i need to do this using a case statement

week 1 to week 6
=If([Case Close Dt (truncated)]Between(RelativeDate(CurrentDate();-42);RelativeDate(CurrentDate();-36));"Week 6";If([Case Close Dt (truncated)]Between(RelativeDate(CurrentDate();-35);RelativeDate(CurrentDate();-29));"Week 5";"Week 4")) and so on.....for the last 6 weeks.

please help em with correct syntax for oracle
Re: Weekly bucket (merged 3) [message #432586 is a reply to message #432579] Wed, 25 November 2009 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Also always post your Oracle version with 4 decimals.

And format your post.

Regards
Michel

[Updated on: Wed, 25 November 2009 00:54]

Report message to a moderator

Re: Weekly bucket (merged 3) [message #432593 is a reply to message #432579] Wed, 25 November 2009 01:51 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
i only need to create a Weekly bucket variable .

can we make it using case like an ageing bucket .

i dont want to modify the db or create any table in Db

please let me know if using case its possibl;e
Re: Weekly bucket (merged 3) [message #432595 is a reply to message #432593] Wed, 25 November 2009 02:01 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well. "CurrentDate()" is "sysdate" in Oracle, and you can just add and subtract days from dates to add or subtract days.

Everything else is pretty much impossible to tell without a test case.
Re: Weekly bucket (merged 3) [message #432600 is a reply to message #432586] Wed, 25 November 2009 02:56 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think that your code would translate to something like:
CASE when trunc(close_dt) between trunc(sysdate)-42 and trunc(sysdate)-36 then 'Week 6'
     when trunc(close_dt) between trunc(sysdate)-35 and trunc(sysdate)-29 then 'Week 5'
     ...     
     else
     ...
     end


I also think you can rewrite it in a general case as:
'Week '||to_char(floor((trunc(sysdate)-trunc(close_dt))/7))
Previous Topic: UNIQUE CONSTRAINT
Next Topic: how requirement is specified?
Goto Forum:
  


Current Time: Thu Sep 29 12:24:56 CDT 2016

Total time taken to generate the page: 0.18586 seconds