Cross Tabs and Groupings

I m trying to struct a query that will give me the impression reports. I want a table that give the page name ,yesterday hits,todays hits, this weeks hits and this month hits.
My Table is like this ..
tblStat----
MID -- key id incrementing with no replication
MURL -- name of page
MIMp -- Counted impression at that day
MDate -- Smalldatetime value
------
returning rows are similar like:
1 index.asp 5 13/12/2003
2 main.asp 2 13/12/2003
3 index.asp 3 14/12/2003
4 main.asp 8 14/12/2003
--
I tried to do a query and it is :
SELECT DISTINCT dbo.tblStats.MURL, INSERT1.MToday, INSERT2.Mpre, INSERT3.MWeek, INSERT4.MMonth
FROM dbo.tblStats LEFT OUTER JOIN
(SELECT DISTINCT MURL, SUM(MIMP) AS Mpre
FROM tblStats
WHERE (Mdate = '3/18/2003')
GROUP BY MURL) INSERT2 ON dbo.tblStats.MURL = dbo.tblStats.MURL LEFT OUTER JOIN
(SELECT DISTINCT MURL, SUM(MIMP) AS MToday
FROM tblStats
WHERE (Mdate = '3/19/2003')
GROUP BY MURL) INSERT1 ON dbo.tblStats.MURL = dbo.tblStats.MURL LEFT OUTER JOIN
(SELECT DISTINCT MURL, SUM(MIMP) AS MWeek
FROM tblStats
WHERE (MDate >= '3/15/2003' AND MDate <= '3/21/2003')
GROUP BY MURL) INSERT3 ON dbo.tblStats.MURL = dbo.tblStats.MURL LEFT OUTER JOIN
(SELECT DISTINCT MURL, SUM(MIMP) AS MMonth
FROM tblStats
WHERE (MDate >= '3/01/2003' AND MDate <= '3/29/2003')
GROUP BY MURL) INSERT4 ON dbo.tblStats.MURL = dbo.tblStats.MURL
------------
but this query returning to me too much records. Because i only have to pages in table that must be two records to return me back. If anyone know how to solve this please response me. Thank you from now on.How About something using the [Case When] & DatePart

SELECT DISTINCT dbo.tblStats.MURL,
SUM(CASE WHEN MDate = Getdate() THEN MIMP ELSE 0 END) AS Today,
SUM(CASE WHEN MDate = (Getdate()-1) THEN MIMP ELSE 0 END) AS YesterDay,
SUM(CASE WHEN MDate = (Getdate()+1) THEN MIMP ELSE 0 END) AS Tommorow,
SUM(CASE WHEN DatePart(wk, MDate) = DatePart(wk, GetDate()) THEN MIMP ELSE 0 END) AS ThisWeek,
FROM tblStats

Or Something like that

PS. Joking about the Tommorow One - lol

GW|||GWilliy,
no distinct, but group by. Do not use abrevs for time parts, it is missguiding.
wk=week

SELECT dbo.tblStats.MURL
...
GROUP BY dbo.tblStats.MURL

hexcode,
be sure you don't store time. Also note that function DATEPART(week,@.D datetime) is nondeterministic (SET DATEFIRST/LANGUAGE dependent).

Good luck!|||The query really worked fine. Just i send the date values from program instead of getdate. Thank you for your help.