Home » SQL & PL/SQL » SQL & PL/SQL » budgetdata (9.2.0.8)
| budgetdata [message #316038] |
Wed, 23 April 2008 09:55  |
skooman
Messages: 794
Registered: March 2005
Location: Netherlands |
Senior Member |
|
|
My requirement is for a reporting solution (with a reporting front end,
doesn’t matter in this case I think), to give all budget versions available
for all projects on a certain date. The user can give a date as a parameter
value, and I have to return the budgets as they were at that time.
Attached the create/insert script to this post.
Source tables are (simplified): my_projects and my_budget_versions
My_projects:
PROJECT_ID PROJECT_NAME
1 My project
2 Your project
My_budget versions:
BUDGET_VERSION_ID PROJECT_ID BUDGET_TYPE BASELINED_DATE REVENUE
1 1 AR 2-1-2008 11:00:00 150
2 1 AR 4-1-2008 11:00:00 180
3 1 AR 4-1-2008 15:00:00 220
4 1 FR 1-1-2008 12:00:00 200
5 2 AR 15-12-2007 11:00:00 300
6 2 AR 3-1-2008 11:00:00 320
I do have a time dimension table, which might be helpful:
My_time_dim:
DAY_DATE YEAR MONTH DESCRIPTION
1-1-2008 2008 1 1 januari 2008
2-1-2008 2008 1 2 januari 2008
3-1-2008 2008 1 3 januari 2008
4-1-2008 2008 1 4 januari 2008
5-1-2008 2008 1 5 januari 2008
6-1-2008 2008 1 6 januari 2008
I have to get the following results:
project budget_type date revenue
My project AR 1-1-2008 <-- no value, no budget known
My project AR 2-1-2008 150 <-- first budget version!
My project AR 3-1-2008 150 <-- last version still applies
My project AR 4-1-2008 220 <-- last entered version on that date,
note that 180 is ignored
My project AR 5-1-2008 220 <-- last version still applies
My project FR 1-1-2008 200
My project FR 2-1-2008 200
My project FR 3-1-2008 200
My project FR 4-1-2008 200
My project FR 5-1-2008 200
Your project AR 1-1-2008 300 <-- found in december, so before start time dimension
Your project AR 2-1-2008 300
Your project AR 3-1-2008 320 <-- newly entered version
Your project AR 4-1-2008 320
Your project AR 5-1-2008 320
Your project FR 1-1-2008 <-- no value, no budget known
Your project FR 2-1-2008
Your project FR 3-1-2008
Your project FR 4-1-2008
Your project FR 5-1-2008
This is how far I got (added some linebreaks to make it better readable):
SQL> SELECT sub3.project_name
2 ,sub3.day_date
3 ,sub2.budget_type
4 ,sub2.revenue
5 FROM (SELECT *
6 FROM (SELECT mbv.project_id
7 ,mbv.budget_type
8 ,mbv.revenue
9 ,trunc(mbv.baselined_date) baselined_date
10 ,rank() over(PARTITION BY mbv.project_id
, mbv.budget_type
, trunc(mbv.baselined_date)
ORDER BY mbv.baselined_date DESC) rnk
11 FROM my_budget_versions mbv
12 ) sub1
13 WHERE rnk = 1) sub2
14 ,(SELECT mp.*
15 ,mtd.*
16 FROM my_projects mp
17 ,my_time_dim mtd) sub3
18 WHERE sub3.project_id = sub2.project_id(+)
19 AND sub3.day_date = sub2.baselined_date(+)
20 ORDER BY sub3.project_name
21 ,sub3.day_date
22 /
PROJECT_NAME DAY_DATE BUDGET_TYPE REVENUE
-------------------- ----------- ----------- ----------
My project 1-1-2008 FR 200
My project 2-1-2008 AR 150
My project 3-1-2008
My project 4-1-2008 AR 220
My project 5-1-2008
My project 6-1-2008
Your project 1-1-2008
Your project 2-1-2008
Your project 3-1-2008 AR 320
Your project 4-1-2008
Your project 5-1-2008
Your project 6-1-2008
12 rows selected
SQL>
What is still missing is (a) the values for the “empty” days (so, pick last budget
basedlined, if any) and (b) the project/budget_type combinations that are not entered.
Any suggestions??
Attachment: TEST.SQL
(Size: 2.82KB, Downloaded 8 time(s))
|
|
|
| Re: budgetdata [message #316051 is a reply to message #316038 ] |
Wed, 23 April 2008 10:17  |
skooman
Messages: 794
Registered: March 2005
Location: Netherlands |
Senior Member |
|
|
Silly me, the budget types should of course also be outerjoined to the whole thing.
But, my main problem still remains: how to show the "last entered budgetvalue" for all subsequent days until another value is entered.
|
|
|
| Re: budgetdata [message #316057 is a reply to message #316038 ] |
Wed, 23 April 2008 10:30 |
anacedent
Messages: 5027
Registered: July 2005
Location: surf meets turf in SoCal |
Senior Member |
|
|
>for all subsequent days until another value is entered.
Search this forum for "calendar" table/query
|
|
| |
| Re: budgetdata [message #316088 is a reply to message #316065 ] |
Wed, 23 April 2008 13:27 |
S.Rajaram
Messages: 516
Registered: October 2006
Location: United Kingdom |
Senior Member |
|
|
This may not be a great solution. But atleast it might give you an idea how to get it.
1 with
2 t as
3 (
4 select a.project_id, a.project_name, b.budget_type, day_date,
5 min(day_date) over() min_day_date
6 from my_projects a,
7 (select project_id, budget_type from my_budget_versions
8 group by project_id, budget_type
9 ) b,
10 my_time_dim
11 )
12 select distinct
13 project_name, budget_type, trunc(nvl(baselined_Date, day_date)),
14 last_value(revenue ignore nulls)
15 over(partition by project_name, budget_type
16 order by nvl(trunc(baselined_date), day_date)) rev
17 from
18 (
19 select
20 t.project_id, t.project_name, t.day_date, t.budget_type, revenue,
21 case when baselined_date < min_day_date
22 then
23 min_day_date
24 else
25 baselined_date
26 end baselined_date
27 from
28 t,
29 my_budget_versions bv
30 where
31 t.project_id = bv.project_id (+)
32 and
33 t.day_date = (
34 case when bv.baselined_date (+) < min_day_date
35 then
36 min_day_date
37 else
38 trunc(bv.baselined_date(+))
39 end
40 )
41 and
42 t.budget_type = bv.budget_type (+)
43* )
PROJECT_NAME BUDGET_TYP TRUNC(NVL(BASELINE REV
-------------------- ---------- ------------------ ----------
My project AR 01-JAN-08
My project AR 02-JAN-08 150
My project AR 03-JAN-08 150
My project AR 04-JAN-08 180
My project AR 05-JAN-08 180
My project AR 06-JAN-08 180
My project FR 01-JAN-08 200
My project FR 02-JAN-08 200
My project FR 03-JAN-08 200
My project FR 04-JAN-08 200
My project FR 05-JAN-08 200
My project FR 06-JAN-08 200
Your project AR 01-JAN-08 300
Your project AR 02-JAN-08 300
Your project AR 03-JAN-08 320
Your project AR 04-JAN-08 320
Your project AR 05-JAN-08 320
Your project AR 06-JAN-08 320
Your project FR 01-JAN-08
Your project FR 02-JAN-08
Your project FR 03-JAN-08
Your project FR 04-JAN-08
Your project FR 05-JAN-08
Your project FR 06-JAN-08
Here the key for your question how to retreive the last value is indeed the last_value analytical function.
Regards
Raj
|
|
|
| Re: budgetdata [message #316185 is a reply to message #316088 ] |
Thu, 24 April 2008 03:27 |
skooman
Messages: 794
Registered: March 2005
Location: Netherlands |
Senior Member |
|
|
|
Thanks a lot! Will try to get this into the real-life situation.
|
|
|
| Re: budgetdata [message #316285 is a reply to message #316185 ] |
Thu, 24 April 2008 08:20 |
skooman
Messages: 794
Registered: March 2005
Location: Netherlands |
Senior Member |
|
|
Raj,
I suppose you did that example on a 10 database (or higher) right? Since ignore nulls is not available in the 9.2.0.8 I have to work on...
Documentation 9i:
LAST_VALUE is an analytic function. It returns the last value in an ordered set of values.
Documentation 10g:
LAST_VALUE is an analytic function. It returns the last value in an ordered set of values. If the last value in the set is null, then the function returns NULL unless you specify IGNORE NULLS. This setting is useful for data densification. If you specify IGNORE NULLS, then LAST_VALUE returns the fist non-null value in the set, or NULL if all values are null.
So, I'm getting really close, but still not there yet... Any suggestions how to do something similar in 9i?
Regards,
Sabine
|
|
|
| Re: budgetdata [message #316303 is a reply to message #316285 ] |
Thu, 24 April 2008 09:44 |
S.Rajaram
Messages: 516
Registered: October 2006
Location: United Kingdom |
Senior Member |
|
|
Try whether this is of any help to you.
SQL> l
1 with
2 t as
3 (
4 select a.project_id, a.project_name, b.budget_type, day_date,
5 min(day_date) over() min_day_date
6 from my_projects a,
7 (select project_id, budget_type from my_budget_versions
8 group by project_id, budget_type
9 ) b,
10 my_time_dim
11 )
12 select distinct
13 project_name, budget_type, trunc(nvl(baselined_Date, day_date)),
14 substr(
15 max(rn)
16 over(partition by project_name, budget_type
17 order by budget_type,trunc(nvl(baselined_Date, day_date))
18 )
19 ,11) revenue
20 from
21 (
22 select
23 t.project_id, t.project_name, t.day_date, t.budget_type, revenue,
24 to_number(lpad(
25 dense_rank() over(partition by t.project_id, t.budget_type
26 order by nvl(baselined_date,t.day_date))
27 ,10,9)
28 ||revenue) rn,
29 case when baselined_date < min_day_date
30 then
31 min_day_date
32 else
33 baselined_date
34 end baselined_date
35 from
36 t,
37 my_budget_versions bv
38 where
39 t.project_id = bv.project_id (+)
40 and
41 t.day_date = (
42 case when bv.baselined_date (+) < min_day_date
43 then
44 min_day_date
45 else
46 trunc(bv.baselined_date(+))
47 end
48 )
49 and
50 t.budget_type = bv.budget_type (+)
51* )
PROJECT_NAME BUDGET_TYP TRUNC(NVL(BASELINE REVENUE
-------------------- ---------- ------------------ --------------------
My project AR 01-JAN-08
My project AR 02-JAN-08 150
My project AR 03-JAN-08 150
My project AR 04-JAN-08 220
My project AR 05-JAN-08 220
My project AR 06-JAN-08 220
My project FR 01-JAN-08 200
My project FR 02-JAN-08 200
My project FR 03-JAN-08 200
My project FR 04-JAN-08 200
My project FR 05-JAN-08 200
My project FR 06-JAN-08 200
Your project AR 01-JAN-08 300
Your project AR 02-JAN-08 300
Your project AR 03-JAN-08 320
Your project AR 04-JAN-08 320
Your project AR 05-JAN-08 320
Your project AR 06-JAN-08 320
Your project FR 01-JAN-08
Your project FR 02-JAN-08
Your project FR 03-JAN-08
Your project FR 04-JAN-08
Your project FR 05-JAN-08
Your project FR 06-JAN-08
24 rows selected.
A brief explanation. In the inner query i am using dense_rank to concatenate the rank value along with the revenue. Also i am using lpad so that i can strip of the characters which I have concatenated to make the group unique.
Hope that helps
Regards
Raj
|
|
|
| Re: budgetdata [message #316307 is a reply to message #316303 ] |
Thu, 24 April 2008 10:10 |
skooman
Messages: 794
Registered: March 2005
Location: Netherlands |
Senior Member |
|
|
Wow, this is getting really creative! 
Will try this, thanks!!
|
|
|
Goto Forum:
Current Time: Sat May 17 03:53:52 CDT 2008
Total time taken to generate the page: 0.03374 seconds |
]
Members
Search
Help
Register
Login
Home
