Home » SQL & PL/SQL » SQL & PL/SQL » Time Bucket
Time Bucket [message #435481] Wed, 16 December 2009 07:35 Go to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
i need to create a period bucket using the start date and saysdate and i want the output as below .

please advise how to achieve this

1 day
2 days
3 days
4 days
5 days
6 days
7 days
8D - 1M
2 Months
3 Months
4 Months
5 Months
6 Months
7 Months
8 Months
9 Months
10 Months
11 Months
12 Months
1-2 Years
2-3 Years
3-4 Years
4-5 Years
Beyond 5Ys
Re: Time Bucket [message #435492 is a reply to message #435481] Wed, 16 December 2009 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select '1 day
2 days
3 days
4 days
5 days
6 days
7 days
8D - 1M
2 Months
3 Months
4 Months
5 Months
6 Months
7 Months
8 Months
9 Months
10 Months
11 Months
12 Months
1-2 Years
2-3 Years
3-4 Years
4-5 Years
Beyond 5Ys '
from dual;

Regards
Michel

Re: Time Bucket [message #435591 is a reply to message #435481] Thu, 17 December 2009 00:27 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
Thanks michel,

actually i want to make a case statement which will calculate these days , month years, beyond 5 years, beyond 10 years based on the opening date and sys date .

Can you please suggest me the syntax by which i can create it
Re: Time Bucket [message #435596 is a reply to message #435591] Thu, 17 December 2009 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And I want a test case but you can do it with just a CASE.

Regards
Michel
Re: Time Bucket [message #435598 is a reply to message #435481] Thu, 17 December 2009 01:04 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
i did not get your words michel.

actually i need to create a variable usin case which i want to provide to users to run and see the results as below and they can group by the revenue based on this variable.

eg : 1D- $ 500 and so on till 10 yrs and above

Please suggest me the syntax and how to get this in one variable

Do let me know if you need any other info
Re: Time Bucket [message #435599 is a reply to message #435481] Thu, 17 December 2009 01:06 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
Please find the same example attached

this is the basic report the users are looking for
Re: Time Bucket [message #435600 is a reply to message #435599] Thu, 17 December 2009 01:13 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>Please find the same example attached
where?
Re: Time Bucket [message #435601 is a reply to message #435599] Thu, 17 December 2009 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is nothing attached. As many of us can't or don't want to download files, post it inline.

Quote:
actually i need to create a variable usin case which i want to provide to users to run and see the results as below and they can group by the revenue based on this variable.

I did not get your words either.
What is input, what is output?

Regards
Michel
Re: Time Bucket [message #435604 is a reply to message #435481] Thu, 17 December 2009 01:38 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member

Period Amount Rate
1 day
2 days
3 days
4 days
5 days
6 days
7 days
8D - 1M
2 Months
3 Months
4 Months
5 Months
6 Months
7 Months
8 Months
9 Months
10 Months
11 Months
12 Months
1-2 Years
2-3 Years
3-4 Years
4-5 Years




This the report format i want to create

so i need a query to get the the first colum which gives the time bucket in days and months and years


Let me know if any other info is needed


[Updated on: Thu, 17 December 2009 01:40]

Report message to a moderator

Re: Time Bucket [message #435605 is a reply to message #435591] Thu, 17 December 2009 01:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
actually i want to make a case statement which will calculate these days , month years, beyond 5 years, beyond 10 years based on the opening date and sys date .


That's exactly how you do it:
CASE
WHEN opening_date-sysdate <=1  then '1 Day'
WHEN opening_date-sysdate <=2  then '2 Days'
....


There are refinements you can make. You can get all the days in a single line:
CASE
WHEN opening_date-sysdate <=7  then ceil(opening_date-sysdate)||' Day'||
     case when opening_date-sysdate > 1 then 's' end
WHEN add_months(opening_date,1) > sysdate then '8D-1M'
...

You can do something similar with months_between to get the values for the months as well.

I'd create a function into which you passed the opening_Date column and it returned the value - that would make it much easier to group by in your queries.

[Updated on: Thu, 17 December 2009 02:28] by Moderator

Report message to a moderator

Re: Time Bucket [message #435606 is a reply to message #435605] Thu, 17 December 2009 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'd create a function into which you passed the opening_Date column and it returned the value - that would make it much easier to group by in your queries.

But it prevents optimizer from knowing what you are actually doing and so can't optimize it.

Regards
Michel
Re: Time Bucket [message #435607 is a reply to message #435606] Thu, 17 December 2009 01:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The optimizer isn't going to have a chance with a CASE statement that complex even if it's in the query.
Re: Time Bucket [message #435608 is a reply to message #435607] Thu, 17 December 2009 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know what the optimizer is able to do and what the optimizer guys will be able to make it do.

Regards
Michel
Re: Time Bucket [message #435609 is a reply to message #435481] Thu, 17 December 2009 02:04 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
thanks for the reply experts..

JRowbottom thanks for the code ...

also can i suggest this function idea to the DBA .

any chance we can calculate this bucket at db LEVEL.
if yes please tell me what to suggest to the DBA so i can ask him to create a table with this bucket

[Updated on: Thu, 17 December 2009 02:11]

Report message to a moderator

Re: Time Bucket [message #435612 is a reply to message #435609] Thu, 17 December 2009 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
any chance we can calculate this bucket at db LEVEL.

What does this mean? If it is inside the query, it is calculated at db level (whatever it is inline or in a stored function).

Regards
Michel
Re: Time Bucket [message #435613 is a reply to message #435609] Thu, 17 December 2009 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if yes please tell me what to suggest to the DBA so i can ask him to create a table with this bucket

Once again, what table have you in mind?

Regards
Michel
Re: Time Bucket [message #435614 is a reply to message #435612] Thu, 17 December 2009 02:13 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
michel what i meant to say was i could suggest the DBA to do this at DB level instead of me crating a Query

i hope am clear .
Re: Time Bucket [message #435615 is a reply to message #435614] Thu, 17 December 2009 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does this mean "i could suggest the DBA to do this at DB level"? What DBA should do? What is "this"?

By the way, this is the same question than in your previous topic a month ago: Weekly bucket (merged 3)

Regards
Michel
Re: Time Bucket [message #435616 is a reply to message #435614] Thu, 17 December 2009 02:15 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
A bucket table which would generate the above columns.so i can use this colums directly instead of writing a complex case statement .

I am grateful to JRowbottom for providing the case code ..and i would definitely try it if DBA doesnt help here.
Re: Time Bucket [message #435618 is a reply to message #435616] Thu, 17 December 2009 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And how would you use the "bucket table"?
Clarify what you have in mind.

Regards
Michel
Re: Time Bucket [message #435619 is a reply to message #435608] Thu, 17 December 2009 02:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
I don't know what the optimizer is able to do and what the optimizer guys will be able to make it do.

Actually - if you put it in a function then you can make a function based index and be certain that the function used in the query will match the index.

That way the optimizer gets to know exactly what's going on.
Re: Time Bucket [message #435620 is a reply to message #435616] Thu, 17 December 2009 02:18 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
yes i have posted that and it is different from this requirement .

Well i guess you are concerned so much for the DBA's work....:)Smile
Re: Time Bucket [message #435621 is a reply to message #435620] Thu, 17 December 2009 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Well i guess you are concerned so much for the DBA's work....Smile

No, I am not but if you just tell the DBA "create a bucket table", I'm pretty sure it will ask you the same question as I do. So try to answer them before asking your DBA, at least here there is no consequence but if you irritate your DBA you might have problem in the future. So try to come with a precise and clear request.

Regards
Michel
Re: Time Bucket [message #435622 is a reply to message #435620] Thu, 17 December 2009 02:22 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
Bucket table will give me the same column the bucket column...which i need.

May be i must speak to DBA first and see what are his ideas and then i must post here againa dn clairfy the same with you michel Smile

thanks for replies michel nice learning for me
Re: Time Bucket [message #435623 is a reply to message #435622] Thu, 17 December 2009 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Bucket table will give me the same column the bucket column...which i need.

It is not a table you need but a function as JRowbottom told it (although I prefer it is inside the query itself).

Regards
Michel
Re: Time Bucket [message #435624 is a reply to message #435619] Thu, 17 December 2009 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Actually - if you put it in a function then you can make a function based index and be certain that the function used in the query will match the index.

a FBI must be based on a deterministic function, which cannot be the case with an expression depending on sysdate (the 1 day bucket is different each day...).

Regards
Michel
Re: Time Bucket [message #435625 is a reply to message #435616] Thu, 17 December 2009 02:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
A bucket table which would generate the above columns.so i can use this colums directly instead of writing a complex case statement .


You could create a table with a structure like:
CREATE TABLE bucket AS
(days_from        number
,days_to          number
,description      varchar2(30));


You would populate this with the date ranges that you want, and then you'd include it in your query like this:
WHERE (sysdate - opening_date) BETWEEN bucket.days_from AND bucket.days_to
Re: Time Bucket [message #435629 is a reply to message #435481] Thu, 17 December 2009 02:33 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
thats amazing idea ..JRowbottom ...

Million Thanks for this ...Now i am sure i can suggest this to DBA and he would be happy.

It easens the work for all of us....

Thanks a lot sir..
Re: Time Bucket [message #435651 is a reply to message #435629] Thu, 17 December 2009 04:42 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If I were implementing it, I'd go for the function approach.
Previous Topic: Is this statement repeating itself (code review) or can the last 2 be combined
Next Topic: Set time parameter in date/time field
Goto Forum:
  


Current Time: Wed Dec 07 06:55:28 CST 2016

Total time taken to generate the page: 0.19105 seconds