Multi Field Combination for Mix / Max Date

Question

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
 useridactivityactivity date stamp1pick3/1/15 9:00 AM1pick3/1/15 9:15 AM1pack3/1/15 11:15 AM1pick3/2/15 11:15 AM1pack3/2/15 11:15 AM3pick3/1/15 9:00 AM3pick3/1/15 9:15 AM2pack3/1/15 11:15 AM2pick3/2/15 11:15 AM4pack3/2/15 11:15 AM

Any help would be greatly appreacited.

Kevin

kane_glendenning · Accepted Answer

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