SQL query to return top X sequential descending rows, by group, for a particular value

sqltsql

I need a query to return, by group, a true or false if the most recent x number of sequential rows, in descending date order, have a column with a false value where x can be different for each group.

For example, a Configuration table would have the number of records that have to match sequentially by companyId and serviceId:

CompanyId     ServiceId      NumberOfMatchingSequentialRecords
2             1              3
3             2              2

The table to query against, say Logging, might have the following data:

CompanyId     ServiceId     SuccessfulConnect(bit)     CreateDate (desc order)
2             1             0                          2009-12-09  9:54am
2             1             0                          2009-12-09  9:45am
2             1             0                          2009-12-09  9:36am
2             1             1                          2009-12-08 10:16am
2             1             1                          2009-12-07  3:24pm

3             2             0                          2009-10-15 8:54am
3             2             1                          2009-10-14 5:17pm
3             2             0                          2009-10-13 4:32am
3             2             1                          2009-10-13 1:19am

For the query to match, SuccessfulConnect must have 0/false values for the sequence by group (companyId, serviceId).

The result of the query would then be…

CompanyId     ServiceId     Alert (bit)
2             1             1
3             2             0

…because companyId=2, serviceId=1 would return a true as the 3 most recent consecutive records in descending date order, as defined in the Configuration table, all had SuccessfulConnect as false.

However, companyId=3 serviceId=2 would return a false because the 2 most recent consecutive records in descending date order, as defined in the Configuration table, did not both have false.

Best Answer

I think the following is what you need.

    SELECT 
        T.CompanyId, T.ServiceId, 
        CASE WHEN SUM(CAST(SuccessfulConnect AS int)) = 0 THEN 1 ELSE 0 END AS Alert
    FROM (
        SELECT
            CompanyId, ServiceId, SuccessfulConnect,
            ROW_NUMBER() OVER (PARTITION BY CompanyId, ServiceId 
                ORDER BY CreateDate DESC) AS intRow
        FROM Logging
    ) AS T
        INNER JOIN Configuration c ON c.CompanyId = T.CompanyId
            AND c.ServiceId = T.ServiceId
    WHERE intRow <= c.NumberOfMatchingSequentialRecords
    GROUP BY T.CompanyId, T.ServiceId, c.NumberOfMatchingSequentialRecords
    HAVING COUNT(*) >= C.NumberOfMatchingSequentialRecords

You can test it with:

CREATE TABLE Configuration (CompanyId int, ServiceId int, NumberOfMatchingSequentialRecords int)
CREATE TABLE Logging (CompanyId int, ServiceId int, SuccessfulConnect bit, CreateDate datetime)

INSERT Configuration VALUES (2, 1, 3)
INSERT Configuration VALUES (3, 2, 2)
INSERT Logging VALUES (2, 1, 0, '2009-12-09  9:54am')
INSERT Logging VALUES (2, 1, 0, '2009-12-09  9:45am')
INSERT Logging VALUES (2, 1, 0, '2009-12-09  9:36am')
INSERT Logging VALUES (2, 1, 1, '2009-12-08 10:16am')
INSERT Logging VALUES (2, 1, 1, '2009-12-07  3:24pm')
INSERT Logging VALUES (3, 2, 0, '2009-10-15 8:54am')
INSERT Logging VALUES (3, 2, 1, '2009-10-14 5:17pm')
INSERT Logging VALUES (3, 2, 0, '2009-10-13 4:32am')
INSERT Logging VALUES (3, 2, 1, '2009-10-13 1:19am')

SELECT 
    T.CompanyId, T.ServiceId, 
    CASE WHEN SUM(CAST(SuccessfulConnect AS int)) = 0 THEN 1 ELSE 0 END AS Alert
FROM (
    SELECT
        CompanyId, ServiceId, SuccessfulConnect,
        ROW_NUMBER() OVER (PARTITION BY CompanyId, ServiceId 
            ORDER BY CreateDate DESC) AS intRow
    FROM Logging
) AS T
    INNER JOIN Configuration c ON c.CompanyId = T.CompanyId
        AND c.ServiceId = T.ServiceId
WHERE intRow <= c.NumberOfMatchingSequentialRecords
GROUP BY T.CompanyId, T.ServiceId, c.NumberOfMatchingSequentialRecords
HAVING COUNT(*) >= C.NumberOfMatchingSequentialRecords

DROP TABLE Logging
DROP TABLE Configuration

This gives:

CompanyId    ServiceId    Alert
    2            1          1
    3            2          0
Related Topic