how to calculate friday, saturday, sunday between two dates 5

[fanlinux90]

Between the dates 11/28/2023 and 12/02/2022 I know how many days there are. DATE(2023, 11, 28) - DATE(2022, 12, 02), how can I calculate how many Fridays, Saturdays, and Sundays there are between those two dates?

 

[Mike Gagnon]

Criss
Maybe the fact that you are in Germany (I think) and he is in Columbia (I think) there may be a time difference.



If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Chris Miller]

Might be, Mike Gagnon, but at the start of the thread we werre both online at the same time.

Chriss

 

[Chris Miller]

Just to show how to count "more professionally":

Create Cursor amonth (dDate D)
Index On Dow(dDate,6)=1 Tag fri binary
Index On Dow(dDate,7)=1 Tag sat binary
Index On Dow(dDate,1)=1 Tag sun binary

nYear= Year(Date())
nMonth = Month(Date())
For nDay=1 to 31
   d = Date(nYear,nMonth,nDay)
   If Empty(d)
      Exit
   Else
      Insert into amonth values (d)
   Endif
EndFor

clear
? 'days of month:',reccount()
Count for Dow(dDate,6)=1 To nFridays
? nFridays
Count for Dow(dDate,7)=1 To nSaturdays
? nSaturdays
Count for Dow(dDate,1)=1 To nSundays
? nSundays

But as I already stated, you can compute these also without counting, by testing the fifth fri,sat,sun is within the month or in the next (another) month.

There often is a misunderstanding about binary indexes and how they can speed up Rushmore, because they can't be used for SEEKing and SET ORDER, but by the help they are helping to build Rushmore optimization bitmaps faster, if they cover more than 3% of the data, which they do for the weekdays, as they cover 1/7 (>14%) of all data. If you only put in fri/sat/sun each of them would even be a third of all data.

They won't show up in the Rushmore "showplan" SYS(3054) shows, as that functonality wasn't updated to show what Rushmore does with binary indexes (which is the most scandalous part of that update of index types). You can also do a "normal index" with integers on Dow(dDate,2), for example, in which case the index nodes would be integers from 1 to 7 with 1 for monday and 5,6,7 for fri,sat,sun, then SYS(3054) will also report the usage of that index, if using the query where conditions Dow(dDate,2)=5,6, or 7 or Dow(dDate,2)>4 for an overall count of all fri/sat/sun.

It can pay and be faster (or fast enough) in comparison to computing this number the way I sketched above, if you have a calendar table anyway, which you may have for many more reasons than counting or categorizing days. Some people consider it a waste and may only have a table of special days like holidays, instead of all days. I looked in more detail into Rick Borups library of date methods and see he doesn't even use meta data, he has some specific holidays hardcoded in CASE statements. But there are many more uses for a calendar table than categorizing days.

If you only want to count some days in any timespan it pays to calculate without data as the basis, as Griffs first answer already showed its mainly the number of weeks that matters. You can then also only consider the days before the first full week and the days after the last full week of the timespan and using DOW() means this also won't need data of these days, just a little DOW adjustment calculation, so you never need to count but can fully compute these. And for very long timespans it pays to compute anyway, even if you can build up a Rushmore optimization bitmap about tens of thousands of days fast, it's always slower than just adding and subtracting a few integers, even if computing these integers also has some ifs or IIFs conditions to check. It has a very low importance though, if all timespans are within say the duration of a insurance policy or whatever is within a lifespan of a person, as we talk about at most 30,000 days, each 10,000 days are about 27 years. And you don't need a table per person/employee/customer, you need one calendar table for all and can have as many categorizing indexes as you want.

Also, even if you would count optimized as in my code sample, if the data is just for counting you could also skip any days that are not fri,sat,sun (Edit: I added that above already) and have even less data and even a higher percentage of rows in the indexes. See the help of the INDEX command and its sections about binary indexes.

For computing and also for counting there even is one more correlation you could make use of: If the number of Sundays and Fridays in a month is the same (4 or 5) the number of Saturdays is also that. So that could alo be a shortcut for most months. Another special case is for any normal February with 28 days, for which the counts of Fri/Sat/Sun are 4, each.

All in all, it's not necessary to optimize the hell out of this, the more specialized your code is, the less easy it becomes to fit in new conditions, the counting of data is most versatile if you have other conditions in the future, as that can mean to only change data without needing code changes. For example, Rick Borups code needs adaption to local holidays, and I've seen holidays evolve within my lifespan, new ones added, others removed, besides regional holidays can depend not only on country but states, regions/areas within a country, too. So calendar data is a very good concept, as you can also easily let your users/customers adjust data, much easier than adjusting code, which they usuall need you for. So it's less customer friendly to do the optimization with code and will only pay off, if there really is a big advantage in computation time. This is not a black and white decision to make, it depends.

Chriss

 

[Chris Miller]

On another aspect: I can understand Rick Borups decision to not make his holidays depend on data. If you design a class that would also depend on data, either you need to create it with init and then need to ensure it's not fiddled with or only changed in a valid manner. You can put it into the responsibility of the class user, too. Just like any change of code or subclassing and overriding is the responsibility of the programmer doing so. but you could also have a hook for making the class customizable. Its very likely to be a library programmed for own usage within the bounds of what's necessary in the USA or UK or both, providing it "as is". Nothing wrong about that and overall it's still a big contribution.

Like always just taking in some library and using without inspeciting it in any detail you'll only partly profit from using soeone elses work you spare to do yourself. You lose full control and can have wrong epectations of whatever library.

And I know there never is a complete status, even if you think of many more things that are relevant for the most general usage of your library, there always will be ideas for progress and corrections.

Chriss

 

[Mike Lewis]

Like always just taking in some library and using without inspeciting it in any detail you'll only partly profit from using soeone elses work you spare to do yourself. You lose full control and can have wrong epectations of whatever library.

Chris, I agree with that. My point in suggesting that the OP look at Rick Borup's work was specifically so that he could learn about VFP's date handling and get ideas for ways of solving his own problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

One more spotlight:

Count for Dow(dDate,6)=1 To nFridays

That's the actual count code, just this oneliner, data should exist and an index that flags exactly the records you wwant to count. Going deep into Rusmore, it might even just read a count of index nodes that is stored within the CDX. So this can be an operation that's not even remotely counting, just reading one value from the head of an index tag.

Chriss

 

[mmerlinn]

There have been numerous threads about this issue over the last several years. I have never chimed in until now simply because spending OVER TWO HOURS to post EACH reply is not time effective for me. However, today I decided to spend four hours to post a FAQ and another two hours to post this reply.

See faq184-8776 for the fast and simple code that I use whenever I have a problem such as this.

P.S. I takes me two hours per reply because there is NO submit button on any Tek-Tips page which requires me to ADD a submit button before I can post. Been that way for years and has prevented me from adding my two cents to the forum. Bottom line is that someone fixed that which was not broken and now it is broken.

Needless to say, there will not be any followup from me - I will simply keep lurking without commenting.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, whom are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[Chris Miller]

Well, hello there.

there is NO submit button on any Tek-Tips page

What's up with that? I can only report one case in which this was true for me, when I edited a post right after editing it, the edit page came up with no submit button, instead it had the report/edit/delete links. So there could be something faulty with the website system or your account.

Does that depend on browser? Your account? Your location? Have you tried creating a new profile? Please don't take two hours to respond. If you use a new browser (with no cookies from tek-tips), create a new account, you have a big chance to get in and respond much faster.

Chriss

 

[Chris Miller]

Well, my answer to the 'S----FS' mask is

Index On Dow(dDate,2)>4 Tag frisatsun binary

But more generally you can have flags in your calendar data that are just a logical field, like suchaday L default .F.

Index On suchaday Tag sad binary

Which will not need to fulfill a 7 day pattern.


Chriss

 

[Mike Lewis]

Ffanlinux,

It's now been a week since you posted your question. You have received a number of replies, including several suggestions for actual code for you to try.

It would be really helpful at this point if you could come back here and give us some feedback on these replies. Were any of them useful to you? Have you now solved the problem? If so, it would be courteous to let us know, if only so that we don't have to spend any more time looking for a solution. And any possible solution could also be helpful to other people with the same problem.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

I agree, Mike.

Let's summarize:

[ul]
[li]Fanlinux asked for the numbers of Fridays, Saturdays, Sundays. More distinctly in his second post, and in an example he needs each of these counts indvidually. And also, in more detail, not in any time span but for months.[/li]
[li]Griff answered at first with an estmate of 3 days per week in the overall days and then offered a count scan loop[/li]
[li]I gave a description what's possible to do with DOW and calculating whether the fifth Fri/Sat/Sun is in another month[/li]
[li]Mike also gave a recipe to combine estimation with a closer look at the first and last days of a tinmespan which are not whole weeks, that's possible again with DOW[/li]
[li]I added sample code for "counting" with the use of an index. By the way VFP9 now also makes use of filtered indexes, too. See the what's new in 9 section of VFP9s help. A focus there is on deleted records and finding out actual reccount without deleted rows, but that just as another aside.[/li]
[li]Mike pointed to a library from Rick Borup. To showcase many more things on the topic of date arithmetic with VFP, even if not fanlinux specific problem.[/li]
[li]Mmerlin came to the forum to once more shine with a oneliner taking the problem in any long timespan with a filter that can adapt this to any week pattern. With the astonishing side story of how tek-tips fails for him and what effort he put into it to post it.[/li]
[/ul]
Aside of these core results on topic, there was lots of talk about you, fanlinux. Let me say that any assumtions I made are still just assumptions, I'll not expect ou to take position to the accusations. You see, people also defended you, mainly on the aspect of not being able to react timely. Mike Lewis, though certainly winning any popular vote about politeness or just objective statistical counts, still also asked you to please give a feedback. I can totally understand why you just never cam back to react, I hope you have picked up a solution or are happy with anything else you decided to follow up, there also are other VFP related forums on the internet.

MMerlin, I would like to address you onnce more. Please don't take my response to your latest magnificiently short solution FAQ as rejecting it, I guess your solution beats one of an expelled member from whom all posts and also FAQs are deleted. I also still have his weekdays/workdays calculation, which like your FAQ works on Mikes description of whole weeks plus stubs of the weeks at the start and end. It's quite ingenious in your case to make use of strings there. I still think there are pros and cons about all solutions and not one alltime best, taking into account aspects I pointed out.

You, both you, MMerlin, and fanlinux, are still welcome. For Mmerlin I assume the two hours you need to make just one post are due to Chinese or a similar state's censorship? Perhaps involving to even get to international internet and then tek-tips. The disfunctionality you see seems also be related to the alternative connections you have to make, but again, just assumptions. I hope you'll find a more practicable way to come back in the future.

Chriss

 

[Chris Miller]

One more spotlight:

Like anything having a FOR clause, also [tt]INDEX ON ... FOR <condition>[/tt] can be optimized. If you have a date field dDate and an index on dDate and on DOW(dDate,2), you can create a further index tag or even a sseparate IDX or CDX with FOR dDate Between startDate and Enddate and DOW(dDAte,2)=5 and then counting becomes just reading the count of that index tag header. So even when the time spans vary wildly, as long as they're within your calendar data you can make individual indexes per use case instead of a query to get a count result, too.

If you make such special indexes permanent tags of the main CDX of a table you always keep this indexes up to date and also the count. You likely will vary the month timespan, though. And that's where creating inddexes can become a norm instead of querying, too, using throw away separate CDX or IDX files. You just need the base indexes on the dDate and one DOW() variant. Depends on your culture, perhaps whether you prefer numbering days starting from Monday or starting from Sunday.

Chriss

 

[Mike Gagnon]

Criss

For Mmerlin I assume the two hours you need to make just one post are due to Chinese or a similar state's censorship?

mmerlin is located in Washington USA according to his profile.



If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[GriffMG]

@mmerlin

I have given you a star for that contribution. A magnificent bit of code indeed, thank you.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Mike Lewis]

Mmerlin, I agree with Griff. It's a nice simple bit of code that does the job well. Have a star from me as well.

It's also good to see a new FAQ being posted: one of the first for a dozen years or more. I hope it will encourage more folk to do the same,

Regarding your lack of a Submit button, I am mystified by your statement that you have add the button yourself. I can't imagine how you do that. And have you reported this is a bug?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Washington USA according to his profile.

Using a VPN might be part of the two hour procedure to get through to tek-tips. Very likely, isn't it?
I know a lot of Chinese developers from times I used NNTP/Newsgroups mainly, and IIRC mmerlin was also within Newsgroups.
Even if I err about mmerlin specifically, the participation of Chinese FoxPro devlopers in the comunity has dropped quite a long time ago, and that may correlate to the time Microsoft stopped supporting NNTP and only introduced a bridge of NNTP to MS forums, which, as you know, also have ceased to include Foxpro as a topic by now.

I don't see how a Washington citizen would have these difficulties.

Also, I never stated my location in the creation of a user profile, so it's very likely geolocation by IP, that's not guaranteeing anything.

Chriss.

 

[mjcmkrsr]

Hi,

The demo code below picks up mmerlinn's approach and enhances it (total of selected days + total of each selected day)

Enjoy

**********
* This demo code
* - calculates total and sub-total of selected weekdays in any date period
* - INCLUDES ending date
**********

goForm = CREATEOBJECT("frmForm")
goForm.Visible = .T.
goForm.Show()

READ EVENTS

CLOSE ALL
CLEAR ALL 

RETURN

**********

DEFINE CLASS frmForm as Form

Caption = "Name and number of selected days"
AutoCenter = .T.
BorderStyle = 2
Width = 570
Height = 300
MinButton = .F.
MaxButton = .F.
Themes = .F.
ShowTips = .T.

	DIMENSION laSelection[7], laTotalDays[1]

	laTotalDays[1] = 0
	
		ADD OBJECT lblSDate as Label WITH Left = 12, Top = 12, Caption = "Start date"
		ADD OBJECT lblEDate as Label WITH Left = 114, Top = 12, Caption = "End date"
		ADD OBJECT lblSDays as Label WITH Left = 12, Top = 72, Autosize = .T., Caption = "Click to select day"
		ADD OBJECT lblTSDays as Label WITH Left = 216, Top = 72, Autosize = .T., Caption = "Total of selected days: "

		ADD OBJECT txtSDate as textbox WITH Top = 36, Left = 12, Width = 90, Value = {}
		ADD OBJECT txtEDate as textbox WITH Top = 36, Left = 114 , Width = 90, Value = {}
	
		ADD OBJECT lstList as ListBox WITH ;
			Top = 96, ;
			Left = 12, ;
			Width = 120, ;
			Height = ThisForm.Height - 24 - 96, ;
			ItemBackColor = RGB(0, 240, 240), ;
			Anchor = 7, ;
			RowSourceType = 1, ;
			RowSource = "Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday", ;
			ColumnCount = 1, ;
			ColumnWidths = "54, 0", ;
			Multiselect = .T., ;
			ToolTipText = " CTRL / SHIFT Click to multi-select ", ;
			IncrementalSearch = .T.
			
			PROCEDURE lstList.Click()
				FOR lnI = 1 TO This.ListCount
					IF This.Selected(lni)
						ThisForm.laSelection[lni] = ALLTRIM(This.List(lnI, This.BoundColumn))

					ENDIF 
				ENDFOR 
			ENDPROC 
		
		ADD OBJECT cmdDoit as CommandButton WITH Left = 216, Top = 12, Height = 48, Width = ThisForm.Width - 24 - 216, BackColor = RGB(0, 240, 240), ;
								FontBold = .T. , FontSize = 12, Caption = "Calculate", Anchor = 11

			PROCEDURE cmdDoit.Click()
				LOCAL liWeeks, liRDays, liTDays, lcSDay
				
				liWeeks = 0
				liRDays = 0
				liTDays = 0
				
				liTDays = ThisForm.txtEDate.Value - ThisForm.txtSDate.Value + 1
				
				IF ThisForm.txtSDate.Value = {} OR ThisForm.txtEDate.Value =  {} OR ThisForm.txtEDate.Value <= ThisForm.txtSDate.Value
					= MESSAGEBOX("Please fill in the dates correctly", 16, "Choose dates")

				ELSE 
				
					SET SAFETY OFF  
					ZAP IN csrSelectedDays
					SET SAFETY ON 
					
					FOR i = 1 TO ALEN(ThisForm.laSelection)
						IF VARTYPE(ThisForm.laSelection[i]) = "C"
							INSERT INTO csrSelectedDays VALUES (ThisForm.laSelection[i], 0)

						ENDIF 
					ENDFOR 
				
					liWeeks = INT(liTDays / 7)
					
					UPDATE csrSelectedDays SET iDays = liWeeks
					
					liRDays = liTDays - (liWeeks * 7)
					
					FOR i = 0 TO liRDays - 1
						lcSDay = CDOW(ThisForm.txtSDate.Value + (liWeeks * 7) + i)

						IF ASCAN(ThisForm.laSelection, lcSDay) != 0
							UPDATE csrSelectedDays SET iDays = iDays + 1 WHERE cName = lcSDay
						
						ENDIF 
					ENDFOR 
					
					IF RECCOUNT("csrSelectedDays") > 0
						SELECT SUM(iDays) FROM csrSelectedDays INTO ARRAY ThisForm.laTotalDays
						
						ThisForm.grdSelectedDays.Visible = .T.
						
						LOCATE 

					ELSE
						WITH ThisForm
							.grdSelectedDays.Visible = .F.
							.laTotalDays[1] = 0
						ENDWITH 
						
						 = MESSAGEBOX("Please choose at least one day", 64, "Choose days")

					ENDIF 

					FOR i = 1 TO ALEN( ThisForm.laSelection)
						ThisForm.laSelection[i] = .F.
					Endfor

					WITH ThisForm
						.lstList.Clear()
						.lstList.Requery()
						.lblTSDays.Caption = "Total of selected days: " + TRANSFORM(ThisForm.laTotalDays[1])
						.Refresh()
					ENDWITH 
				ENDIF 

			ENDPROC 
		
		ADD OBJECT grdSelectedDays AS Grid WITH ;
			RecordSource = "csrSelectedDays", ;
			ColumnCount = -1 , ;
			Left = 138, ;
			Top = 96, ;
			Width = ThisForm.Width - 24 - 138, ;
			Height = ThisForm.Height - 24 - 96, ;
			BackColor = RGB(0, 240, 240), ;
			DeleteMark = .F. , ;
			Enabled = .F. , ;
			Anchor = 15, ;
			Visible = .F.
	 
			PROCEDURE grdSelectedDays.Init
				 WITH This.Column1
				  .Width = 78			
				  .Header1.Caption = "Day"
				 ENDWITH

				 WITH This.Column2
				  .Width = 60			
				  .Header1.Caption = "Number"
				 ENDWITH

			 ENDPROC 
			 
	PROCEDURE Load()
		CREATE CURSOR csrSelectedDays (cName C(18), iDays I)
		
	ENDPROC 
      
	PROCEDURE Destroy()
		CLEAR EVENTS
	
	ENDPROC 
   	
ENDDEFINE 
**********

MarK

 

[Chris Miller]

Regarding your lack of a Submit button, I am mystified by your statement that you have add the button yourself. I can't imagine how you do that. And have you reported this is a bug?

I think it has to do with tek-tip opening a websocket. That way you post without a normal submit process and your new post is added without reloading the page. I think this does not work in cases you're not directly connecting but have only an indirect connection to a site.

Chriss