Return the last 3 months on SQL - how? 1

[tinapa]

hi guys! can you please give me some idea? i have this table:

id datefield counthits
1 dec 12, 2008 2323
2 nov 23, 2008 1234
3 oct 2, 2008 532
4 sep 5, 2008 3455
5 aug 12, 2008 3895
.
.
.

what i want is to return this recordset, the last 3 months:

datefield counthits
dec 2323
nov 1234
oct 532

any ideas is appreciated. thanx

 

[SQLSister]

OK, first how is your date information stored? In a date field?

"NOTHING is more important in a database than integrity." ESquared

 

[tinapa]

yes, in a date field

 

[tlbroadbent]

Assuming the table contains multiple rows per month you could write a query like this to summarize three months of data.

SELECT Mnth = CONVERT(CHAR(3), datefield, 7), counthits = SUM(counthits)
FROM Tablename
WHERE datefield >= DATEADD(mm, -2, DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
GROUP BY CONVERT(CHAR(3), datefield, 7)

DATEADD(mm, -2, DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) calculates the first day of the first month of the past three months which includes the current month.

Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)

 

[tinapa]

Hi Terry, you're AWESOME! It work!!!!!

Have a nice weekend.

Thanks a lot!!!