Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

SQL Date

Hello,

We have a  feature in our ECR system that allows us to pull in a sub-report into our services. This way our nurses can view past information that helps them with viewing all associated client information.

This "service" is called "Health Care Review - 30 Day". This service has a date field that is stored in our database as actual_date.

Within this "Health Care Review - 30 Day" we have a sub-report that pulls in all "Nursing Assessments".

I need to enter SQL into the sub-report that tells it to just pull in "Nursing Assessments" that occurred within the prior 30 days from the "Health Care Review -30 Day's" actual_date.

So if I created the "Health Care Review - 30 Day" with an actual_date of 3/30/2012, my sub report should pull in only the "Nursing Assessments" that occurred in the prior 30 days of the 3/30/12 "Health Care Review - 30 Day" actual_date.

So if there are any "Nursing Assessments" within  3/1/2012-3/30/2012 they will pull.

The actual_date of the "Health Care Review - 30 day" can change so it is not a concrete date. So this is why I need a formula that will just pull 30 days prior to whatever date has been entered for the actual_date.
0
TechsupportAbility
Asked:
TechsupportAbility
  • 5
  • 2
1 Solution
 
Jason SchlueterIT ManagerCommented:
DATEADD can add/subtract time from a certain date.
...where actual_date between DATEADD(day, -30, actual_date) and actual_date;

Open in new window

0
 
TechsupportAbilityAuthor Commented:
This didn't work and gave an error in the system.
0
 
Jason SchlueterIT ManagerCommented:
Monday morning...
declare @reviewdate datetime
set @reviewdate = "2013-06-03"
where actual_date between DATEADD(day, -30, @reviewdate) and @reviewdate

Open in new window

0
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
TechsupportAbilityAuthor Commented:
The section that I am able to put the SQL code is used just like the "where" clause in a SQL select....so it would have to work like that.
Here is what we have but this pulls according to the current date because of the GetDate ()
__________________________________________________________________

actual_date>=GetDate()-30 and event_name = 'Nursing Assessment'
0
 
Alpesh PatelAssistant ConsultantCommented:
actual_date>=DATEADD("d", -30, TODAY()) and event_name = 'Nursing Assessment'
0
 
TechsupportAbilityAuthor Commented:
Wont this pull 30 days prior to today's date? I need to pull 30 days prior to the entered date of the event or service.
0
 
TechsupportAbilityAuthor Commented:
I figured it out...

This works....thanks everyone for trying to assist...

actual_date >= actual_date-100
0
 
TechsupportAbilityAuthor Commented:
I figured this out on my own
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now