SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL YTD (-) previous week query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arvi
Starting Member

Philippines
7 Posts

Posted - 03/04/2014 :  08:35:26  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
420 Posts

Posted - 03/04/2014 :  08:52:35  Show Profile  Reply with Quote


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)




sabinWeb MCP
Go to Top of Page

arvi
Starting Member

Philippines
7 Posts

Posted - 03/04/2014 :  09:20:01  Show Profile  Reply with Quote
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
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
420 Posts

Posted - 03/04/2014 :  09:23:21  Show Profile  Reply with Quote


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)




sabinWeb MCP
Go to Top of Page

arvi
Starting Member

Philippines
7 Posts

Posted - 03/04/2014 :  09:38:56  Show Profile  Reply with Quote
yay! it works!

thanks a lot Stepson
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
420 Posts

Posted - 03/04/2014 :  09:40:45  Show Profile  Reply with Quote
welcome


sabinWeb MCP
Go to Top of Page

arvi
Starting Member

Philippines
7 Posts

Posted - 03/05/2014 :  03:49:21  Show Profile  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000