Finding difference in dates , group by another column

[sriniprad08]

Hi Team,

I have situation where we have to difference between two dates group by id column. The results should be another column interms of dates,

The questions are 1) and 2)

Please let me know your thoughts.

id	start date	end date
3663	12/13/2002	12/30/2021
3663	1/15/2003	12/20/2002
1234	4/8/2003	12/31/2003
1234	7/2/2003	12/31/2020
1234	8/6/2003	9/30/2003
3663	8/6/2003	9/30/2004
2143	8/6/2003	1/30/2004
2142	8/6/2003	2/27/2004

Thanks,

Sriniv

[atcodedog05]

@sriniprad08 

Great scenario do have any expected output. The logic is only partially clear😅

[Qiu]

@sriniprad08 
Maybe a bit off on the details but should give the idea

[sriniprad08]

Thank you @atcodedog05 

Please find below the potential output.

Scenario A - Ouptut			Scenairo B - output
id	No of months/days		id	No of days/mnths
3663	23			3663	23
1234	22			1234	25
2143	300			2143	22
2142	20			2135	21

Scenario A- The logic is try to get the difference of two dates by the id.

Scenario B - This is little different. Wherein we take the max of column B  and take the difference from column C again group by id.

The idea is to see which ids are greater than certain threshold interms of no of days or months.

Thanks

[sriniprad08]

Hi @Qiu,

Thank you for the solution. it looks good. One thing sorry i missed was i need max of start date for that particular id not for the entire column. how can i go about this?

Actually it should take the max of start date column for that particular id and subtract with the current date not the end date.

cheers,

Srinivas

[atcodedog05]

Hi @sriniprad08 

If your dates and expected output the right numbers or just random numbers.

Because for your first row

12/13/2002 - 12/30/2021 cannot be 23 months it will be more than that

and 

1/15/2003 - 12/20/2002 duration is 26 days.

[Qiu · Accepted solution]

@sriniprad08 
Add ID to the sorting and sample the fist by grouping of ID should do it.

[sriniprad08]

@atcodedog05 

They are random numbers . I provided it as a sample.

Thanks,

[sriniprad08]

Thank you @Qiu  for your help.

I will come back if i face anything in real data.

Cheers,

Sriniv

[atcodedog05]

Cool anyways 

Glad its solved 🙂

Kudos to you @Qiu