Solved

MS SQL Server 2008 Query Question

Posted on 2011-09-16
4
296 Views
Last Modified: 2013-11-05
My sql is rusty and I need some help.  I have a table ae_audit which has a records that can join to a number of different tables (ae_dl1, ae_dl2, etc.).  ae_audit has a unique docid that can be in one of the other tables(dl1, dl2, etc.) and those table have a record for each page in that document.  So for each record in ae_audit, i'm trying to figure out how many pages there are by joining to a union in a sub select.  I've tried various queries and can't figure it out.  The different subqueries work fine, i cant bring them together.
thanks

select distinct Audit.docid
from dbo.ae_audit as Audit
where CONVERT(VARCHAR(10),GETDATE(),111)= CONVERT(VARCHAR(10),convert(datetime,tsstamp),111)
 AND
audit.docid = ((select docid, pagenum from dbo.ae_dl1 UNION ALL
select docid, pagenum from dbo.ae_dl2 UNION ALL
select docid, pagenum from dbo.ae_dl3 UNION ALL
select docid, pagenum from dbo.ae_dl4 UNION ALL
select docid, pagenum from dbo.ae_dl5 UNION ALL
select docid, pagenum from dbo.ae_dl6 UNION ALL
select docid, pagenum from dbo.ae_dl7 UNION ALL
select docid, pagenum from dbo.ae_dl8 UNION ALL
select docid, pagenum from dbo.ae_dl9) As All_Pages)
0
Comment
Question by:kfm2011
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
tim_cs earned 500 total points
ID: 36551476

SELECT
   Audit.DocID
   ,Count(All_Pages.Pagenum) TotalPages
FROM
   dbo.ae_audit Audit
   INNER JOIN (select docid, pagenum from dbo.ae_dl1 UNION ALL
      select docid, pagenum from dbo.ae_dl2 UNION ALL
      select docid, pagenum from dbo.ae_dl3 UNION ALL
      select docid, pagenum from dbo.ae_dl4 UNION ALL
      select docid, pagenum from dbo.ae_dl5 UNION ALL
      select docid, pagenum from dbo.ae_dl6 UNION ALL
      select docid, pagenum from dbo.ae_dl7 UNION ALL
      select docid, pagenum from dbo.ae_dl8 UNION ALL
      select docid, pagenum from dbo.ae_dl9) 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

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 36554006
I think the solution below is better then using a UNION. Also i fyou are using the SAL2008 then casting to DATE type is much simpler than using convert:
SELECT
	Audit.DocID
	,Count(*) TotalPages
FROM
	dbo.ae_audit Audit
	LEFT JOIN dbo.ae_dl1 d1
		on d1.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl2 d2
		on d2.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl3 d3
		on d3.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl4 d4
		on d4.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl5 d5
		on d5.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl6 d6
		on d6.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl7 d7
		on d7.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl8 d8
		on d8.DocID=Audit.DocID
	LEFT JOIN dbo.ae_dl9 d9
		on d9.DocID=Audit.DocID
where
	cast(getdate() ad date)=cast(tsstamp as date)
group by
	Audit.DocID

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 36554008
Of course I mean "as" and not "ad" in the WHERE clause:
...
where
	cast(getdate() as date)=cast(tsstamp as date)

Open in new window

0
 

Author Comment

by:kfm2011
ID: 36561461
Thanks, you definitely answered my initial question.  As a followup question, the ae_audit may have multiple records (all with the same date for each DocID) and I only want to join one of these to ae_dl1, etc., not all of them.  Any more guidance would be appreciated.

thanks
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

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…
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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 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