Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MS SQL Query help

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

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach 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.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

809 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