Retrieving counts from multiple collumns (Crosstab?)

[drizzage]

I know just enough SQL to get by, and lately, I'm not getting by so well!
I have a listing of records on my Web site that displays the total number of views each has received, along with last month's views, and this month's views. I'm currently running 3 separate queries, and using the recordcount from each to show the number of views.

<!--Total Views-->
SELECT *
FROM Table
WHERE ID = 'thisID'
	
<!--This Month-->
SELECT *
FROM Table
WHERE Month(DateViewed) = Month(GetDate()) AND
year(FirstEntered) = year(getdate()) AND ID = 'thisID'   

<!--Last Month-->
SELECT *
FROM Table
WHERE year(DateViewed) = year(dateadd(m, -1, getdate()))
and month(DateViewed) = month(dateadd(m, -1, getdate())) AND ID = 'thisID'

Although this is working, it's grossly inefficient, and slowing things down. I know there's got to be a better way to do this. Is there a way to retrieve all 3 values with a single query?

TIA

 

[indrahig]

declare @TtlView int, @TtlThisMonth int, @TtlLastMonth int

set @TtlView = (SELECT count(*)
FROM Table
WHERE ID = 'thisID')

set @TtlThisMonth = (SELECT count(*)
FROM Table
WHERE Month(DateViewed) = Month(GetDate()) AND
year(FirstEntered) = year(getdate()) AND ID = 'thisID')

set @TtlLastMonth = (SELECT count(*)
FROM Table
WHERE year(DateViewed) = year(dateadd(m, -1, getdate()))
and month(DateViewed) = month(dateadd(m, -1, getdate()))
AND ID = 'thisID')

select @TtlView, @TtlThisMonth, @TtlLastMonth


try that code on single query..
this may not the best solution, but just give alternative..
Hope that helps

 

[drizzage]

Thanks indrahig. That runs a tiny bit faster, but it's searching such a large recordset, that it's still to slow. What I'm going to do is drop the monthly breakdown from the list, and make that info available in a pop-up window for each list item.

 

[donutman]

This should be very fast:
Select count(*) CrntTotal,
sum(case datediff(mm,DateViewed,GetDate()) when 0 then 1 else 0 end)) ThisMonth,
sum(case datediff(mm,DateViewed,GetDate()) when 13 then 1 else 0 end)) LastYear from Table
where ID='thisID'
-Karl