Solved

I need to create a View or Query in SQL Server 2005 that contains 2 queries

Posted on 2011-02-18
2
183 Views
Last Modified: 2012-05-11
For starters, I am new to SQL Server So I apologize for my ignorance in advance.
I've been working in Access for 10 years
I want to use theresults of the queries below as a source for an asp page.

I have 2 queries which each output 3 columns.
XNAC_Name           Model      ServiceableCallCount
XNAC_Name           Model      Solved Count

The criteria for the 3rd column is the only thing that changes.
I would like to combine the 2 queries  to get 4 columns
XNAC_Name           Model      ServiceableCallCount      Solved Count

How can these best be combined ?
'**This is the query to get Serviceable Call Count:
SELECT     RCAMasterFinal.XNAC_Major, XNAC_Lookup.XNAC_Name, RCAMasterFinal.Model, Count(*)AS ServiceableCalls
FROM         RCAMasterFinal INNER JOIN
                      XNAC_Lookup ON RCAMasterFinal.XNAC_Major = XNAC_Lookup.XNAC_Major
WHERE     (RCAMasterFinal.CallDate BETWEEN CONVERT(DATETIME, '2011-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-01-31 00:00:00', 102)) AND 
                      (RCAMasterFinal.ResolutionCode < 9)
 AND RCAMasterFinal.XNAC_Major = 'STP'

Group By    RCAMasterFinal.XNAC_Major, XNAC_Lookup.XNAC_Name, RCAMasterFinal.Model

'**This is the query to get Solved Count:

SELECT     RCAMasterFinal.XNAC_Major, XNAC_Lookup.XNAC_Name, RCAMasterFinal.Model, Count(*)AS SolvedCalls
FROM         RCAMasterFinal INNER JOIN
                      XNAC_Lookup ON RCAMasterFinal.XNAC_Major = XNAC_Lookup.XNAC_Major
WHERE     (RCAMasterFinal.CallDate BETWEEN CONVERT(DATETIME, '2011-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-01-31 00:00:00', 102)) AND 
                      (RCAMasterFinal.ResolutionCode = 5)
Or (RCAMasterFinal.ResolutionCode = 8)  AND RCAMasterFinal.XNAC_Major = 'STP'
Group By    RCAMasterFinal.XNAC_Major, XNAC_Lookup.XNAC_Name, RCAMasterFinal.Model

Open in new window

0
Comment
Question by:Mswetsky
2 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 34927262
I believe you can do this in one query, essentially sum using a case statement.

SELECT     RCAMasterFinal.XNAC_Major, XNAC_Lookup.XNAC_Name, RCAMasterFinal.Model, Count(*)AS ServiceableCalls, sum(case when RCAMasterFinal.ResolutionCode = 5 or RCAMasterFinal.ResolutionCode = 8 then 1 else 0 end) as SolvedCount
FROM         RCAMasterFinal INNER JOIN
                      XNAC_Lookup ON RCAMasterFinal.XNAC_Major = XNAC_Lookup.XNAC_Major
WHERE     (RCAMasterFinal.CallDate BETWEEN CONVERT(DATETIME, '2011-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-01-31 00:00:00', 102)) AND
                      (RCAMasterFinal.ResolutionCode < 9)
 AND RCAMasterFinal.XNAC_Major = 'STP'

Group By    RCAMasterFinal.XNAC_Major, XNAC_Lookup.XNAC_Name, RCAMasterFinal.Model

0
 
LVL 1

Author Closing Comment

by:Mswetsky
ID: 34927418
Wow, That is a great solution from a different perspective than I was looking. Thank you so much!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 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