kfm2011
asked on
MS SQL Query help
Any help on this sql query would be appreciated. I have a main table Audit with these fields (tsstamp(date/time), appid, docid, username, description). For each document in our system, there may be one of more records in this table. Based on appid in the above table, there will be a corresponding record in a table (dt1, dt2, dt3 where the number corresponds to the appID). dtX table has the following fields (docID and numobjects (the number of pages in the document). I'm trying to create a query that will look at Audit and join to the appropriate dt table and sum the numobjects field. I need this grouped by date, user, numobjects. Here's the SQL i've pullted together so far - any help would be apprecaited. One issue I'm having is I can't figure out how to limit to just one record in the Audit table so records are being counted multiple times. thanks
SELECT
audit.DocID,Audit.usrname, max(Audit.tsstamp), audit.appid, Sum(All_Pages.numobjects) TotalPages
FROM
dbo.ae_audit Audit
INNER JOIN (select docid, numobjects from dbo.ae_dt1 UNION ALL
select docid, numobjects from dbo.ae_dt2 UNION ALL
select docid, numobjects from dbo.ae_dt3 UNION ALL
select docid, numobjects from dbo.ae_dt4 UNION ALL
select docid, numobjects from dbo.ae_dt5 UNION ALL
select docid, numobjects from dbo.ae_dt6 UNION ALL
select docid, numobjects from dbo.ae_dt7 UNION ALL
select docid, numobjects from dbo.ae_dt8 UNION ALL
select docid, numobjects from dbo.ae_dt9) All_Pages
ON Audit.DocID = All_Pages.DocID
WHERE
CONVERT(VARCHAR(10),GETDAT E(),111)= CONVERT(VARCHAR(10),conver t(datetime ,tsstamp), 111)
GROUP BY
Audit.DocID, Audit.usrname, Audit.tsstamp, audit.appid
SELECT
audit.DocID,Audit.usrname,
FROM
dbo.ae_audit Audit
INNER JOIN (select docid, numobjects from dbo.ae_dt1 UNION ALL
select docid, numobjects from dbo.ae_dt2 UNION ALL
select docid, numobjects from dbo.ae_dt3 UNION ALL
select docid, numobjects from dbo.ae_dt4 UNION ALL
select docid, numobjects from dbo.ae_dt5 UNION ALL
select docid, numobjects from dbo.ae_dt6 UNION ALL
select docid, numobjects from dbo.ae_dt7 UNION ALL
select docid, numobjects from dbo.ae_dt8 UNION ALL
select docid, numobjects from dbo.ae_dt9) All_Pages
ON Audit.DocID = All_Pages.DocID
WHERE
CONVERT(VARCHAR(10),GETDAT
GROUP BY
Audit.DocID, Audit.usrname, Audit.tsstamp, audit.appid
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.