Solved

Query that will return all rows for the last two weeks

Posted on 2013-06-06
6
468 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SELECT query on two levels (detail and summary) 13 57
SSRS 2016 KPI 2 32
error in my cursor 5 41
SQL Syntax 14 35
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
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…

820 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