Solved

Data based on Next Month

Posted on 2011-10-26
253 Views
I need to pull people from the database if their birthday month is next month.
How can I do this?

and Month({PATIENT.BIRTH_DATE}) = Current Month + 1
0
Question by:Becky Edwards

LVL 11

Expert Comment

If you are using SQL Server, try this:

select *
from PATIENT
where datepart(mm, PATIENT.BIRTH_DATE) =  datepart(mm, dateadd(mm,  1, getdate()))
0

Author Comment

I am putting this in the report selection on a crystal report.

The sequel server one won't work.
0

LVL 100

Expert Comment

Try this

You can't just compare the months because of year change.

mlmcc
0

Author Comment

I tried that formula and it didn't return anything.  And I know there should be stuff there because I have the report working with this formula, choosing 11 as the birth month.

Month({PATIENT.BIRTH_DATE}) = {?BirthMonth}

What am I doing wrong?
0

Author Comment

I am asking only that the birth month is 10 (or 11).

What I need now is to just build that piece into the report, so the user does not have to tell me what month they want, but that they just get all patients whose birth month is next month.
0

LVL 100

Expert Comment

Did you try my formula as the select formula?

mlmcc
0

Author Comment

Here is the one I meant to include.
0

Author Comment

0

LVL 100

Expert Comment

Did you try this one

The first one won't work because the dates are being compared with the year

mlmcc
0

LVL 100

Accepted Solution

Another one that will work is

Month(CurrentDate)+1 = Month({PATIENT.BIRTH_DATE})
OR
Month(CurrentDate)+1 = Month({PATIENT.BIRTH_DATE}) +12

ANother idea

If Month(CurrentDate) = 12 then
Month({PATIENT.BIRTH_DATE}) = 1
Else
Month({PATIENT.BIRTH_DATE}) = Month(CurrentDate)+1

mlmcc
0

Author Closing Comment

Yes, they all work!  The first one seems to return the fastest, the second one takes the longest, but I prefer the third one because I know it will work in December, and I am not sure the first one will.  Since I won't be working on this or checking it in December for January, I will go the safest route, which is the third one.

Thank you so much!!!!!!!  :)
0

Featured Post

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…