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