SQL select for month date range

20 pts.
Tags:
SQL
SQL Server
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

Carlosdl 29360 pts. | Nov 9 2009 11:08PM GMT

I’m wondering how is that running without errors.

AFAIK, the DATEPART function retunrs an integer, so, here:

<pre>[service date 1] between [new patient date] and datepart(m,[new patient date])+1</pre>

You are mixing two different data types.

I think you should try something like this:

<pre>[service date 1] between [new patient date] and DATEADD(m,1,[new patient date])</pre>

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    I'm wondering how is that running without errors. AFAIK, the DATEPART function retunrs an integer, so, here:
    [service date 1] between [new patient date] and datepart(m,[new patient date])+1
    You are mixing two different data types. I think you should try something like this:
    [service date 1] between [new patient date] and DATEADD(m,1,[new patient date])
    69,065 pointsBadges:
    report
  • CraigN
    that works perfectly, thanks for your help!
    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following