Hello,

I am attempting to obtain a count of visits each patient comes in in one month, so for example of they were a new patient in January on the 4th, I would want from the 4th of January to the 4th of February.  The code I am attempting to use is below, but every time I use it, no records are returned.  How can I fix this problem?

Thanks,

Craig

select [last name], [first name], [new patient date], count (distinct [service date 1]) as 'Visit Count'
from [chiropractic healing center].dbo.patient
inner join [chiropractic healing center].dbo.[Billing detail] on [chiropractic healing center].dbo.patient.[chart number] = [chiropractic healing center].dbo.[Billing detail].chart
where [new patient date] between '2009-05-01' and '2009-05-31'
and [service date 1] between [new patient date] and datepart(m,[new patient date])+1
and ([transaction code] = '98940' or [transaction code] = '98941' or [transaction code] = 'A9170' or [transaction code] = 'A9270')
group by [last name], [first name], [new patient date]
order by [new patient date]


Software/Hardware used:
SQL Server 2005