Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I have two tables data as below:
Table 1:
Id | Create Date (YYYY-MM-DD HH:SS:MM) | Closed Date(YYYY-MM-DD HH:SS:MM) |
D-1 | 2021-01-01 19:10:23 | 2021-01-10 20:10:23 |
D-2 | 2021-01-02 19:10:23 | 2021-02-10 19:10:23 |
D-3 | 2021-03-01 19:10:23 | 2021-03-10 19:10:23 |
D-4 | 2021-04-01 19:10:23 | 2021-04-11 23:10:23 |
D-5 | 2021-05-01 19:10:23 | |
D-6 | 2021-06-01 19:10:23 | 2021-06-20 23:10:23 |
D-7 | 2021-07-01 19:10:23 | 2021-07-02 20:10:23 |
D-8 | 2021-02-11 11:10:23 | 2021-03-11 19:10:23 |
Table 2:
Sprint | StartDate(YYYY-MM-DD HH:SS:MM) | End Date(YYYY-MM-DD HH:SS:MM) |
S1 | 2021-01-01 00:00:00 | 2021-02-10 23:59:59 |
S2 | 2021-02-11 00:00:00 | 2021-03-10 23:59:59 |
S3 | 2021-03-11 00:00:00 | 2021-04-10 23:59:59 |
S4 | 2021-04-11 00:00:00 | 2021-05-10 23:59:59 |
S5 | 2021-05-11 00:00:00 | 2021-06-10 23:59:59 |
S6 | 2021-06-11 00:00:00 | 2021-07-02 23:59:59 |
From the Table 1 i will get the difference of (Closed Date- Create Date) in Days and i should distinguish them in to sprints as : (Note: This which i have done manually)
Days(Closed Date-Create Date) in Days | ||
10 | S1 | |
39 | S1 | |
9 | S2 | |
11 | S3 /S4 | 10+1 |
20 | S5 / S6 | 10+10 |
2 | S6 | |
29 | S2 / S3 | 28+1 |
The Output which i should get :
Sprint | Sum of each Sprint |
S1 | 49 |
S2 | 37 |
S3 | 11 |
S4 | 1 |
S5 | 10 |
S6 | 12 |
Note: Table 1 Data i will get from one of the source, Table 2 data is Inline Data.
Hi, you can use IntervalMatch to assign each row to and interval, you will have to choose if you want to assign them by Crate date or Close date, and use a scrit similar to the one in the help page: