Skip to content
Advertisement

Compare results of two selects statements

How can i compare the results of two select statements in TSQL (2014)?

my both queries:

SELECT CallDisposition, count(CallDisposition) as Count
  FROM [bs_Reporting].[dbo].[Termination_Call_Detail]
  where (DateTime between dateadd(minute,@timespan,convert(datetime2,(GETDATE())+ @count)) AND convert(datetime2,(GETDATE())+ @count))
  Group by CallDisposition

SELECT CallDisposition, count(CallDisposition) as Count
  FROM [bs_Reporting].[dbo].[Termination_Call_Detail]
  where DateTime >= dateadd(minute,@timespan,convert(datetime2,(GETDATE())))
  Group by CallDisposition

Results of Query 1:

+-----------------+-------+
| CallDisposition | Count |
+-----------------+-------+
|               2 |     2 |
|               3 |     8 |
|               4 |     8 |
|               7 |    21 |
|              10 |    16 |
|              13 |   738 |
|              14 |    45 |
|              15 |    14 |
|              19 |     8 |
|              28 |    41 |
|              29 |    12 |
|              52 |    76 |
|              55 |    1  |
+-----------------+-------+

Results of Query 2:

+-----------------+-------+
| CallDisposition | Count |
+-----------------+-------+
|               2 |     4 |
|               3 |     7 |
|               4 |     6 |
|               6 |     2 |
|               7 |    22 |
|              10 |     6 |
|              13 |   703 |
|              14 |    67 |
|              15 |    11 |
|              19 |     4 |
|              26 |     1 |
|              28 |    62 |
|              29 |    10 |
|              52 |    79 |
+-----------------+-------+

The main problem behind, the results of both queries can be different. Based on example above: CallDisposition 6 and 26 are missing in Query 1. CallDispoition 55 is missing in Query 2 Note: CallDispoition values 0-100 can be expected, maybe that helps?!

The expected result should look like that:

+-----------------+-------+
| CallDisposition | Count |
+-----------------+-------+
| 2               |     2 |
| 3               |     1 |
| 4               |     2 |
| 6               |     2 |
| 7               |     1 |
| 10              |    10 |
| ..              |       |
| ..              |       |
| ..              |       |
| 52              |     3 |
| 55              |     1 |
+-----------------+-------+

Advertisement

Answer

WITH S1 AS (SELECT CallDisposition, count(CallDisposition) as Count
  FROM [bs_Reporting].[dbo].[Termination_Call_Detail]
  where (DateTime between dateadd(minute,@timespan,convert(datetime2,(GETDATE())+ @count)) AND convert(datetime2,(GETDATE())+ @count))
  Group by CallDisposition
)
,S2 AS (
SELECT CallDisposition, count(CallDisposition) as Count
  FROM [bs_Reporting].[dbo].[Termination_Call_Detail]
  where DateTime >= dateadd(minute,@timespan,convert(datetime2,(GETDATE())))
  Group by CallDisposition
)
Select ISNULL(S1.CallDisposition,S2.CallDisposition) AS CallDisposition
      ,ABS(ISNULL(S1.COUNT,0)-ISNULL(S2.Count,0)) Count
FROM S1 FULL JOIN S2
      ON S1.CallDisposition=s2.CallDisposition
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement