How can i compare the results of two select statements in TSQL (2014)?
my both queries:
x
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