TechsupportAbility
asked on
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.
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.
ASKER
This didn't work and gave an error in the system.
Monday morning...
declare @reviewdate datetime
set @reviewdate = "2013-06-03"
where actual_date between DATEADD(day, -30, @reviewdate) and @reviewdate
ASKER
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'
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'
actual_date>=DATEADD("d", -30, TODAY()) and event_name = 'Nursing Assessment'
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I figured this out on my own
Open in new window