SQL for data from today to six weeks back? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL for data from today to six weeks back?

Author

Topic

Maverick_
Posting Yak Master

107 Posts

Posted - 2011-09-23 : 05:16:18

Hi all,

I am looking to write an SQL query that looks at Jobs entered today and what is outstanding Six weeks back.

How do you get it to run so that it looks at Jobs outstanding from today's date to anything outstanding six weeks back?

So far this is what I have. The data is coming from one table: Job

SELECT
job.job_no,
job.job_desc,
job.jobentrydate

FROM
job

WHERE
job.outstanding = 'Y'

Any help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 05:20:50

add this to where condition


AND job.jobentrydate >=DATEADD(wk,DATEDIFF(wk,0,GETDATE())-6,0)
AND job.jobentrydate < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Maverick_
Posting Yak Master

107 Posts

Posted - 2011-09-23 : 07:30:12

Hi Visakh, thanks! I am trying to understand the code but am having a little bit of difficulty understanding how DATEADD and DATEDIFF work together. Any explanations to decode it :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 08:14:12

yep.
you first take weeks/days past from base date till date value and then add the corresponding value to base date. this will take you to start of date for the period(ie start of week in case of wk,start of day in case of dd etc which corresponds to time of 12 midnight). this will enable you to include all the datafrom start period ie from midnight on day that falls on 6 weeks back till ( and not including) midnight of next day ie whole of today.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources