Solved

Query that will return all rows for the last two weeks

Posted on 2013-06-06
6
464 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 21

Assisted Solution

by:huslayer
huslayer 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to remove non-duplicated row 5 25
complicated query 15 44
ms sql stored procedure 22 77
encyps queries mssql 15 27
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

10 Experts available now in Live!

Get 1:1 Help Now