Jump to content

Days between two dates broken by year.


Recommended Posts

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.

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

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.

0.png

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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 by Enigma20xx
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.