I have a table (tblReportHits) that contains 3 columns ntid, report, date. I want to show by report and grouped by ntid the report count by ntid (an agent no). I want to do this since the begining and for the past 30 days.The table has been in existence about 4 months. So for each ntid (agent) I can show how many time he/she went to report X since the begining and for the last 30 days, this is a "hits" calculation.
I thought I could do the above via a derived query. I am using SSMS and in a view when I list my selects in the from clause I end up with the following:
SELECT derivedtbl_1.report, derivedtbl_1.Expr1, derivedtbl_1.ntid
FROM (SELECT report, COUNT(report) AS Expr1, ntid
WHERE (lastVisit > GETDATE() - 30)
GROUP BY report, ntid) AS derivedtbl_1 CROSS JOIN
(SELECT report, COUNT(report) AS TotalHits, ntid
FROM dbo.tblReportHits AS tblReportHits_1
GROUP BY report, ntid) AS derivedtbl_2
Thus I get a cross join. This also happens when I manually write the query
Select t.report, t.count, t.date, m.count
From (Select ntid, count(report) as count, date from tblReportHits Group by ntid, date) as t,
(Select ntid, count(report) as count, date from tblReportHits Group by ntid, date) as m
Am I trying to do something that is logically not possible?