Подскажите в каком направлении думать. Есть таблица, такого типа:
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
