<# tw.local.parameters = new tw.object.listOf.SQLParameter(); #>
declare @wItemId bigint = ?; <# APCAddSqlParameter(tw.local.parameters, tw.local.wItemId); #>
declare @userName nvarchar(255) = ?; <# APCAddSqlParameter(tw.local.parameters, tw.local.userName); #>
declare @historyLength int = 10;
declare @itemId bigint;
declare @roomId bigint;
declare @homeId bigint;
select top 1
@itemId = ERP.itemId ,
@roomId = PRJ.roomId ,
@homeId = ENG.homeId
from WItem2EntityReportingPeriod WI2ERP
join EntityReportingPeriod ERP on WI2ERP.entityReportingPeriodId = ERP.entityReportingPeriodId
join Project PRJ on PRJ.roomId = ERP.roomId
join Engagement ENG on ENG.engagementId = PRJ.engagementId
where WI2ERP.wItemId = @wItemId ;
update UserActivity
set lastUpdated = CURRENT_TIMESTAMP
where userName = @userName
and itemId = @itemId
and roomId = @roomId
and homeId = @homeId ;
if @@ROWCOUNT = 0
begin
-- delete old rows for user from UserActivity
with uaDates as (
select userActivityId
, row_number() over (order by lastUpdated desc) as rowNumber
from UserActivity
where userName = @userName
)
delete UserActivity
from UserActivity UA
join uaDates on UA.userActivityId = uaDates.userActivityId
where uaDates.rowNumber >= @historyLength;
-- add new UserActivity
insert into UserActivity (userActivityId, userName, homeId , roomId , itemId , lastUpdated)
values (next value for UserActivity_Seq, @userName, @homeId , @roomId , @itemId , CURRENT_TIMESTAMP);
end
No comments:
Post a Comment