?
Solved

MS SQL Query help

Posted on 2011-09-19
2
Medium Priority
?
237 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
[X]
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
2 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1000 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 1000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

765 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