Link to home
Start Free TrialLog in
Avatar of Mitch Swetsky
Mitch SwetskyFlag for United States of America

asked on

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

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

ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mitch Swetsky

ASKER

Wow, That is a great solution from a different perspective than I was looking. Thank you so much!