Show "Y" in MAX DATE column for the maximum date for each user

[wonka1234]

Hi,

How can I create a max_date and min date for each Account ID?

The logic is ,  For each Account ID, where file_date_with_days is greater then the action date, show the minimum date with "Y" and show the maximum date with "Y". IF the File_date_with_days is less then action date show blank in the max and min columns.

These dates can range differently for each user.

Sample data (MAX and MIN columns here are expected outputs). Please also note , there are many more columns and account_ids in my actual dataset.

Account_ID	c_Action_Date	File_date_with_days	MAX_DATE	MIN_DATE
username5	6/27/2022	10/13/2022		Y
username5	6/27/2022	10/13/2022		Y
username5	6/27/2022	10/13/2022		Y
username5	6/27/2022	10/13/2022		Y
username5	6/27/2022	10/13/2022		Y
username5	6/27/2022	10/13/2022		Y
username5	6/27/2022	10/13/2022		Y
username5	6/27/2022	10/13/2022		Y
username5	6/27/2022	10/13/2022		Y
username5	6/27/2022	10/13/2022		Y
username5	6/27/2022	11/13/2022	Y
username5	6/27/2022	11/13/2022	Y
username5	6/27/2022	11/13/2022	Y
username5	6/27/2022	11/13/2022	Y
username5	6/27/2022	11/13/2022	Y
username5	6/27/2022	11/13/2022	Y
username5	6/27/2022	11/13/2022	Y
username5	6/27/2022	11/13/2022	Y
username5	6/27/2022	11/13/2022	Y
username5	6/27/2022	11/13/2022	Y

any help would be appreciated.

[binuacs]

@wonka1234 One way of doing this

[wonka1234]

@binuacs  thank you for this. Not sure if this includes the logic that these max and min dates have to be greater then action date otherwise "". 

I guess my sample dataset doesnt have this built in.

[binuacs]

@wonka1234 Yes, I removed the MAX_DATE and MIN_DATE from the initial file you provided, and these fields (MAX and MIN) in the output are calculated fields

[wonka1234]

@binuacs but why no reference to action date?