[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

combine two queries

Posted on 2015-02-05
11
Medium Priority
?
70 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 52

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 52

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 52

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

649 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