hi. i'm trying to make a report in asp that sums up a whole bunch of statistics. the report is quite lengthy and takes about 2 minutes to load. i thought i would solve that problem by making a table to dump the statistics into instead of calculating the statistics every time the report is viewed. this way, the page would just read the table with the statistics already put in it. these statistics would be updated every day or two so i don't need anything up to the minute.
however, i have run into a small problem which is actually updating the information in the table. my new table is called OfficeReport and it looks like this:
this is a query for one of the statistics i am currently using:
SELECT DefendantCase.UserID, COUNT(DefendantCase.UserID) AS CountOfUserID
FROM DefendantCase LEFT JOIN UserDescription ON DefendantCase.UserID=UserDescription.UserID
WHERE UserDescription.Status=1 AND UserDescription.UserType=1
GROUP BY DefendantCase.UserID
if i run this query, this is what i get:
how can i modify this query so its output updates the same UserID column in the OfficeReport table? Thanks!
I'm guessing you want to update when the userid is already present in the table and insert when it's not...
-- OUTER JOIN with OfficeReport WHERE UserID IS NULL should give all
-- UserID's that are not in OfficeReport
INSERT OfficeReport (UserID, Stat1)
FROM DefendantCase dc
LEFT OUTER JOIN UserDescription ud ON dc.UserID = ud.UserID
LEFT OUTER JOIN OfficeReport orep ON orep.UserID = dc.Userid
WHERE ud.Status = 1
AND orep.UserID IS NULL
SET Stat1 = COUNT(dc.UserID)
FROM OfficeReport orep
INNER JOIN DefendantCase dc ON dc.UserID = orep.UserID
LEFT OUTER JOIN UserDescription ud ON dc.UserID= ud.UserID
WHERE ud.status = 1