Htykuut

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 result
CTE Image

I have recursive table called [dTimePeriod] this table contains different cycles and each cycle contains different number of periods,EX: one cycle has 8 periods another cycle has 12 periods and so on, I want if cycles contains 12 periods put to each period value = 18 and for others cycle periods null
and there are some another conditions but it's not the issue.

And when I put it in the SSAS cube doesn't work because the cube doesn't understand the CTE so I tried to find a solution but it doesn't work,
one of them to put this CTE in a view and call this view in the cube but the view doesn't work as well.
so I start to write it as subquery to make the cube able to understand it.
but I am stuck, I can't write this CTE in subquery statement

and this is the subquery where I stuck

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,

               CASE WHEN (c.WD IS NOT NULL) AND (c.StartDate <= getdate()) THEN 18           

               WHEN (c.WD IS NOT NULL) AND (c.StartDate > getdate()) THEN NULL ELSE c.WD END AS WorkingDays,



              case when (select sub.*  from 

             (select count(*) as children  from dTimePeriod c2   where c2.ParentId = c.ID and   c2.Status='actv' ) sub ) = 12 

              then 18 else null end as WWW





FROM           dTimePeriod c 

WHERE         (c.MarketId = 7)

Instead of using the SQL command directly in the data source for your cube, you can turn this query into a stored procedure and use that result set in the cube. For the SQL statement in the cube, just do an EXEC command as below. The database name isn't necessary if this database is already the initial catalog in the connection string of the data source.

EXEC YourDatabase.YourSchema.YourSP