Previous Month and previous week

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 2000 Forums

Transact-SQL (2000)

Previous Month and previous week

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-11-05 : 21:02:44

hi
Can anyone help me in writing 3 different queries which retrives
1)all record from a table for previous week
2)all record from a table for previous quarter
3)all record from a table for previous month

Note the start day of month is not sunday it may be monday, tuesday or whatever the admin will set

Thanks in advance

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-05 : 21:45:48

You mentioned the starting day is variable, what about the end day?
Is the assumption for week 7 days or 5 days? ie M-F or a through b where a and b are input params?
Previous month meaning 1st of the month through last of the month or again a to b?

You mention the admin will set the day - Can you be more specific ?

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-11-05 : 22:39:54

hey thanks
I got the months and week thing but stuck in previous quater will u please help me and previous quater means one minus he current quater
thanks

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-05 : 22:42:59

Sure,

DECLARE @thisdate datetime,
		@startdate datetime, 
		@enddate datetime

SET @thisdate = convert(datetime,convert(varchar(12),getdate()))

--Previous Quarter
--Assumes Q1 = 1/1 - 4/1 ; Q2 = 4/1 - 7/1 ; Q3 = 7/1 - 10/1 ; Q4 = 10/1 - 1/1
select @startdate = 

	case when @thisdate between  convert(datetime,'1/1/' + convert(char(4),datepart(yy,getdate()))) AND convert(datetime,'4/1/' + convert(char(4),datepart(yy,getdate()))) 
		--Forth Quarter of last year
		 then convert(datetime,'10/1/' + convert(char(4),datepart(yy,getdate())-1))

		when @thisdate between  convert(datetime,'4/1/' + convert(char(4),datepart(yy,getdate()))) AND convert(datetime,'7/1/' + convert(char(4),datepart(yy,getdate())))
		--First Quarter of tis year
		then convert(datetime,'1/1/' + convert(char(4),datepart(yy,getdate())))

		when @thisdate between  convert(datetime,'7/1/' + convert(char(4),datepart(yy,getdate()))) AND convert(datetime,'10/1/' + convert(char(4),datepart(yy,getdate()))) 
		--Second Quarter of this year
		then convert(datetime,'4/1/' + convert(char(4),datepart(yy,getdate())))

		when @thisdate between  convert(datetime,'10/1/' + convert(char(4),datepart(yy,getdate()))) AND convert(datetime,'1/1/' + convert(char(4),datepart(yy,getdate())+1)) 
		--Third Quarter of this Year
		then convert(datetime,'7/1/' + convert(char(4),datepart(yy,getdate())))

	end,

	@enddate = dateadd(mm,3,@startdate)

select @startdate StartofLastQ,@enddate EndOfLastQ

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-11-05 : 22:48:12

Ummmmm...

SELECT * FROM myTable WHERE StartDate BETWEEN DateAdd(q, DateDiff(q, 0, Start), 0) AND DateAdd(q, DateDiff(q, 0, Start)+1, 0)

Shit, I goofed that up, this is the correct version:

SELECT * FROM myTable WHERE StartDate BETWEEN DateAdd(q, DateDiff(q, 0, Start)-1, 0) AND DateAdd(q, DateDiff(q, 0, Start), 0)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-05 : 22:57:41

quote:
Originally posted by robvolk

Ummmmm...

SELECT * FROM myTable WHERE StartDate BETWEEN DateAdd(q, DateDiff(q, 0, Start), 0) AND DateAdd(q, DateDiff(q, 0, Start)+1, 0)

Well I think we'll all agree that code is much better...
Except s/he was looking for prev q


SELECT * from table WHERE StartDate Between DateAdd(q, DateDiff(q, 0, dateadd(mm,-3,getdate())), 0) AND DateAdd(q, DateDiff(q, 0, dateadd(mm,-3,getdate()))+1, 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources