Solved

Query that will return all rows for the last two weeks

Posted on 2013-06-06
6
471 Views
Last Modified: 2013-07-18
Hello,

Is it possible to get some help to write a query that will return all rows for all items in a two week period?

I need to write a SSRS report that will default to the last two weeks when it opens for the first time but I am not sure how to create or what to assign to this parameter.

I hope this is clear, if not, please let me know.

Thank you so much.
0
Comment
Question by:metropia
[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
6 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 125 total points
ID: 39225936
>'will default to the last two weeks'
Does this imply that the user should eventually be able to select other date values?

If no, better to handle this in the SP that feeds this report in a WHERE clause.
0
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 125 total points
ID: 39225938
You can establish the date for 2 weeks ago with a couple functions.

SELECT DATEADD(dd, -14, CONVER(varchar(10), GETDATE(), 101))
0
 

Author Comment

by:metropia
ID: 39226005
To answer JimHorn, yes the user eventually will have the ability to select other date values.

To UnifiedIS, thank you for that query. I will give it a try.
0
Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

 
LVL 21

Assisted Solution

by:Jason Yousef, MS
Jason Yousef, MS earned 125 total points
ID: 39226559
Hi,

So do you want to control the 2 weeks period from SSRS and let users have control over it? using parameters?


or in your source T-sql ?
select * from table where datecolumn between getdate() and DATEADD(Week, -2, getdate() ) 

or

select * from table where datecolumn >= DATEADD(Week, -2, getdate() ) 

Open in new window

0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 125 total points
ID: 39228279
:( the following line will always select zero rows

select * from table where datecolumn between getdate() and DATEADD(Week, -2, getdate() )

between requires the dates in the opposite positions (low first, high second)

select * from table where datecolumn between DATEADD(Week, -2, getdate() )  and getdate()

but I prefer not to use between anyway.
0
 

Author Closing Comment

by:metropia
ID: 39336622
Hi guys.

I apologize for taking so long to get back to this question. I got side tracked and today memory struck me and logged-on to EE to finally close this question.

I ended up using a different approach, based on a solution I found online, using a stored procedure.

I am splitting the 500 point among the four experts that contribute with a comment, or potential solution, because of all the time that took me to get back to you.

I hope this is acceptable.

Thank you greatly.
0

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

623 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