• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

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

   audit.DocID,Audit.usrname, max(Audit.tsstamp), audit.appid, Sum(All_Pages.numobjects) TotalPages
   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
   CONVERT(VARCHAR(10),GETDATE(),111)= CONVERT(VARCHAR(10),convert(datetime,tsstamp),111)
   Audit.DocID, Audit.usrname, Audit.tsstamp, audit.appid
2 Solutions
Union ALL does not filter duplicates, so if you have the same DocID and NumObjects in two of the dt# tables, it will not filter the duplicate records.
yeah so you either need to add "Union" instead of "Union ALL" that will prevent the data duplicacy. Or you can keep the count from the above query and then do inner join on the tables separately and keep the count of the records returned and at last subtract the value from the earlier one.

You can also having clause at last to filter out those records which have count > 1

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now