Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
johnoxy
Starting Member
10 Posts |
 Posted - 2008-09-30 : 02:05:32
|
| HiWe have some SSAS developers putting together a cube for us and i have asked for certain functionality that they said can't be done, this may be the case but i thought i would try and find a way anyway.We have 3 budget releases per year, we'll call them 1,2 and 3. The year 2007 has budget's 1,2,3 and the year 2008 has only 1 and 2.What we want to do is view the budget components by year and budget ID, this is fine BUT when i remove Budget ID from the query and view data at year level i want to see the last budget ID figures for each year.(it currently shows the first BudgetID's figures)The developer said what i wanted was this: "[Budget].[3] for [Time].[2007] but [Budget].[2] for [Time].[2008]"Could anyone give me a thubs up ar down for this. Cheers |
|
|
SteveTR
Starting Member
7 Posts |
Posted - 2008-09-30 : 10:09:53
|
| Johnoxy,Asking to compare the most recent (or last created, or max-of-someID-field) budget for a given year, to the same for any other year should be possible. You didn't give any details as to your table structures etc. but it's not difficult. Here's one example that assumes a 'budgetID' integer is a field in the 'budget_table' table:-- returns budget records for year 2007 with the highest budgetID entered for 2007 select b.*from budget_table bwhere b.budgetID = (select max(bt.budgetID) from budget_table bt where bt.year = 2007) -- returns budget records for year 2008 with the highest budgetID entered for 2008 select b.*from budget_table bwhere b.budgetID = (select max(bt.budgetID) from budget_table bt where bt.year = 2008) You now have the two records sets of interest, which can be 'UNION ALL'ed together, or LEFT/RIGHT joined etc. to do what ever comparisons you want. I haven't worked with cubes since SQL2K was new but it could use queries as the basis for cubes, so I'm assuming SSAS can as well.- TR |
 |
|
|
jsmaccready
Starting Member
2 Posts |
Posted - 2008-09-30 : 13:08:04
|
| Taking it a little further, conceptually: Perhaps it could default to the latest budget year & version UNLESS parameters for year and version are supplied. |
|
|
|
|
|
|
|
|
Can this be done?