CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Foxocube
Foxocube

Reputation: 740

SQL Query for each date in a range

I currently have an SQL query that looks like this:

SELECT CONVERT(DECIMAL(5,1),(
    CONVERT(DECIMAL(6,1),(
        SELECT COUNT(*)
        FROM Requests
        LEFT JOIN RequestSLA ON Requests.RequestId = RequestSLA.RequestId
        WHERE DateLogged between '2015/03/01' and '2015/08/17'
        AND RespondBy <= Responded
    ))/CONVERT(DECIMAL(6,1),(
        SELECT COUNT(*)
        FROM Requests
        WHERE DateLogged between '2015/03/01' and '2015/08/17'
    )))
*100)

This returns the percentage of tickets that breached their responded SLA (where Responded is after RespondBy) for all tickets in the range of dates supplied. Is it possible to adapt this query to show the percentage for each day separately? I.E. something like this:

Date       Percentage
2015-03-01  10
2015-03-02  7

etc...

I tried using a GROUP BYclause, but this didn't work because the DateLogged (which is what I would be grouping by) is not included in the output of the statement

Upvotes: 0

Views: 304

Answers (1)

GarethD
GarethD

Reputation: 69759

Use a single query rather than two correlated sub-selects, then you can use GROUP BY DateLogged, you just need to put a case expression within one of your counts:

SELECT  r.DateLogged,
        Total = COUNT(*),
        BreachedSLA = COUNT(CASE WHEN sla.RespondBy <= sla.Responded THEN 1 END),
        PercentBreached = CONVERT(DECIMAL(6, 1), 100.0 * COUNT(CASE WHEN sla.RespondBy <= sla.Responded THEN 1 END) / COUNT(*))
FROM    Requests AS r
        LEFT JOIN RequestSLA AS sla
            ON r.RequestId = sla.RequestId
WHERE   r.DateLogged >= '20150301'
AND     r.DateLogged <= '20150817'
GROUP BY r.DateLogged;

N.B, I have changed your date format to the completely culture independent format yyyyMMdd, this is the only independent format for DATETIME and SMALLDATETIME in SQL Server, so although marginally less legible, it guarantees correct conversion, with certain regional settings the format yyyy/MM/dd will throw an error, or worse it will be converted to an unintended date.

SET DATEFORMAT DMY;
SELECT  CONVERT(DATETIME, '2015/08/17')

I have also changed your BETWEEN clause to >= and <=, not because it is any different, but because it is easier to convert to an open ended date range, e.g.

WHERE   r.DateLogged >= '20150301'
AND     r.DateLogged < '20150818'

With between '2015/03/01' and '2015/08/17', you might get a date logged of 2015/08/17 10:00, that isn't being returned, and it is often not intuitive as to why. For more reading see What do BETWEEN and the devil have in common?.

Upvotes: 3

Related Questions