Join/UNION ALL to show result in different columns

Question

I am fetching COUNT from 3 different table based on some conditions but to group them on time interval. (Like: 1 hour, 30 minutes.)

I need the following output:

Date            Interval    Success Un-Success  Closed  CLInotFound
2/20/2016   01:01 – 02:00   5          3           2    13
2/20/2016   02:01 – 03:00   14         9          23    5
2/20/2016   03:01 – 04:00   8         67          89    345
2/20/2016   04:01 – 05:00   2         23          92    12
2/20/2016   05:01 – 06:00   44        55          78    98
2/20/2016   06:01 – 07:00   12        87          56    445

I am able to calculate them separately but when I am trying to combine the result gets different.

Query 1 For Success & Un-Success:

SELECT CONVERT(VARCHAR(5), A.InsertionDate ,108) AS 'Interval',
    COUNT(CASE WHEN A.call_result = 0 then 1 ELSE NULL END) AS 'Success',
    COUNT(CASE WHEN A.call_result = 1 then 1 ELSE NULL END) AS 'Un-Success'
from dbo.AutoRectifier A 
WHERE CONVERT(DateTime,A.InsertionDate,101) BETWEEN '2016-02-19 02:10:35.000' AND '2016-02-19 07:15:35.000'
GROUP BY A.InsertionDate; 

Query 2 For Closed:

SELECT CONVERT(VARCHAR(5), C.DateAdded ,108) AS 'Interval',
 COUNT(*) AS 'Closed' FROM dbo.ChangeTicketState C 
WHERE C.SourceFlag = 'S-CNR' AND C.RET LIKE '%CLOSE%' 
AND C.DateAdded BETWEEN '2016-02-19 02:10:35.000' AND '2016-02-19 07:15:35.000'
GROUP BY C.DateAdded;

Query 3 For CLI Not Found:

SELECT CONVERT(VARCHAR(5), T.DateAdded ,108) AS 'Interval',
        COUNT(*) 'CLI Not Found' FROM dbo.TICKET_INFO T
     WHERE T.CONTACT_NUMBER = '' AND T.DateAdded BETWEEN '2016-02-19 02:10:35.000' AND '2016-02-19 07:15:35.000'
     GROUP BY T.DateAdded;

Answer 1

You have got several problems to solve in you question.

1. You have to produce a union result set from Query1, Query2, Query3 to group it. You can use UNION ALL for it but all 3 queries must have similar column list for it. So, add
   0 as Closed, 0 as CLInotFound
   to select-list of the Query1, add
   0 as Success, 0 as Un-Success, 0 as CLInotFound
   to select-list of the Query2 and add
   0 as Success, 0 as Un-Success, 0 as Closed
   to Query3 Then you can write
   select * from Query1 union all select * from Query2 union all select * from Query3

2. Don't convert date to varchar at Query1, Query2, Query3. Better return datetime from query to use it for grouping after union. So, query 1 will look like
   SELECT A.InsertionDate AS Date, ...
   Query2 -
   SELECT C.DateAdded AS Date, ...
   etc.

3. Then you can group results on per-hour basis, for instance using GROUP BY SUBSTRING(CONVERT(VARCHAR(20), Date ,120), 1, 13)

So, the result will look like
SELECT SUBSTRING(CONVERT(VARCHAR(20), Date ,120), 1, 13) as Interval, sum(Success) as sum(Un-Success) as, sum(Closed) as, sum(CLInotFound) as from ( select * from Query1 union all select * from Query2 union all select * from Query3 ) q GROUP BY SUBSTRING(CONVERT(VARCHAR(20), Date ,120), 1, 13)

Its result have slightly different format of Date and Interval field, but shows the idea. You can use GROUP BY DATEPART(yy, Date), DATEPART(mm, Date), DATEPART(dd, Date), DATEPART(hh, Date) instead of GROUP BY SUBSTRING(CONVERT(VARCHAR(20), Date ,120), 1, 13) and format if as you wish.

Also result set does not contain intervals that not present at original data. You can add Query4, containing all intervals required and zeros at all fields to fix it.