Today real days

[Infinite]

Hello! I have 2 fields, Start Date and End Date. I am trying to figure out the "true" days between them. The problem is, im using date diff, but most of my days have other dates that happen on the same, or during the other show dates. Am am trying to figure out, how many days do we "truly" go to shows in a year. Thanks!

 

[sneuberg]

I could imagine that given a start date and end date that you might want to know the number of days between them that are not covered by other intervals. For example lets say I have a table with one interval between Jan 1, 2015 and Jan 2, 2015 and second interval between Jan 10, 2015 and Jan 15, 2015. Then if I ask how many days between Jan 1, 2015 and Jan 20, 2015 the answer would not be 19 but 12 because of these intervals.

I am not saying I know the answer but is that the question?

 

[Infinite]

Kinda.

I have Show 1, its dates are 1-1-2015 to 1-3-2015.

That is a 3 day show.
Now,

I have Show 2, its dates are 1-2-2015 to 1-3-2015

That is a 2 day show. My current data would say thats 5 days, when in the end, its just really 3 days.

 

[sneuberg]

Interesting challenge. May take a while,but I'll see if I can come up with something, probably just calculate it brute force. If you don't hear anything from me by tomorrow I didn't get it at all.

There's probably an existing algorithm out there for this. Maybe someone else knows of it.

 

[plog]

I have Show 2, its dates are 1-2-2015 to 1-3-2015

That is a 2 day show. My current data would say thats 5 days, when in the end, its just really 3 days.

Huh? Context please.

"That is a 2 day show." -- so far so good, that seems right.

"My current data would say thats 5 days" -- now you lost me. What is "your data". The DateDif calculation?

", when in the end, its just really 3 days." -- Now I think your just screwing with us. How's it really 3 days? What's the "really" in that sentence suppose to mean?

You started the explanation matter of factly stating 2 days, but end the explanation with an even more matter of fact that it is 3. Completely lost.

 

[Infinite]

sneuberg got it, its not that hard.

The date diff will say its a 5 day show because that is how many days are between those 2 shows dates. But, I dont want that, what I want, is those 2 days overlap, and I just want the total ones.

If the show dates were 1-1-2015 to 1-31-2015 and the next show was 1-15-2015 to 1-31-2015 it would be a total of 47 days according to the Datediff. But, I dont want that, I want it to tell me its only been a "true" total of 31 days, the month.

Now I think your just screwing with us.

No, im not "screwing" with you. That would be pointless, and get Me no were. I am trying to figure out something, not trying to waste any ones time.

You started the explanation matter of factly stating 2 days, but end the explanation with an even more matter of fact that it is 3. Completely lost.

Your correct. I am correct it what I was trying to say.

1-2-2015 to 1-3-2015

(Lets call that Show 1)

How many days are there? 2 days.

1-1-2015 to 1-3-2015

(Lets call that Show 2)

How many days are there? 3 days.

A total of......? 5 days.



Now, the dates between Show 1 and Show 2 overlap. I dont care about Show 2s dates. I care that in that weekend, we were at shows for 3 days. I dont want to know that in 3 days, we were at 5 days of shows...wait, what? That is what im trying to NOT do. It should be, in that weekend, we were at a total of 3 days of shows. Even though it was 5 "days" of the show, its was truly only 3 days, as that is how many days from the start, to the end.

I just dont know how to tell it to do what I want.


If you still dont understand, just say so and I will try again.

now you lost me. What is "your data". The DateDif calculation?

Yes.

How's it really 3 days? What's the "really" in that sentence suppose to mean?

That time that passes on a watch, computer, phone, etc, etc.

You started the explanation matter of factly stating 2 days, but end the explanation with an even more matter of fact that it is 3. Completely lost.

Hopefully something I said has cleared it up somewhat, or fully.

 

[sneuberg]

1. Start with a blank calendar.
2. For each day Infinite was at a show(s) put a X on the calendar. Only one X per day.
3. For a given input range count the Xs

That's what I understood.

I wonder if there's something more elegant than doing this brute force.

 

[sneuberg]

I've attached a database which has the functionality I believe you are looking for. It has a table name Show. The form Shows allows you to add shows to this table. The form Calculate Dates allows you to calculate the "true days".

I haven't put in any error checking and this isn't very pretty but it might give you something to work from. Also there's nothing elegant or efficient about this solution. If you put enough shows in this and have a large time range this will get slow.

I'll try and see if I can come up with something better than this, but no promises.

 

[sneuberg]

Re: Today real days - Combining intervals of dates into non-overlapping intervals

I think I got it. The thing that occurred to me was that if these intervals didn't overlap you could used datediff plus 1 on each of them and just add them up. So if you could combine these intervals into non-overlapping intervals you could do this too. It wasn't hard to find a algorithm for that and I adapted the one on http://www.geeksforgeeks.org/merging-intervals/ to this. I believe the algorithm is about efficient as you are going to get and performance shouldn't be a problem as it might have been with the brute force method.

The attached database has a module named "True Days Calculation" which has the code you will want. The main function in this code is GetTrueDays which takes a table name, the name of the start date field, the name of the end date field, the start date, and the end date as parameters. I believe this is generic enough to plug into whatever you already have. I also put in some error check that checks if dates are in the correct order.

If you have any problems or questions please let me know.

 

[Infinite]

Sorry for the long wait! Been really, really, really, busy. Just got around to using it, and figuring out how to. It is exactly what I wanted. Thanks a TON Sneuberg! That will help a lot with being able to tell how many real days we have been doing shows and such. Thanks again!