casey-172678 (10/4/2012)
It looks to me like your example counts the last day in a consecutive day grouping as a visit. Here are some wrinkles:First, your data are pure dates. I see this sort of problem more often with input data that are date/time. Second, you are counting the visit on the last day. This is all well and good if you're selecting from an unfiltered data set. But what if you're needing to use this technique against a date range, say patient visits this month? How would you set it up so that your results from querying each month in the year will logically match the results for the whole year, etc.
I think the script actually counts the number of days that don't have a preceding day, so it should work correctly if you want to select ranges of days for reports.
So if you want the number of visits that started in Jan 2012 you would add
where tbl1.er_date between '1/1/2012' and '1/31/2012'