title: CTE in subQuery
body: <p>I have a scenario to display data from 2 tables combined. First table Named 'DayTable' consists of daily plan and actual. Second table named 'MonthTable' consists of Monthly plan and actual. I need to display last 6months data and the current month daily data. So i wrote the query like below i for the expected output</p>
  
  <pre><code> Declare @startdate date = CONVERT(DATE, DATEADD(dd, -DAY(DATEADD(MONTH, 0, GETDATE())) + 1, DATEADD(MONTH, 0, GETDATE()))) 
   Declare @endDate date = DATEADD(DAY, -DAY(DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE()))
  
  CREATE TABLE #TEMP
  (
   PlanDate NVARCHAR(100),
   [PastTrend - Plan] INT,
   [PastTrend - Actual] INT,
   [Current - Plan] INT,
   [Current - Actual] INT,
  )
  
  ;With cte
  as
  (
      Select @startdate sDate
      Union All
      Select DATEADD(day,1,sDate) From cte where DATEADD(day,1,sDate) &lt;= @endDate
  )
  INSERT INTO #TEMP
  SELECT 
  REPLACE(CONVERT(CHAR(6), A.sDate, 106),' ',' - ') PlanDate 
  ,NULL AS [PastTrend - Plan]
  ,NULL AS [PastTrend - Actual]
  ,SUM(B.PlanQuantity) AS [Current - Plan]
  ,SUM(B.Actual) AS [Current - Actual]
  FROM cte A 
  LEFT OUTER JOIN DayTable B 
  ON A.sDate = CONVERT(DATE,B.PlanDate) 
  GROUP BY A.sDate 
  --ORDER BY A.sDate
  
  
  SELECT
  *
  
  FROM
  (
  SELECT 
  CONVERT(CHAR(3), datename(month,PlanMonth)) + ' ' + RIGHT(CONVERT(VARCHAR(4), YEAR(PlanMonth)), 2)  AS PlanDate
  ,SUM(PlanQuantity) AS [PastTrend - Plan]
  ,SUM(Actual) AS [PastTrend - Actual]
  ,NULL AS [Current - Plan]
  ,NULL AS [Current - Actual]
  FROM 
  MonthTable 
  WHERE CONVERT(DATE, PlanMonth) &gt;= CONVERT(DATE, DATEADD(dd, -DAY(DATEADD(MONTH, 0, GETDATE())) + 1, DATEADD(MONTH, -6, GETDATE())))
  group by PlanMonth 
  
  
  UNION ALL
  
  SELECT 
  PlanDate
  ,[PastTrend - Plan]
  ,[PastTrend - Actual]
  ,[Current - Plan]
  ,[Current - Actual]
  FROM 
  #TEMP
  
  ) T1
  
  
   DROP TABLE #TEMP
  </code></pre>
  
  <p>My Output is like </p>
  
  <p><img src="https://i.stack.imgur.com/VnHfj.png" alt="Output Image"></p>
  
  <p>Now i am thining to avoid temp table concept because if any failure after create temp table it will not drop. So rewrite the query like below</p>
  
  <pre><code> Declare @startdate date = CONVERT(DATE, DATEADD(dd, -DAY(DATEADD(MONTH, 0, GETDATE())) + 1, DATEADD(MONTH, 0, GETDATE()))) 
   Declare @endDate date = DATEADD(DAY, -DAY(DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE()))
  
  ;With cte
  as
  (
      Select @startdate sDate
      Union All
      Select DATEADD(day,1,sDate) From cte where DATEADD(day,1,sDate) &lt;= @endDate
  )
  SELECT 
  A.sDate AS OriginalDate
  ,REPLACE(CONVERT(CHAR(6), A.sDate, 106),' ',' - ') PlanDate 
  ,NULL AS [PastTrend - Plan]
  ,NULL AS [PastTrend - Actual]
  ,SUM(B.PlanQuantity) AS [Current - Plan]
  ,SUM(B.Actual) AS [Current - Actual]
  FROM cte A 
  LEFT OUTER JOIN DayTable B 
  ON A.sDate = CONVERT(DATE,B.PlanDate) 
  GROUP BY A.sDate 
  
  UNION ALL
  
  SELECT 
  PlanMonth AS OriginalDate
  ,CONVERT(CHAR(3), datename(month,PlanMonth)) + ' ' + RIGHT(CONVERT(VARCHAR(4), YEAR(PlanMonth)), 2)  AS PlanDate
  ,SUM(PlanQuantity) AS [PastTrend - Plan]
  ,SUM(Actual) AS [PastTrend - Actual]
  ,NULL AS [Current - Plan]
  ,NULL AS [Current - Actual]
  FROM 
  MonthTable 
  WHERE CONVERT(DATE, PlanMonth) &gt;= CONVERT(DATE, DATEADD(dd, -DAY(DATEADD(MONTH, 0, GETDATE())) + 1, DATEADD(MONTH, -6, GETDATE())))
  group by PlanMonth 
  
  ORDER BY OriginalDate
  </code></pre>
  
  <p>But here i have problem. In the output i dont need OriginalDate. How to avoid this. For this i can wrap the union output to a select query but how can i got error in cte. Please guid me. Also suggest which method is best one</p>