I need to calculate the BLACKLOG_M value corresponding to the Snapdate of the first of the month. It becomes a constant value repeated for each line of the month
As it is explained in the picture:
SELECT T1.SNAP, T1.PERI, T1.ANNEE, T1.MOIS, T1.[BCKL], T2.[BCKL] AS BCK2,[NBR1]
,[NBR2]
FROM stg.FACTSALES AS T1 LEFT OUTER JOIN
(SELECT YEAR(SNAP) AS ANNEE, MONTH(SNAP) AS MOIS, [BCKL]
FROM stg.FACTSALES
WHERE (DAY(SNAP) = 1)) AS T2 ON T2.ANNEE = T1.ANNEE AND T2.MOIS = T1.MOIS
The script returns the correct values, but is there any another best idea or improvement?