Solved

Query that will return all rows for the last two weeks

Posted on 2013-06-06
6
467 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
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Count with a subquery showing details 10 40
sql server concatenate fields 10 30
SQL Server 2012 r2 - Varible Table 3 19
Query Help - MSSQL - Averages 5 25
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now