Solved

SQL Date

Posted on 2013-06-03
8
381 Views
Last Modified: 2013-06-23
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
Comment
Question by:TechsupportAbility
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 5

Expert Comment

by:Jason Schlueter
ID: 39216957
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
 

Author Comment

by:TechsupportAbility
ID: 39217294
This didn't work and gave an error in the system.
0
 
LVL 5

Expert Comment

by:Jason Schlueter
ID: 39217550
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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:TechsupportAbility
ID: 39219645
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 39225523
actual_date>=DATEADD("d", -30, TODAY()) and event_name = 'Nursing Assessment'
0
 

Author Comment

by:TechsupportAbility
ID: 39256718
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
 

Accepted Solution

by:
TechsupportAbility earned 0 total points
ID: 39256872
I figured it out...

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

actual_date >= actual_date-100
0
 

Author Closing Comment

by:TechsupportAbility
ID: 39269084
I figured this out on my own
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Report Builder 3.0 3 58
SSRS - Powershell 7 81
Visual Studio 2015 Report Viewer has blank data source? 1 19
T-SQL Query 9 37
In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question