Solved

Query that will return all rows for the last two weeks

Posted on 2013-06-06
6
470 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 65

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 48

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 67
SP result not being displayed 5 69
T-SQL: Only Wanting One Record 8 61
STUFF function - proper use to update column? 6 17
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
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 …
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…

751 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