SQL Выборка строк с нарушением

Подскажите в каком направлении думать. Есть таблица, такого типа:
create table table1( Id int primary key, User nvarchar(30), Typeofposition nvarchar(60), PayDate date, Price numeric(15,2) );
insert into table( Id, User, Typeofposition, PayDate, Price ) ( 1, 'user1', 'Взнос', '20120216', 1000 ), ( 2, 'user1', 'использование', '20120216', 0 ), ( 3, 'user1', 'использование', '20120215', 1000 ), ( 4, 'user2', 'использование', '20120220', 0 ), ( 5, 'user2', 'использование', '20120223', 300 ), ( 6, 'user2', 'Взнос', '20120321', 1000 ), ( 7, 'user3', 'использование', '20120521', 0 ), ( 8, 'user1', 'использование', '20151015', 0 )
По правилам, сервисом можно пользоваться двумя способами.
1) Либо сразу взносишь 1000 руб и можешь пользоваться сервисом бесплатно в течении 4 месяцев (заказ записывается в БД с ценой 0);
2)При каждом использовании оплачиваешь услугу
написать SQL-запрос, который вернет из таблицы table позиции «активность», оформленные с нарушениями. Так, в приведенной выше таблице нарушены позиции :
Id = 4 - "использование" с ценой 0 проведен раньше "взноса"; Id = 7 - "использование" с ценой 0 проведен без оплаты "взноса"; Id = 8 - "использование" с ценой 0 проведено спустя более 4 месяцев после оплаты "взноса".

Ответ

Вариант 1. Курсор
Можно отсортировать данные по пользователю и дате. Завести табличную переменную, где будем хранить нарушения, аналогичную по структуре исходной таблице.
Открыть по запросу курсор и по каждому пользователю при каждом взносе запоминать PayDate в переменную @last_pay_date, а для использований с нулевой стоимостью считать datediff от последней запомненной даты.
Для использований: если datediff превышает 4 месяца или @last_pay_date is null - добавлять строку в табличную переменную.
При переходе к следующему пользователю - обнулять @last_pay_date
В конце - вернуть содержимое.
declare @violations table( Id int, [User] nvarchar(30), Typeofposition nvarchar(60), PayDate date, Price numeric(15,2))
declare @cur_id int, @cur_user nvarchar(30), @prev_user nvarchar(30), @cur_type nvarchar(60), @cur_date date, @cur_price numeric(15,2), @last_pay_date date
DECLARE cur CURSOR FOR SELECT Id, [User], Typeofposition, PayDate, Price FROM table1 WHERE Typeofposition = N'Взнос' OR Price = 0 ORDER BY [User], PayDate
OPEN cur
FETCH NEXT FROM cur INTO @cur_id, @cur_user, @cur_type, @cur_date, @cur_price
WHILE @@FETCH_STATUS = 0 BEGIN IF @cur_user <> @prev_user OR @prev_user IS NULL BEGIN SET @last_pay_date = null SET @prev_user = @cur_user END
IF @cur_type = N'Взнос' SET @last_pay_date = @cur_date ELSE BEGIN IF @last_pay_date IS NULL OR datediff(month, @last_pay_date, @cur_date) > 4 INSERT INTO @violations VALUES (@cur_id, @cur_user, @cur_type, @cur_date, @cur_price) END
FETCH NEXT FROM cur INTO @cur_id, @cur_user, @cur_type, @cur_date, @cur_price END
CLOSE cur DEALLOCATE cur
SELECT * FROM @violations
Пример на SQLFiddle

Вариант 2. Оконная функция
Оконные функции позволяют вычислить некоторое агрегатное значение для каждой строки, опираясь на некоторые диапазоны строк, относительно текущей, или секции (похожие на группы в GROUP BY).
Рассмотрим поэтапно:
Сортируем по пользователю и дате. Отбрасываем использования с ненулевой оплатой.
SELECT * FROM table1 WHERE Typeofposition = N'Взнос' OR Price = 0 ORDER BY [User], PayDate Разбиваем на секции по пользователю с сортировкой по дате. Для каждой секции вычисляем максимальную скользящую дату внесения платежа (максимальную дату с начала секции и до текущей строки). Для "взносов" - это PayDate, для использований - null.
SELECT * , MAX( CASE Typeofposition WHEN N'Взнос' THEN PayDate ELSE null END ) OVER (PARTITION BY [User] ORDER BY PayDate) as LastPayDate, FROM table1 WHERE Typeofposition = N'Взнос' OR Price = 0 ORDER BY [User], PayDate Вычисляем datediff в месяцах между PayDate и максимальной скользящей датой внесения платежа.
SELECT * , datediff(month, MAX( CASE Typeofposition WHEN N'Взнос' THEN PayDate ELSE null END ) OVER (PARTITION BY [User] ORDER BY PayDate), PayDate) as MonthsSinceLastPayment
FROM table1 WHERE Typeofposition = N'Взнос' OR Price = 0 ORDER BY [User], PayDate OVER-выражения нельзя использовать в WHERE, поэтому обернем все в SELECT и выберем строки, где MonthsSinceLastPayment не задан (взносов еще не было) или превышает 4 месяца:
SELECT * FROM (SELECT * , datediff(month, MAX( CASE Typeofposition WHEN N'Взнос' THEN PayDate ELSE null END ) OVER (PARTITION BY [User] ORDER BY PayDate), PayDate) as MonthsSinceLastPayment
FROM table1 WHERE Typeofposition = N'Взнос' OR Price = 0 ) Violations WHERE MonthsSinceLastPayment >= 4 OR MonthsSinceLastPayment IS NULL
Пример на SQLFiddle