Set first period's initial date for all items of a Relation Id - FalconFT/FalconDocs GitHub Wiki

Update only the initial date of the first behavior value

When importing assets from AndBank we've to set the initial date to 01/01/2024 for all the first periods of behavior values. This script updates all initial dates for the firsts periods of a Relation ID to a specific value of Initial Date:

DECLARE @InitialDate DATETIME = '20240101'
DECLARE @BehaviorRelationId INT = 421

;WITH CTE_BV AS (
SELECT 
BV.*
FROM behaviors.BehaviorValues BV
INNER JOIN behaviors.BehaviorRelations R
ON R.BehaviorRelationId = BV.BehaviorRelationId
INNER JOIN behaviors.Behaviors B
ON B.BehaviorId = R.BehaviorId
WHERE 
B.HasHistory = 1 AND
BV.BehaviorRelationId = @BehaviorRelationId
),
CTE_FIRST_BV AS (
SELECT 
  T.*, 
ROW_NUMBER() OVER (PARTITION BY T.ItemValueId1 ORDER BY T.InitialDate) AS Num
FROM CTE_BV T
)
/*
SELECT T.BehaviorValueId FROM CTE_FIRST_BV T 
WHERE T.Num = 1 AND T.InitialDate <> @InitialDate
ORDER BY T.ItemValueId1
*/
UPDATE behaviors.BehaviorValues
SET InitialDate = @InitialDate
WHERE BehaviorValueId IN
(
SELECT T.BehaviorValueId FROM CTE_FIRST_BV T 
WHERE T.Num = 1 AND T.InitialDate <> @InitialDate
)