3/24/2014

T-SQL CTE Recursive Update DimDate



I was asked to update the OPSMTDValue field in the DimDate table.
The OPSMTDValue is the addition of the OPS value summed up by month, for each day.
Here's the query in T-SQL:

WITH CTE_OPSMTD (
       date_date
       ,OPSVALUE
       ,MTDOPSVALUE
       ,UnionType
       )
AS (
       SELECT FirstDayOfMonth
             ,opsvalue
             ,mtdopsvalue
             ,0 AS UnionType
       FROM dimdate
       WHERE opsvalue <> 0
             AND date_date = FirstDayOfMonth
      
       UNION ALL
      
       SELECT d.date_date
             ,opsvalue
             ,(
                    SELECT Sum(Opsvalue) AS mtdopsvalue
                    FROM dimDate
                    WHERE opsvalue <> 0
                           AND date_date >= d.FirstDayOfMonth
                           AND date_date <= d.date_date
                    )
             ,1 AS UnionType
       FROM dimdate d
       WHERE opsvalue <> 0
             AND d.date_date >= FirstDayOfMonth
             AND d.date_date <= LastDayOfMonth
       GROUP BY date_date
             ,opsvalue
             ,FirstDayOfMonth
             ,LastDayOfMonth
       )

       UPDATE dimdate
       SET MTDOPSVALUE = c.MTDOPSVALUE
       FROM dimdate AS dd
       JOIN CTE_OPSMTD AS c ON c.date_date = dd.date_date
       and c.OPSVALUE = dd.OPSVALUE;

No comments:

Post a Comment

Babalon