select max(date) from UserActionEvent where dtype = 'CSE'

select *
from UserActionEvent
where UserActionEvent.dtype='CSE' and
UserActionEvent.date>='20200301' and UserActionEvent.date<='20201231'

select *
від UserActionEvent
where dtype = 'CSE' and
cashregnumber in ('7') and --НОМЕР ФР
session_number in ('749') and --НОМЕР ЗМІН
date in ('20220116', '20220116'); --ДАТА

select *
from UserActionEvent
where UserActionEvent.dtype='CSE' and
UserActionEvent.closeDate is null and
UserActionEvent.date>='20100101' and UserActionEvent.date<='20211231'

update UserActionEvent
set UserActionEvent.closeDate='20180101 23:00:00.000' -- Дата та час закриття для незачиненої касової зміни
,UserActionEvent.responsibleUser = UserActionEvent.manager
--,UserActionEvent.cashRemain=0 -- тільки якщо потрібно прибрати залишок за касовою зміною
,UserActionEvent.revision=(Select revision from DBVersion) -- Необхідно, тільки якщо РМС підключений до Чейна, інакше не провантажиться. Якщо Чейну немає, то рядок можна забрати
where UserActionEvent.dtype='CSE' and
UserActionEvent.closeDate is null and
id='' -- вставити між апострофами ID, знайдений у першому запиті

update UserActionEvent 
set revision=-revision 
where revision>0 and id=''

- Закриття КС за часом закриття останнього чека в цій КС, Кс без чеків пропускається
DECLARE @CountSession INT, @SessionId uniqueidentifier ,@i int, @y int, @datetime datetime

set @CountSession = (select count (*) from UserActionEvent where dtype = 'CSE' and session_id in
(select uae.session_id як countSession from UserActionEvent як uae LEFT OUTER JOIN OrderPaymentEvent як OP ON uae.session_id = ope.session_id
  where dtype = 'CSE' і closeDate is null and (uae.date BETWEEN '20000101' and '20210723') group by uae.session_id having count(ope.session_id) >0 ));
 
set @ i = 0;

WHILE @CountSession > @i
begin
set @SessionId = (select TOP 1 uae.session_id як countSession від UserActionEvent як uae
where dtype = 'CSE' and closeDate is null and (uae.date BETWEEN '20000101' and '20210723') group by uae.session_id having count(ope.session_id) >0 )
set @datetime = (select top 1 closeTime from OrderPaymentEvent where session_id = @SessionId order by closetime desc)

update UserActionEvent set closeDate = @datetime, responsibleUser = manager,
--cashRemain = 0, --тільки якщо необхідно прибрати залишок за касовою зміною
revision = (Select revision from DBVersion) --Необхідно, тільки якщо РМС підключений до Чейна, інакше не провантажиться. Якщо Чейну немає, то рядок можна забрати
where dtype = 'CSE' and closeDate is null and id = (select id from UserActionEvent where session_id = @SessionId and dtype = 'CSE' )
set @i = @i +1
end

select session_id
into session_tmp
from UserActionEvent
where dtype = 'CSE' and
cashregnumber in ('999') and --НОМЕР ФР
session_number in ('1', '2', '3') and --НОМЕРИ ЗМІН
date in ('20210523', '20210524'); --ДАТА

delete from OrderPaymentEvent
where session_id in (select session_id from session_tmp);

delete from ItemSaleEvent
where session_id in (select session_id from session_tmp);

delete from AccountingTransaction
where session_id in (select session_id from session_tmp);

delete from SalesDocument
where sessionid in (select session_id from session_tmp);

delete from SalesDocumentItem
where invoice_id not in (select id from SalesDocument);

delete from WriteoffDocument
where sessionid in (select session_id from session_tmp);

delete from WriteoffDocumentItem
where writeoffDocument_id not in (select id from WriteoffDocument);

delete from UserActionEvent
where session_id in (select session_id from session_tmp);

drop table session_tmp;

delete from OrderBonusEvent
where order_id not in (select "order" from OrderPaymentEvent);

delete from SessionRevenueEntry
where closeEvent not in (select id from UserActionEvent where dtype = 'CSE');

truncate table ATransactionSum;

select * from UserActionEvent
where date between '2022-08-15' and '2022-08-15'
and dtype='CSE'

Update UserActionEvent
set revision=( select revision from dbversion)+1,
lastModifyNode=NULL,
cashRemain=0,
sessionStartCash =0
where dtype = 'CSE' and id='D434309D-CBD7-4296-9EAA-4E3A28937F0E'