Max Date Issue

building reports webintelligence
1

All,

I need to return 3 pieces of data:
1 - Case No
2 - Date Time
3 - User ID

Several user will access a case but I need to return only the last user on each case. While I can write custom SQL and do a max on the date field because there are many users I will still receive more than one record. While this does not have to happen at the report level it is the preferred option.

Any assistance would be appreciated,

Markus Lowry :us: (BOB member since 2007-03-30)

2

Is your max only on date or date time ( ie some form of time stamp) ?

I would have though the chances of two users have the same date time on a case would be almost imposssible given time stamp normally goes down to seconds even milliseconds.

Nniixx :australia: (BOB member since 2009-09-02)

3

Sometimes I cheat Webi like this …

  1. Have your query as Max(Date) AS YourDate, User and Case

  2. Then in your report have a Section on Case.

  3. You then have Case, YourDate and User inside your section. Also a ‘hidden’ column =Max(Date IN Section).

The row where YourDate = Max(Date in Section) is the one you want to show.

  1. Create a variable dimension (MyFilter) that says If(YourDate = Max(Date in Section);1;0)

Now you can create a block filter to only show rows where MyFilter=1.

It will work for some of these sort of queries.

I use this technique when I have to display a summary page (for example one User per Case) and also a detail page (all transactions) in the same report - but don’t want to run two queries.

weaver (BOB member since 2008-10-17)

4

You want to display the User name those who was opened last time??, right?

then use =CurrentUser() function.

Thanks

BOCP (BOB member since 2007-07-02)

5

Nniixx,

Yes there is a time associated with the date.

Weaver,

This option that you provided will not work for my purposes. I need to have a single block containing a single recors for each case having the max date for that case.

BOCP,

I am pulling data from an outside system. The currnet user function that you mentioned is not an atribute in that system. The currentuser function is a BO function that tells you who is working on a document in BO.

Markus

Markus Lowry :us: (BOB member since 2007-03-30)

6

So doesn’t max(date time) give you a single value for each case or do some cases have the same date-time for different users ?

Nniixx :australia: (BOB member since 2009-09-02)

7

Hi Lowry,

Could you post some sample data with desired results.
So it will be easy to understand the problem and to suggest a solution.

Thanks,

DoSTh :india: (BOB member since 2007-05-31)

8

Here is an example of the data:

Case# UserID Datestamp
1 user1 1/1/2009 12:01
1 user2 1/2/2009 14:50
1 user3 1/2/2009 16:45

Now because I am pulling these 3 different fields the max date per case number will not work because I get a distinct record for each user that touched the case. I need to only get the final record that will include the case number, the user id, and the tinestamp.

Markus Lowry :us: (BOB member since 2007-03-30)

9

You could do it with a correlated subquery in a dervived table at universe level, but no idea at report level!

Debbie

Debbie :uk: (BOB member since 2005-03-01)

10

Here I guess , you need to show two records ?( as per your requirement )-

Case# UserID Datestamp
1 user1 1/1/2009 12:01
1 user3 1/2/2009 16:45

Please correct me if I am wrong.

aniketp :uk: (BOB member since 2007-10-05)

11

Aniket, I think he wants to show all maximums per user, per case.

i.e. the three records he listed.

Agreed, I have no idea how to resolve this on a report… :crazy_face: .

I would always put the correlated Subquery in a filter, infact I discussed how to do this, today, here:-

https://bobj-board.org/t/140568

This keeps your schema a bit more tidy than using derived tables…:).

Mak 1 :uk: (BOB member since 2005-01-06)

12

Thanks for that. I will have a look as it’s something I do quite a lot. I never use filters - I forget they exist!

Debbie

Debbie :uk: (BOB member since 2005-03-01)

13

All,

What I am looking fo ris the last user that touched each case. In the example that I provided the report should only return the last user for case 1. This would he user3.

Markus Lowry :us: (BOB member since 2007-03-30)

14

No problem, great for asking questions such as first Order, Last Order e.t.c.

If you use a sequence number in your fact table then you can use this technique to get first record, second record, last record…etc, by defining a series of filters and step through all the history, by a given dimension.

Easy to understand from a users perspective also…:).

Mak 1 :uk: (BOB member since 2005-01-06)

15

So you only want to get back

How about buidling a query with a sub select in it on date eg

select  Case#, UserID, Datestamp
from your tables
where Datestamp in
(select Case#, Max (Datestamp)
from your tables)

obviously the above is a rough guide and may not have the correct syntax.

Nniixx :australia: (BOB member since 2009-09-02)

16

Hi Markus,

Try with the following custom sql query and let us know…

select x.caseid, x.userid, x.datestamp
from
abc x where (x.caseid, x.datestamp) in (select y.caseid, max(y.datestamp) from abc y group by y.caseid)

Thanks,

Tims_Bo :india: (BOB member since 2008-07-26)

17

All,

Found an easy way of doing this. I create 2 queries.
The first query contained the case # and the Max(datestamp).
The second query contained the case #, datestamp and the userid.

I merged these two queries in the report and then used the Case# and Max(datestamp) from query 1 and added the UserID from query 2. While this may not be the best way it if functional.

To everyone that chimed in, Thank you for the assistance,

Markus Lowry :us: (BOB member since 2007-03-30)

18

No, you wanted to solve the problem on the report, glad it worked for you… :yesnod: .
Thanks for letting us know what you did…

Mak 1 :uk: (BOB member since 2005-01-06)