How to perform MAX date in data entity

(1) Share

Report

Posted on by Alex C

Hello,

I am attempting to create a data entity with the SysUserLog table to export the latest login date for a user (createdDateTime field). I am unsure how to perform the MAX date with a data entity to represent the following SQL query:

select UserId, Max(createdDateTime) AS 'LastLogin'
from SYSUSERLOG
group by UserId

Here is how I made the structure of the entity:

Is there a range I can apply for the MAX(createdDateTime) or should I go via a SysComputedColumn?

Thanks

I have the same question (0)

All responses (4)

Answers (1)

Layan Jwei 8,133 Super User 2025 Season 2 on at

Like (1)

Report
Copy link

Link copied!

Hi,

Did you check the aggregate property on the field in the entity where you can choose "Max" and see if it helps?

Was this reply helpful? Yes No
Alex C 84 on at

Like (0)

Report
Copy link

Link copied!

@Layan Jwei
Do you have an example of what you mean? Not sure I understand...

Thanks

Was this reply helpful? Yes No
Verified answer

Layan Jwei 8,133 Super User 2025 Season 2 on at

Like (2)

Report
Copy link

Link copied!

Hi Alex,

Create a view with SysUserLog table. Add the field UserId and CreatedDateTime.
Put userId in the groupBy node -- just as u did in the entity

go to the createdDateTime field and change the aggregation property to Max.

Now go to your entity and add this view as a datasource instead of the table.

If you go to SQL, this view will give you this query:
SELECT MAX(CREATEDDATETIME) AS CREATEDDATETIME1, USERID, PARTITION, 1010 AS RECID
FROM dbo.SYSUSERLOG AS T1
GROUP BY USERID, PARTITION

Which is what you exactly want :)

Thanks,
Layan Jweihan
Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future

Was this reply helpful? Yes No
Andrew Baker Idahoan 7 on at

Like (0)

Report
Copy link

Link copied!

**EDIT**
Solved: I had to create a data view with the aggregation and then use that view in the entity instead of sysuserlog table itself.

**Original:
I'm trying to do the exact same thing, (SysUserLog and all), but Aggregation property is not available.

What am I missing?

Was this reply helpful? Yes No