distinctcount(date,group) will give a count of the value in the field

 

What mrstacy wanted was a distinctcount of the range of days covered by the enrollment.

using the opriginal sample data of

TYPE          START             END

algebra     1-1-2012        1-10-2012

science     1-4-2012        1-11-2012

english      1-9-2012        1-13-2012

 

distinctcount of the startdate would be 3

distinctcount of the enddate would be 3

but the request was for a distinct count of every day that was "covered" by an enrollment into a class.

13 days (1-1-2012 to 1-13-2012)

using a claendar table for every day lets the join do all of the work in finding any gaps or overlaps.

HTH

 

 

 

 

Thank you so much for detailed explanation.  I thought series of dates, but it was range dates.

 

 

Help :   I would like see the threads which I answered through search.  Because, I am answering some threads and not able to follow them or answer futher.