tags:

views:

217

answers:

1

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.

+2  A: 

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
Paul
Thanks for the reply but that didn't work. It returned all rows from the table and not the two rows I needed telling me if an alert is warranted or not. I think it's the correct way to start but the key is being able to look at the last x sequential records by company and service and determining of the SuccessfulConnect flag has been interrupted in that sequence as CompanyId = 3 and ServiceId = 2 show.
Jeff
Are you sure you copied it correctly? I tried it myself and got exactly the two rows that you have.The intRow <= c.NumberOfMatchingSequentialRecords limits us to the number of sequential rows, and if SUM(SoccessfulConnect) is 0 then they are uninterupted by a 1.
Paul
Just to make sure I've got the table structure correct, I've added to my answer the test script I used.
Paul
Thanks Paul. I'll try again Monday when I'm back at work.
Jeff
I apologize Paul. Apparently when I ran the code on my actual tables I had neglected to update the correct records with the NumberOfMatchingSequentialRecords values so they were null :(I greatly appreciate your help in this matter as your code works perfectly!
Jeff
That's good :-) Care to accept my answer? It's the button that looks like the outline of a tick.
Paul
Done! Thanks again for your help.
Jeff
Quite alright :-)
Paul
Should there be a "HAVING COUNT(*) >= C.NumberOfMatchingSequentialRecords" at the end?Otherwise, the query would return false positives for 'Alert' if you have y unsuccessful connections, where 0 < y < x. Example: INSERT INTO Configuration VALUES (4, 1, 2); INSERT INTO Logging VALUES (4, 1, 0, '2010-01-1'); -- now the query returns 1 in 'Alert' for CompanyId = 4, ServiceId = 1, but there was only one successful connection when NumberOfMatchingSequentialRecords = 2 for that company, service pair.
ArIck
You're quite right, there should. Now edited...thanks :-)
Paul