Numbering based on an interval - Linq/Programming Logic required

Help
, ,
1

I have below table. For each ID, There is a Start Date. I need to get the dates which fall into an year interval from the Start Date - (ie: Start Date + 365 days). If any row of records fall into the interval, I will number it as 1. If it doesnt fall into the interval, I will have to add 1 year to the Start Date and the interval and keep on checking until the record ends for that ID. If any row of records fall into the 2 nd interval, I will number it as 2.

If No records fall into any interval, we will not put any number. We will have skip that and increase the interval for the next set.

In the same way, For every ID, I will have to set Number from 1 to n.

Please suggest me how can this be achieved.

Notes: There whole requirement is bit more complicated. As long as I can achieve this logic, I will be able to manage rest of the things.

Logic.xlsx (10.1 KB)

Input DT:

ID Dates Start Date
1001 20100505 20100301
1001 20110307 20100301
1001 20110419 20100301
1001 20200106 20100301
1002 20100804 20100501
1002 20100904 20100501
1002 20110706 20100501
1002 20120806 20100501
1002 20181105 20100501
1003 20161124 20161101
1003 20200507 20161101
1003 20200508 20161101
1004 19950608 19950501
1004 19980412 19950501

The Output which I need:

ID Dates Start Date Output
1001 20100505 20100301 1
1001 20110307 20100301 2
1001 20110419 20100301 2
1001 20200106 20100301 3
1002 20100804 20100501 1
1002 20100904 20100501 1
1002 20110706 20100501 2
1002 20120806 20100501 3
1002 20181105 20100501 4
1003 20161124 20161101 1
1003 20200507 20161101 2
1003 20200508 20161101 2
1004 19950608 19950501 1
1004 19980412 19950501 2
2

get number of days between your dates using below
IntTotalDays = (DateEndDate - DateStartDate).TotalDays

Divide it by 365 and apply ceiling function

1 Like
3

@Codegias I never thought it would be able to do so simple. Thanks a lot. :slight_smile:

4

No issues, happy automation!

1 Like
5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.