?
Solved

combine two queries

Posted on 2015-02-05
11
Medium Priority
?
69 Views
Last Modified: 2015-02-05
In the following query i want to combine these 2 queries so that i return projectname, Total Days, DocumentCount as ONE dataset... how?

SELECT     dbo.Project.[Project Name], DATEDIFF(day, dbo.[Document].[Document Date], dbo.[Document].[Closing Date]) AS TotalDays
FROM         dbo.Project INNER JOIN
                      dbo.Document_Project ON dbo.Project.ID = dbo.Document_Project.Project_ID INNER JOIN
                      dbo.[Document] ON dbo.Document_Project.Document_ID = dbo.[Document].ID INNER JOIN
                      dbo.State ON dbo.[Document].State_ID = dbo.State.ID
WHERE     (dbo.[Document].[Closing Date] IS NOT NULL)
GROUP BY dbo.Project.[Project Name], dbo.[Document].[Document Date], dbo.[Document].[Closing Date]


SELECT     Project.[Project Name], COUNT(*) AS DocumentCount
FROM         Project INNER JOIN
                      Document_Project ON Project.ID = Document_Project.Project_ID INNER JOIN
                      [Document] ON Document_Project.Document_ID = [Document].ID
GROUP BY Project.[Project Name]
0
Comment
Question by:vbnetcoder
[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
  • 6
  • 3
  • 2
11 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40590831
Do you mean union them together?

SELECT     dbo.Project.[Project Name], DATEDIFF(day, dbo.[Document].[Document Date], dbo.[Document].[Closing Date]) AS TotalDays
FROM         dbo.Project INNER JOIN
                      dbo.Document_Project ON dbo.Project.ID = dbo.Document_Project.Project_ID INNER JOIN
                      dbo.[Document] ON dbo.Document_Project.Document_ID = dbo.[Document].ID INNER JOIN
                      dbo.State ON dbo.[Document].State_ID = dbo.State.ID
WHERE     (dbo.[Document].[Closing Date] IS NOT NULL)
GROUP BY dbo.Project.[Project Name], dbo.[Document].[Document Date], dbo.[Document].[Closing Date]

union

SELECT     Project.[Project Name], COUNT(*) AS DocumentCount
FROM         Project INNER JOIN
                      Document_Project ON Project.ID = Document_Project.Project_ID INNER JOIN
                      [Document] ON Document_Project.Document_ID = [Document].ID
GROUP BY Project.[Project Name] 

Open in new window

0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40590833
Or do you mean you want 3 columns in the output?
0
 

Author Comment

by:vbnetcoder
ID: 40590881
3 columns of output

 projectname, Total Days, DocumentCount
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40590889
You can use a CTE for do the trick:
 WITH DocCount_CTE (ID, Project_Name, DocumentCount) AS
 (
 SELECT     Project.ID, Project.[Project Name], COUNT(1) 
 FROM         Project 
 INNER JOIN Document_Project ON Project.ID = Document_Project.Project_ID 
 INNER JOIN [Document] ON Document_Project.Document_ID = [Document].ID
 GROUP BY Project.ID, Project.[Project Name] 
)

SELECT     DocCount_CTE.[Project Name], DATEDIFF(day, dbo.[Document].[Document Date], dbo.[Document].[Closing Date]) AS TotalDays, DocCount_CTE.DocumentCount
FROM       DocCount_CTE
INNER JOIN dbo.Document_Project ON DocCount_CTE.ID = dbo.Document_Project.Project_ID 
INNER JOIN dbo.[Document] ON dbo.Document_Project.Document_ID = dbo.[Document].ID 
INNER JOIN dbo.State ON dbo.[Document].State_ID = dbo.State.ID
 WHERE   (dbo.[Document].[Closing Date] IS NOT NULL)

Open in new window

0
 

Author Comment

by:vbnetcoder
ID: 40590917
That is a very good start ... the one problem is that it is returning duplicates
0
 

Author Comment

by:vbnetcoder
ID: 40590918
duplicate projects i mean
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40590928
I need to know more about your data model. Samples will help.
Also, what do you want to achieve here?
0
 

Author Comment

by:vbnetcoder
ID: 40590995
otherwise it is correct but Total days needs to be the sum per project name --- so project will only be listed once
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40591003
The problem is that a project as many documents. If you want a row by project the you should also have a document only otherwise will return a row by document.
The only thing that can do what you want is to return only a single document. But which document do you want (the first one? the last one? other one?).
0
 

Author Comment

by:vbnetcoder
ID: 40591115
OK i figured it out with your help ... thanks
0
 

Author Closing Comment

by:vbnetcoder
ID: 40591118
ty
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

764 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