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.