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
Solved

combine two queries

Posted on 2015-02-05
11
62 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 48

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 48

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 48

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

861 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