[Solved] Improve SQL Server query

by


I ran your query through a code formatter to help clean it up. I also change the variable declaration since you didn’t seem to understand what I was saying. For the record, the way you had it coded you might have missed some rows in the last few milliseconds of the day.

I changed the DATEDIFF function to use the datepart name spelled out because it is just too easy use the wrong abbreviation and get it wrong. I also simplified the calculation for the last column. The cast to money was not needed if you change the 1 – to 1.0 -. You should avoid using reserved words for object names and avoid spaces in column names. Let the front end do this kind of pretty formatting.

I also added the soon the be required WITH keyword when using table hints. (I would recommend understand what NOLOCK really means before using it).

DECLARE @timTimeout int
    , @iniDate date
    , @endDate date

SET @iniDate="2014-07-20"              
SET @endDate="2014-11-25"           
SET @timTimeout = 4000                       

SELECT MONTH(DateMsgIncome) as MyMonthColumn
    , 'Approved (0200)' = ISNULL(SUM(CASE CodMsgIncome WHEN '0200' THEN 1 END), 0)
    , 'Approved Off (0220)' = ISNULL(SUM(CASE CodMsgIncome WHEN '0220' THEN 1 END), 0)
    , 'Cancel (0400)' = ISNULL(SUM(CASE CodMsgIncome WHEN '0400' THEN 1 END), 0)
    , 'Regret (0420)' = ISNULL(SUM(CASE CodMsgIncome WHEN '0420' THEN 1 END), 0)
    , 'TOTAL' = COUNT(*)
    , 'Time-outs' = ISNULL(SUM(CASE WHEN DATEDIFF(MILLISECOND, DateMsgIncome, DateMsgSent) > @timTimeout THEN 1 END), 0)
    , 'Disponibility (%)' = (1.0 - ISNULL(SUM(CASE WHEN DATEDIFF(MILLISECOND, DateMsgIncome, DateMsgSent) > @timTimeout THEN 1 END), 0) / COUNT(*)) * 100
FROM [Message] WITH (NOLOCK) --Ack!!! I wouldn't let this fly on my system due to inconsistencies with this hint unless accuracy is not important (like 

WHERE DateMsgIncome >= @iniDate 
    AND DateMsgIncome < @endDate
    AND CodMsgIncome IN 
    (
        '0200'
        , '0220'
        , '0400'
        , '0420'
        , '0800'
        , '0900'
        , '9080'
        , '9085'
    ) 
    AND DescMsgIncome <> '0220'
GROUP BY MONTH(DateMsgIncome)

4

solved Improve SQL Server query