I am looking for a way to pull the last room assigned for each person. I tried the MAX function, but I get both rooms for person 111 because the rows aren’t unique. I can get the MAX time for each person, but when I add the room into the query, I get 2 rows for person 111 instead of 1.
Example of Data from Table:
Person Room Date Assigned
111 Yellow 06/01/2009 10:01:34 PM
111 Green 06/01/2009 11:10:45 PM
222 Yellow 06/01/2009 9:34:04 PM
333 Green 06/01/2009 11:15:55 PM
333 Green 06/01/2009 09:44:12 PM
How I want the data to be returned from query, only showing the last room assigned to each person.
111 Green 06/01/2009 11:10:45 PM
222 Yellow 06/01/2009 9:34:04 PM
333 Green 06/01/2009 11:15:55 PM
There really are only two ways to achieve what your after.
You create a correlated sub-query. To do this, you will need to manually modify the SQL and link your subquery back to the main query. or you can create a filter dimension in your universe that does the actual subquery and correlation.
You get a stored procedure created that allows you to pass an id and that id will return the max date for that id, then in your universe you create an object that calls the stored proc passing it the id. Then in the report, you create a condition where your date equals the date returned by your stored proc.
Option 2 is probably the easiest, its also really fast, but if you can’t created the stored proc. it becomes an issue and then option 1 starts to look a lot better.
Hi Rana,
I tried this and it seemed to work until I tried to filter and there is only a 0 option - even though in the report I have one row with a 1 in it - if I try and filter there is only an option to choose 0.
Can you explain this please?
Thanks
Sarah
As I stated in my reply, when trying to get back a max date back for an item which can have many records, I’ve found the easiest thing to do is to have a stored procedure created.
Create a dimension in the universe that calls that stored proc and pass it the code. The return value is the max date, then in your where you can say where ‘Date = (new dimension)’
It really is very simple and it is also very fast.
But with regards to the issue you are having, I’ve had it in the past and the method I’ve described above is generally been the best way and most accurate way around it.
Creating a proc is actually quite simple. Using a Having isn’t going to give you what you want. You really only can use this or the correlated sub-query which you need to do manually
(BOB member since 2004-03-22)
(BOB member since 2008-07-08)
(BOB member since 2005-08-22)
(BOB member since 2006-07-28)