Hi Folks,I have created the query below for a report that I need to produce for call failure stats. The query works fine and I can get the report outputting correctly. However I am getting a lot of stats for calls that have not failied i.e. U, C X or M have 0 values.I need to see stats where calls that have sum value greater than 1 for Reason codes U,C,X and M. But I do not know how to implement this. Can someone amend my query to allow this please. Much appreciated.DECLARE @Starttime datetime DECLARE @Endtime datetimeSet @Starttime = '2011-03-08 00:00:00'Set @Endtime = '2011-03-09 00:00:00'SELECT Diallednumber as RoutingNumber, COUNT(*) AS TotalCalls,SUM(CASE WHEN Reason = 'U' THEN 1 ELSE 0 END) AS NU,SUM(CASE WHEN Reason = 'C' THEN 1 ELSE 0 END) AS Congestion,SUM(CASE WHEN Reason = 'X' THEN 1 ELSE 0 END) AS Unknown,SUM(CASE WHEN Reason = 'M' THEN 1 ELSE 0 END) AS UnsupportedMediaFROM CallTWhere starttime between @starttime and @endtimeand (DialledNumber like '98%' or RoutingNumber like '+44206%')and isinbound = '0'GROUP BY originaldiallednumberorder by NU desc
Filter results by Sum variables
Posted - 2011-03-10 : 11:16:31
