Home Invalid column name even though variable specified in INSERT and SELECT
Reply: 0

Invalid column name even though variable specified in INSERT and SELECT

Harsh Patel
1#
Harsh Patel Published in 2018-01-10 18:13:35Z

I declared the variable in the insert and select statement and it still throws
The error pops at the last insert statement.

DECLARE
    @CohortName VARCHAR(MAX) = 'Reg w/in 7d & NotificationOnly'
    ,@ChurnDay INT = 90
    ,@EnrollMonth_Start INT=3172
    ,@EnrollMonth_End INT=3173
    ,@Month_Seq_No INT
    ,@TempId int
    ,@MonthStartDate date
    ,@MonthEndDate date;

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    DROP TABLE #Temp;

IF OBJECT_ID('tempdb..#Temp_Final') IS NOT NULL
    DROP TABLE #Temp_Final;

CREATE TABLE #Temp
(
    Id int identity(1,1) primary key,
    [month_seq_no] int,
    IsProcessed bit DEFAULT 0
)

CREATE TABLE #Temp_Final
(
    Cohort_Name varchar(100),
    Year_Month_Name varchar(50),
    [month_seq_no] int,
    Event_Name varchar(50),
    Denominator bigint,
    Numerator bigint
)

INSERT INTO #Temp ([month_seq_no])
SELECT
    DISTINCT
    month_seq_no 
FROM [CDP_All].[Date_Dim] 
WHERE Month_Seq_No >=@EnrollMonth_Start 
    AND Month_Seq_No<=@EnrollMonth_End

WHILE EXISTS(SELECT 1 FROM #Temp WHERE IsProcessed=0)
BEGIN
    SELECT TOP 1
        @Month_Seq_No=Month_seq_no
    FROM #Temp WHERE IsProcessed=0

    SELECT 
        @MonthStartDate=cast(start_of_month_date as date)
        ,@MonthEndDate=cast(end_of_month_date as date)
    from cdp_all.Date_Dim(nolock) where month_seq_no=@Month_Seq_No

;WITH CTE_Notif AS
(
    SELECT 
        mh.subid, event_name, count(mh.event_name) as event_count,
        min(CONVERT(date,mh.event_date)) as Event_Date
    FROM 
        cdp_sprint.[Notifications_Stage_20180105] mh(NOLOCK)
    WHERE 
        CONVERT(date,mh.event_date) >= @MonthStartDate
        AND CONVERT(date,mh.event_date) < dateadd(day,(7+30+1),@MonthEndDate)
        AND subid IS NOT NULL
    group by 
        mh.subid, mh.event_name
)
,CTE_C2Ch AS
(
    SELECT 
        mh.subid, event_name, count(mh.event_name) as event_count,
        min(CONVERT(date,mh.event_date)) as Event_Date
    FROM 
        cdp_sprint.[Notifications_Chat_Stage_20180105] mh(NOLOCK)
    WHERE 
        CONVERT(date,mh.event_date) >= @MonthStartDate
        AND CONVERT(date,mh.event_date) < dateadd(day,(7+30+1),@MonthEndDate)
        AND subid IS NOT NULL
    group by 
        mh.subid, mh.event_name
)
,CTE_C2C AS
(
    SELECT 
        p.MDN AS mdn
        ,min(p.EventDate) event_date
    FROM
        cdp_sprint.Subscriber_Profile enroll        
        JOIN cdp_sprint.vPSS_Data p
        ON enroll.MDN = p.MDN
    WHERE 
        month_seq_no =@Month_Seq_No
        AND p.Channel ='App'
        --AND p.Category = 'Supported' 
        AND p.call_duration > 0
        AND DATEDIFF(Day,enroll.Enroll_Date,p.EventDate) >= -3 
    GROUP BY
        p.MDN
)

INSERT INTO #Temp_Final
    (Cohort_Name
    ,[month_seq_no]
    ,Year_Month_Name
    ,Event_Name  **The error pops up here. why?**
    ,Denominator
    ,Numerator)
SELECT 
    @CohortName CohortName,
    Month_Seq_No,
    Year_Month_Name,
    COALESCE(notif.event_name, c2ch.event_name) as Event_Name,
    Enrollment_Master_Code AS Denominator,
    CASE 
        WHEN (drop_flag = 'Drop' AND (Claim_Date IS NULL OR Claim_Enroll > (@ChurnDay+Home_Enroll))
            AND (isnull(datediff(day,Enroll_Date,drop_date),98) <=  (@ChurnDay+Home_Enroll))) 
            THEN Enrollment_Master_Code END 
            AS NumeratorDrop_WOClaims
FROM 
    cdp_sprint.Subscriber_Profile (NOLOCK) enroll

    JOIN CTE_NOTIF notif 
    ON enroll.Enrollment_Master_Code = notif.subid

    LEFT OUTER JOIN CTE_C2c c2c
    ON enroll.mdn = c2c.mdn

    LEFT OUTER JOIN CTE_C2ch c2ch
    ON enroll.Enrollment_Master_Code = c2ch.subid
WHERE 
    enroll.Month_Seq_No =@Month_Seq_No
    --AND (Claim_Date IS NULL OR Claim_Enroll > (@ChurnDay+Home_Enroll))
    AND (Home_Enroll between -3 and 7) 
    ---------------------------------------------------
    AND (DATEDIFF(DAY,enroll.Enroll_Date,notif.event_date) <= Home_Enroll+30)
    AND (c2c.event_date IS NULL OR DATEDIFF(DAY,enroll.Enroll_Date,c2c.event_date) > Home_Enroll+30)
    AND (c2ch.event_date IS NULL OR DATEDIFF(DAY,enroll.Enroll_Date,c2ch.event_date) > Home_Enroll+30)
--group by 
    --Month_Seq_No,
    --Year_Month_Name

--######

    UPDATE #Temp SET IsProcessed=1 FROM #Temp WHERE month_seq_no=@Month_Seq_No;
END

SELECT * FROM #Temp_Final order by month_seq_no;
--16min
--6min
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.36988 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO