CODEWITHSUNDEEP
sql-server-2005
Axle
Axle

Reputation: 181

SQL Server 2005 Business intelligence use 2 datasets one report

Just need some help/advice with SQL Server 2005 and Visual Studio 2005.

Basically I have 2 datasets one which results in a total number of good calls and another which results in the number of bad calls.

I want to make a report which shows the rate of good to bad.

For call failures actstatus has "105"

SELECT Country, count(status) AS FailedCalls
FROM termactivity(nolock) 
INNER JOIN termconfig(NOLOCK) ON cfgterminalID = actterminalID
WHERE actstatus IN (105)
GROUP BY country
ORDER BY country

For good calls actstatus has "5" in its cell

SELECT Country, count(status) AS FailedCalls
FROM termactivity(nolock) 
INNER JOIN termconfig(NOLOCK) ON cfgterminalID = actterminalID
WHERE actstatus IN (5)
GROUP BY country
ORDER BY country

Now both of these result in tables with country name and numbers like below

 Country    fails
    AT  240
    BE  3
    CH  1
    CZ  23
    DE  5733
    ES  336
    FR  2
    IE  1066
    IT  7085
    NL  260
    NZ  89
    PT  790

    Country CallSuccess
    AT  5662
    BE  480
    CH  370
    CZ  1124
    DE  19412
    ES  7740
    FR  1976
    IE  26616
    IT  32764
    NL  4046
    NZ  114
    PT  6567

How can I add these to the one layout and eventually have a table like say below:

Country CallSuccess  fails
AT  5662                 240
BE  480                  10
CH  370                  22
CZ  1124                 112
DE  19412                888
ES  7740                 etc..
FR  1976
IE  26616
IT  32764
NL  4046
NZ  114
PT  6567

Upvotes: 0

Views: 384

Answers (2)

Steve Homer
Steve Homer

Reputation: 3922

I'd change your sql to return one dataset like this.

Select Country,
        Sum(Case When actstatus = 5 Then 1 Else 0 End) As FailedCalls,
        Sum(Case When actstatus = 105 Then 1 Else 0 End) As SucceededCalls
From  termactivity(nolock)  
    INNER JOIN termconfig(NOLOCK) ON cfgterminalID = actterminalID 
Where  actstatus IN (5, 105)
Group By Country
order By Country

Upvotes: 1

gbn
gbn

Reputation: 432260

COUNT ignores NULL, and the CASE gies NULL for the implied ELSE condition

SELECT
   Country, 
   count(CASE WHEN actstatus = 105 THEN status END) AS FailedCalls,
   count(CASE WHEN actstatus = 5 THEN status END) AS GoodCalls
FROM
   termactivity(nolock) 
    INNER JOIN
   termconfig(NOLOCK) ON cfgterminalID = actterminalID
where actstatus IN (105, 5)
GROUP BY country
ORDER BY country

Upvotes: 1

Related Questions