• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 72
  • Last Modified:

combine two queries

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
vbnetcoder
Asked:
vbnetcoder
  • 6
  • 3
  • 2
1 Solution
 
Lee SavidgeCommented:
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
 
Lee SavidgeCommented:
Or do you mean you want 3 columns in the output?
0
 
vbnetcoderAuthor Commented:
3 columns of output

 projectname, Total Days, DocumentCount
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
vbnetcoderAuthor Commented:
That is a very good start ... the one problem is that it is returning duplicates
0
 
vbnetcoderAuthor Commented:
duplicate projects i mean
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I need to know more about your data model. Samples will help.
Also, what do you want to achieve here?
0
 
vbnetcoderAuthor Commented:
otherwise it is correct but Total days needs to be the sum per project name --- so project will only be listed once
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
vbnetcoderAuthor Commented:
OK i figured it out with your help ... thanks
0
 
vbnetcoderAuthor Commented:
ty
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now