MS SQL Query help

Posted on 2011-09-19
Last Modified: 2012-06-27
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
Question by:kfm2011
LVL 39

Accepted Solution

BrandonGalderisi earned 250 total points
ID: 36562737
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.
LVL 25

Assisted Solution

TempDBA earned 250 total points
ID: 36566072
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question