Home » SQL & PL/SQL » SQL & PL/SQL » budgetdata (9.2.0.8)
budgetdata [message #316038] Wed, 23 April 2008 09:55 Go to next message
skooman
Messages: 912
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 115 times)
Re: budgetdata [message #316051 is a reply to message #316038] Wed, 23 April 2008 10:17 Go to previous messageGo to next message
skooman
Messages: 912
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 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>for all subsequent days until another value is entered.
Search this forum for "calendar" table/query
Re: budgetdata [message #316065 is a reply to message #316057] Wed, 23 April 2008 10:58 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Thanks for the suggestion. I just did a search, however
(a) you, Michel and others reply "search for calendar" so often that I had to scroll through 3 pages of results before finding any actual contents....
(b) from what I read, calendar is all about generating dates (or ranges) and outerjoining data to it. As far as I am aware, I already did so (how did you think I got this time dimension table, enter 15 years of days by hand?? - oh, that's the life situation, I did actually type in the simplified example Wink)

So, what I can't find in this search is how to repeat a certain value through time until another value pops up.
Re: budgetdata [message #316088 is a reply to message #316065] Wed, 23 April 2008 13:27 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
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 Go to previous messageGo to next message
skooman
Messages: 912
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 Go to previous messageGo to next message
skooman
Messages: 912
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 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
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 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Wow, this is getting really creative! Wink
Will try this, thanks!!
Previous Topic: DBMS_OUTPUT
Next Topic: Query to produce list of minutes in a day
Goto Forum:
  


Current Time: Thu Dec 08 01:58:10 CST 2016

Total time taken to generate the page: 0.10296 seconds