Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    7

    Unanswered: 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.

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Transform with Fixed Column Headers

    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
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    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!

  4. #4
    Join Date
    Mar 2003
    Posts
    7
    The query really worked fine. Just i send the date values from program instead of getdate. Thank you for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •