Solved

MS SQL Query help

Posted on 2011-09-19
2
222 Views
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

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),GETDATE(),111)= CONVERT(VARCHAR(10),convert(datetime,tsstamp),111)
GROUP BY
   Audit.DocID, Audit.usrname, Audit.tsstamp, audit.appid
0
Comment
Question by:kfm2011
2 Comments
 
LVL 39

Accepted Solution

by:
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.
0
 
LVL 25

Assisted Solution

by:TempDBA
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now