Multi Field Combination for Mix / Max Date

[kpurcell]

Hello,

I was hoping if someone could help if there is an easier way to do this.

I have a table with userid, activity code and activity date stamp.  Is there a formula that would give me min activity date and max acivity date for each uniquie userid, activity code for each day.  For example
 

userid	activity	activity date stamp
1	pick	3/1/15 9:00 AM
1	pick	3/1/15 9:15 AM
1	pack	3/1/15 11:15 AM
1	pick	3/2/15 11:15 AM
1	pack	3/2/15 11:15 AM
3	pick	3/1/15 9:00 AM
3	pick	3/1/15 9:15 AM
2	pack	3/1/15 11:15 AM
2	pick	3/2/15 11:15 AM
4	pack	3/2/15 11:15 AM

Any help would be greatly appreacited.

Kevin

[kane_glendenning · Accepted solution]

Hi Kevin,

To do this, you probably want to use a Summarise tool. The Prep work that will be needed is getting a field for each day. The summarise tool will do the min/max, and then a join tool will append to the original data if that was required.

A formula tool with the following will give you the Day field ?DateTimeTrim([Activity Date Stamp],'day')

The Summarise tool will just need Group By: UserID, Activity, Day, Min (Activity Date Stamp), Max (Activity Date Stamp)

If you want to append your results back on, then use a Join tool on all the relevant fields to append the Min & Max fields.

Kane

[kpurcell]

Thank you Kane.  I will try that approach.

Kevin