Days between two dates broken by year.

[Enigma20xx]

Giving two dates from different years, I`d like to populate a child table with one record for each year (not each 365 days) but year “number‟, counting then, the days of each year.

Let`s say:

Parent table.
start_date 10/21/2020
end_date, 02/15/2022
Total days: 483
-I got this solved-.

I know I have to make a script to populate the child table, but I don`t know how to “extract/count‟ that are 3 different years in the dates given and count their days.

Child table (3 records).
72 days, year 2020
365 days, year 2021
46 days, year 2022

As always, I appreciate any advise.

[comment]

Make your script loop until the year of $start_date is equal to the year of $end_date

Within the loop, calculate the difference between $start_date and the first day of the following year and create a record. Then set $start_date to the first day of the following year.

After exiting the loop, create a record for the remainder.

 

Edited September 21, 2022 by comment

[Enigma20xx]

Thank you very much comment.

I think I followed the idea (sure "over engineered") but I could make it work.

I really really apreciate the help.

 

I attach a screenshot of my script so if it can be helpful to someone.

[comment]

Hm. I believe it could be shorter:

If [ Child::ParentID ] 
   Beep
Else
   Set Variable [ $parentID; Value:Parent::ParentID ] 
   Set Variable [ $startDate; Value:Parent::StartDate ] 
   Set Variable [ $endDate; Value:Parent::EndDate ] 
   Go to Layout [ “Child” (Child) ]
   Loop
      Exit Loop If [ $startDate > $endDate ]
      Set Variable [ $year; Value:Year ( $startDate ) ]
      Set Variable [ $endOfYear; Value:Date ( 12 ; 31 ; $year ) ]
      New Record/Request
      Set Field [ Child::ParentID; $parentID ]
      Set Field [ Child::Year; $year ]
      Set Field [ Child::NumberOfDays; Min ( $endOfYear ; $endDate ) - $startDate + 1 ] 
      Set Variable [ $startDate; Value:$endOfYear + 1 ]
   End Loop
   Go to Layout [ original layout ] 
End If

 

[Enigma20xx]

As always, great help comment.

Works great. And I'm still trying to figure out how can you do it so simple.

On 9/22/2022 at 6:40 PM, comment said:

    Set Field [ Child::NumberOfDays; Min ( $endOfYear ; $endDate ) - $startDate + 1 ] 
      Set Variable [ $startDate; Value:$endOfYear + 1 ]

This seems so obvious and logical when you see it... But I would never have thought of it.

I really appreciate the help. Thank you very much.

Edited September 25, 2022 by Enigma20xx