Solved

combine two queries

Posted on 2015-02-05
11
61 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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 47

Accepted Solution

by:
Vitor Montalvão earned 500 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 47

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 47

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Union 2 queries to a cte (temp table perhaps) 9 36
SQL Help 27 46
Query Help - MSSQL - Averages 5 27
SQL Use Distinct with two fields 3 11
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 …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

832 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