Last 3 months compared to same period 1 year ago

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

Transact-SQL (2005)

Last 3 months compared to same period 1 year ago

Author

Topic

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2007-10-01 : 12:47:23

Hey All,

I have this requirement to create 2 datasets so I can compare the last 3 months of data for this year against the same period last year. How would you guys to this?

Last 3 Mos on Current Year:
select * into #a
from db..tablea
where datediff(day,SaleDt,getdate ()) between 0 and 120

select * into #b
from db..tablea
where datediff(month,SaleDt,getdate ()) between 12 and 15

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-01 : 14:52:26

create table #a(<cols>)
insert into #a
select <cols>
from db..tablea
where
DATEDIFF(month,@indate,DATEADD(dd,0,DATEDIFF(dd,0,@date))) between 12 and 14
and similary for the last 3 months of current year

between 12 and 15 gives you 4 months of data!

Jim

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-10-01 : 19:01:23

can you post your table structure, and desired results

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-02 : 01:54:58

This allows you to use present indexes for datetime column.

-- Previous three months 
SELECT	*
FROM	Table1
WHERE	Col1 >= DATEADD(MONTH, DATEDIFF(MONTH, '19000401', CURRENT_TIMESTAMP), '19000101')
	AND Col1 < DATEADD(MONTH, DATEDIFF(MONTH, '19000101', CURRENT_TIMESTAMP), '19000101')

-- Previous three months a year ago
SELECT	*
FROM	Table1
WHERE	Col1 >= DATEADD(MONTH, DATEDIFF(MONTH, '19010401', CURRENT_TIMESTAMP), '19000101')
	AND Col1 < DATEADD(MONTH, DATEDIFF(MONTH, '19010101', CURRENT_TIMESTAMP), '19000101')

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources