[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

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

0
Mswetsky
Asked:
Mswetsky
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
MswetskyAuthor Commented:
Wow, That is a great solution from a different perspective than I was looking. Thank you so much!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now