How to calculate qty days between periods in the same month

[igorfv]

Hi guys,

I have a challenge to calculate qty days between periods in the same month. See the example below. Could help me, please?

Table 1
Doc number (Key)	Start Date
1	2023-01-01
1	2023-01-15
1	2023-01-25

Table 2
Doc number (Key)	End Date
1	2023-01-10
1	2023-01-20
1	2023-01-30

Result
Doc number (Key)	Start Date	End Date	Qty Days
1	2023-01-01	2023-01-10	9
1	2023-01-15	2023-01-20	5
1	2023-01-25	2023-01-30	5

Result
Doc number (Key)	Qty Days
1	19

Note: It's possible exists many Start and End dates in the same month or not.

Thanks,

Igor

[chandler-gjino]

take a look at this workflow and see if it helps
without a unique key between the two tables, it'll be hard to do a true join.
If we joined on doc number, because it's repeated, we'd come out with 9 records (each combination of start and end) -- however you could have a series of logic to remove the duplicates. 

I have one option joining on position, if that's possible for your needs. and the other option joining on doc num but removing the duplicates at the end

[rzdodson]

@igorfv posting one way to do this (see below and attached).

As an aside, I would recommend that there is some additional level of detail in the Doc number (Key) field as it can get confusing if you are attempting to join on record position. But, for this use case, the solution gets to the desired end state.

[igorfv]

Hi Guys,

Great! It's worked. Thank you for your support.

Igor Valle