SQL YTD (-) previous week query

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)

SQL YTD (-) previous week query

Author

Topic

arvi
Starting Member

7 Posts

Posted - 2014-03-04 : 08:35:26

Hi,

I need to extract the data from the beginning of this year to the previous week. So if I'll run the query today, it will give me the data from January 1, 2014 - March 1, 2014. Considering that the start day of every week is Sunday and end day is Saturday.

If I'll run the query next week, I'll get the data from January 1, 2014 to March 8 ,2014.

Can someone please help me on this?

Thanks a lot!

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-04 : 08:52:35

[code]

declare @dtDate as DateTime2
set @dtDate= '20140311'--SYSDATETIME()

select CONVERT(VARCHAR(30),DATEADD(YY,DATEDIFF(YY,0,@dtDate),0),107)
, CONVERT(VARCHAR(30),DATEADD(WEEK, DATEDIFF(WEEK, 0, @dtDate), -2),107)

[/code]

sabinWeb MCP

arvi
Starting Member

7 Posts

Posted - 2014-03-04 : 09:20:01

Hi Stepson,

Apologies but can you let me know how to add your code to my current code below? Just need to extract the id's and start_dtm, from that date range.

Select ID,start_dtm
FROM tbl_subcription
WHERE start_dtm between '2014-01-01' AND (?)

How can I add the code for this year previous week?

thanks again

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-04 : 09:23:21

[code]

Select ID,start_dtm
FROM tbl_subcription
WHERE start_dtm between DATEADD(YY,DATEDIFF(YY,0,start_dtm),0) AND DATEADD(WEEK, DATEDIFF(WEEK, 0, start_dtm), -2)

[/code]

sabinWeb MCP

arvi
Starting Member

7 Posts

Posted - 2014-03-04 : 09:38:56

yay! it works!

thanks a lot Stepson

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-04 : 09:40:45

welcome

sabinWeb MCP

arvi
Starting Member

7 Posts

Posted - 2014-03-05 : 03:49:21

Hi again sir,

What code should I put on the WHERE clause if I'm going to get the previous week data only? So if I'll run the query any day this week (week 10), I'll get the data from week 9 (Feb. 23 - March 1).

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources