CTE doesn't work in SSAS Cube. I want to find solution or convert it ti subquery
0
I write this CTE query and the explanation is: WITH TP AS (select c.ID, c.PeriodCId, c.PeriodName, c.Status, c.StatusChangeDate, CAST(c.StartDate AS DATE) AS StartDate, c.EndDate,c.PeriodCode, c.PeriodType, c.ParentCId, c.MarketId, c.ParentId, c.WD, LEFT(CONVERT(varchar, c.StartDate, 112), 6) AS YEARMONTH, (select count(*) from dTimePeriod c2 where c2.ParentId = c.ID and c2.Status='actv') as #children from dTimePeriod c where (MarketId = 7) ), TP2 AS ( SELECT * FROM TP WHERE #children='12' ), TP3 AS (SELECT TP.*, CASE WHEN (TP.WD IS NOT NULL) AND (TP.StartDate <= getdate()) AND TP2.ID=TP.ParentId THEN 18 ELSE NULL END AS WorkingDays FROM TP LEFT JOIN TP2 ON TP2.ID=TP.ParentId) select * from TP3 order by ID and this is the resu...
