CTE in subQuery

Question

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

 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) <= @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) >= 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

My Output is like

Output Image

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

 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) <= @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) >= CONVERT(DATE, DATEADD(dd, -DAY(DATEADD(MONTH, 0, GETDATE())) + 1, DATEADD(MONTH, -6, GETDATE())))
group by PlanMonth 

ORDER BY OriginalDate

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

Why don't you just include IF EXISTS .... DROP for the temp table? You can also consider a TRY..CATCH logic to drop the temp table in case of failures — Raj
– Raj, Commented Apr 23, 2014 at 4:42
ya good idea. but i am trying to avoid temp table. I dont know which method is good. I think direct method is easy and good. Also like this, i have other procedures also using temp. So thinking to avoid it . Thats all. If temp table is the better approach i can go like that — Akhil
– Akhil, Commented Apr 23, 2014 at 4:59

Akhil · Accepted Answer · 2014-04-23 05:25:43Z

I finished the query. ACtually i just wrap the query as outer with out the cte. Cte must be the top in the query. The final query is

    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) <= @endDate
    )
    SELECT
    T1.PlanDate
    ,T1.[PastTrend - Plan]
    ,T1.[PastTrend - Actual]
    ,T1.[Current - Plan]
    ,T1.[Current - Actual]
    FROM
    (
    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) >= CONVERT(DATE, DATEADD(dd, -DAY(DATEADD(MONTH, 0, GETDATE())) + 1, DATEADD(MONTH, -6, GETDATE())))
    group by PlanMonth 
    ) T1 
    ORDER BY T1.OriginalDate

But i need to know the peroformance. When i execute this query with actual execution plan Query Cost (Relative to the batch) : 100% When i execute the first method using temp the query cost is 90%. Can anyone guid for this

Collectives™ on Stack Overflow

CTE in subQuery

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related