Need select to get last weeks info based on sysdat

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

SQL Server 2008 Forums

Transact-SQL (2008)

Need select to get last weeks info based on sysdat

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-05-22 : 15:05:10

I am using the following query manually putting dates to fetch count.

select COUNT(*) from tab_ocrdata where CONVERT(VARCHAR(8), created_dt, 1) in ('05/14/12','05/15/12','05/16/12','05/17/12','05/18/12') and type = 'PM'

Want to put the select within a SP, how can i automatically execute query based on sysdate() just fetch last weeks worth of select count(*).

Thank you very much for the helpful info.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-22 : 15:10:12

Here is the gist, you might have to tweak the dates if you are lookin for Monday - Friday:

SELECT 
	COUNT(*) 
FROM 
	tab_ocrdata 
WHERE
	type = 'PM'
	AND created_dt >= DATEADD(WEEK, DATEDIFF(WEEK, 0, SYSDATETIME()) - 1, 0)
	AND created_dt < DATEADD(WEEK, DATEDIFF(WEEK, 0, SYSDATETIME()), 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources