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
Solved! Go to Solution.
@sriniprad08
Maybe a bit off on the details but should give the idea
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
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
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.
@sriniprad08
Add ID to the sorting and sample the fist by grouping of ID should do it.