CODEWITHSUNDEEP
sqlsql-serverssms
psysky
psysky

Reputation: 3195

save query in temporary table in isq

I have the query

WITH cte AS 
(
    SELECT 
        *, 
        DATEPART(WEEKDAY, Dt) AS WeekDay,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SaleCount) 
                OVER (PARTITION BY ItemRelation, DocumentNum, DocumentYear) as PERCENTILE,
        AVG(SaleCount) OVER (PARTITION BY ItemRelation, DocumentNum, DocumentYear, DATEPART(WEEKDAY, Dt), IsPromo) AS AVG_WeekDay
    FROM 
        [Action].[dbo].[promo_data_copy]
)
UPDATE a 
SET SaleCount = cte.AVG_WeekDay
FROM CTE
JOIN [Action].[dbo].[promo_data_copy] a ON a.Dt = cte.dt
                                        AND a.ItemRelation = cte.ItemRelation 
                                        AND a.DocumentNum = cte.DocumentNum 
                                        AND a.DocumentYear = cte.DocumentYear 
                                        AND a.ispromo = cte.ispromo
WHERE CTE.PERCENTILE < CTE.SaleCount
  AND DATEPART(WEEKDAY, CTE.Dt) < 5
  AND CTE.ispromo = 0 ;

When I run it, my table promo_data_copy is updated. It is not right.

Tell me, please, how to do, that, final result will return in the temporary table, let "temp_table"?

Edit:

WITH cte AS 
(
    SELECT 
        *,
        DATEPART(WEEKDAY, Dt) AS WeekDay,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SaleCount) 
              OVER (PARTITION BY ItemRelation, DocumentNum, DocumentYear) as PERCENTILE,
        AVG(SaleCount) OVER (PARTITION BY ItemRelation, DocumentNum, DocumentYear, DATEPART(WEEKDAY, Dt), IsPromo) AS AVG_WeekDay
    FROM 
        [Action].[dbo].[promo_data_copy]
)
SELECT * 
INTO #temp_table 
FROM cte 

SET SaleCount = cte.AVG_WeekDay
FROM CTE
JOIN #temp_table a ON a.Dt = cte.dt
                   AND a.ItemRelation = cte.ItemRelation 
                   AND a.DocumentNum = cte.DocumentNum 
                   AND a.DocumentYear = cte.DocumentYear 
                   AND a.ispromo = cte.ispromo
WHERE CTE.PERCENTILE < CTE.SaleCount
  AND DATEPART(WEEKDAY, CTE.Dt) < 5
  AND CTE.ispromo = 0 ;

The error

Msg 102, level 15, state 1, line 12
Incorrect syntax near the "=" construct.

Edit #2:

WITH cte AS 
(
    SELECT 
        *,
        DATEPART(WEEKDAY, Dt) AS WeekDay,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SaleCount) 
              OVER (PARTITION BY ItemRelation, DocumentNum, DocumentYear) as PERCENTILE,
        AVG(SaleCount) OVER (PARTITION BY ItemRelation, DocumentNum, DocumentYear, DATEPART(WEEKDAY, Dt), IsPromo) AS AVG_WeekDay
    FROM 
        [Action].[dbo].[promo_data_copy]
)
SELECT * 
INTO #temp_table 
FROM cte 

UPDATE a
SET SaleCount = cte.AVG_WeekDay
FROM #temp_table
JOIN #temp_table a ON a.Dt = cte.dt
                   AND a.ItemRelation = cte.ItemRelation 
                   AND a.DocumentNum = cte.DocumentNum 
                   AND a.DocumentYear = cte.DocumentYear 
                   AND a.ispromo = cte.ispromo
WHERE CTE.PERCENTILE < CTE.SaleCount
  AND DATEPART(WEEKDAY, CTE.Dt) < 5
  AND CTE.ispromo = 0 ;

Then I get many errors of the same type

Msg 4104, level 16, state 1, line 11
Could not bind composite identifier "cte.dt"

Msg 4104, level 16, state 1, line 11
Could not bind composite identifier "cte.ItemRelation"

Msg 4104, level 16, state 1, line 11
Could not bind composite identifier "cte.DocumentNum"

How can I correct the query?

Upvotes: 0

Views: 122

Answers (1)

Ian-Fogelman
Ian-Fogelman

Reputation: 1605

If i am understanding you right you want the CTE to go into the temp table which can be accomplished with:

 with cte as (
    Select *,datePart(WEEKDAY,Dt) as WeekDay,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SaleCount) Over (partition by ItemRelation,
    DocumentNum, DocumentYear) as PERCENTILE,
    avg(SaleCount) over (Partition by ItemRelation, 
    DocumentNum, DocumentYear,datePart(WEEKDAY,Dt), IsPromo) as AVG_WeekDay
    From [Action].[dbo].[promo_data_copy])

select * into #temp_table from cte

Edit

If you want to output the records that were updated into a temp table then you need to create a temp table and add an output clause to the update statement.

create #temp_table
(
...
)

with cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SaleCount) Over (partition by ItemRelation,
DocumentNum, DocumentYear) as PERCENTILE,
avg(SaleCount) over (Partition by ItemRelation, 
DocumentNum, DocumentYear,datePart(WEEKDAY,Dt), IsPromo) as AVG_WeekDay
From [Action].[dbo].[promo_data_copy])
Update a 
Set SaleCount = cte.AVG_WeekDay
output inserted.* into #temp_table
From CTE
join [Action].[dbo].[promo_data_copy] a 
  on a.Dt = cte.dt
 and a.ItemRelation=cte.ItemRelation 
 and a.DocumentNum = cte.DocumentNum 
 and a.DocumentYear = cte.DocumentYear 
 and a.ispromo = cte.ispromo
Where CTE.PERCENTILE < CTE.SaleCount
and datePart(WEEKDAY,CTE.Dt) < 5
and CTE.ispromo = 0

Upvotes: 1

Related Questions