Jump to content

Count dates in all range


Jurandi Frana

Recommended Posts

How to count all records where the reference date is in the range [start; end] for all [start; end]

 

Table entry:

 

 

 

[start]

[end]

[ref]

 

 

 

 

05/01/2020

06/01/2020

05/20/2020

 

 

05/01/2020

07/01/2020

12/01/2020

 

 

12/20/2020

12/25/2020

06/10/2020

 

 

 

 

Out:

 

 

 

 

[start]

[end]

[ref]

[count]

 

 

 

 

05/01/2020

06/01/2020

05/20/2020

2

 

 

05/01/2020

07/01/2020

12/01/2020

0

 

 

12/20/2020

12/25/2020

06/10/2020

1

 

 

 

 

example:

[ ref]05/20/2020 is in [05/01/2020; 06/01/2020] and [05/01/2020;07/01/2020] , count 2

Link to comment
Share on other sites

I don't see how this could work witha calculated column in a table like this. Because you want to check the value in ref column of row 1 against all other rows.

I suggest that you make one table with start and end, then cross join a table with your ref column. Then make a simple calculated column that returns 1 if your ref date is between start and end date. And then summarize your data with a cross table.

Another possibility is to write a data function that takes each ref value and then loops thru start/end date.

Let me know when you have further questions

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
×
×
  • Create New...