Код:
DECLARE @start datetime = DATEADD(day,-31,GETDATE())
DECLARE @stop datetime = DATEADD(day,-30,GETDATE())
DECLARE @date datetime = GETDATE()
DECLARE @unit int = 12--(SELECT [UNIT] FROM [gkArcade].[gk].[UNITS] WHERE [NAME] = 'Очки-Бонусы')
DECLARE @activity int = 329--(SELECT [ACTIVITY] FROM [gkArcade].[gk].[ACTIVITIES] WHERE [NAME] = 'Списание очков с истекшим сроком действия')
DECLARE @t_guid uniqueidentifier
DECLARE @value money, @bonus money, @new_bonus money
DECLARE @card int, @level int, @deal int, @EMPLOYEE int
DECLARE REM_BONUS CURSOR READ_ONLY FOR
SELECT T.[CARD] AS [CARD], C.[Level] AS [LEVEL], T.[EMPLOYEE] AS [EMPLOYEE], C.[Bonus] AS [BONUS]
FROM [gkArcade].[gk].[GK_TRANSACTS] T
LEFT JOIN [gkArcade].[gk].[GK_CARDS] C ON T.[CARD] = C.[Card]
WHERE [ACTIVITY]>400 AND [ACTIVITY]<500 AND [DATE]>@start AND [DATE]<@stop AND C.[Bonus] > 0
SET @t_guid = newid()
SET @deal = (SELECT TOP 1 [DEAL] FROM [gkArcade].[gk].[GK_TRANSACTS] ORDER BY [DATE] DESC)+1
PRINT @t_guid
OPEN REM_BONUS
FETCH NEXT FROM REM_BONUS INTO @card, @level, @EMPLOYEE, @bonus
WHILE (@@FETCH_STATUS <> -1) BEGIN
SET @new_bonus = (
SELECT SUM([VALUE]) FROM [gkArcade].[gk].[GK_TRANSACTS]
WHERE [ACTIVITY]>400 AND [ACTIVITY]<500 AND [DATE]>@stop AND [DATE]<@date AND [CARD] = @card)
IF @bonus > @new_bonus BEGIN
SET @value = @bonus - @new_bonus
INSERT INTO [gkArcade].[gk].[GK_TRANSACTS]
([DATE]
,[ACTIVITY]
,[CARD]
,[ACCOUNT_TYPE]
,[VALUE]
,[QUANT]
,[CREATOR]
,[CREATORADDR]
,[DEAL]
,[EMPLOYEE]
,[LEVEL]
,[ARCADE]
,[GUID]
,[TRANSACT_GUID])
VALUES
(@date
,@activity
,@card
,@unit
,-@VALUE
,1
,1
,1
,@deal
,@EMPLOYEE
,@LEVEL
,1
,newid()
,@t_guid)
INSERT INTO [gkArcade].[gk].[TRANSACTS] (
[DATE]
,[ACTIVITY]
,[CARD]
,[EMPLOYEE]
,[VALUE1]
,[UNIT1]
,[QUANT]
,[CREATOR]
,[CREATORADDR]
,[LEVEL]
,[DEAL]
,[GUID]
,[TRANSACT_GUID])
VALUES(@date,
@activity,
@card,
@EMPLOYEE,
@value,
@unit,
1,
1,
1,
@level,
@deal,
newid(),
@t_guid)
UPDATE [gkArcade].[gk].[GK_CARDS] SET [Bonus] = [Bonus] - @value WHERE [Card] = @card
END
FETCH NEXT FROM REM_BONUS INTO @card, @level, @EMPLOYEE, @bonus
END
CLOSE REM_BONUS
Хотя это еще надо править и так далее. Исполнять нужно этот скрипт ночью каждый день