CODEWITHSUNDEEP
sql-servercreate-table
mql4beginner
mql4beginner

Reputation: 2233

How to create a table based on query with variable declaration in MSSQL

I would like to create a table for a funnel query that has a declaration for days variable.If I use the query without the create statement the query runs fine. When I tried to add the select * into ..from I get two errors:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'declare'. 
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ')'.

How can save the query with the days declaration into a new table

select * into tbl60D_AlertsFunnel from (
    declare @d date = getdate() - 60
    select sum(IP) AS IP ,sum(sentCount) sentCount, sum(readCount) readCount, sum(NumberOfOpensPerEmail) OpenNumber
    from (
    select COUNT(distinct CAST(sm.IP AS nvarchar(20))) as IP
      , count(distinct v.iditem) sentCount
      , MAX(cast(sm.OpenDate as date)) as OpenDateShort -- v.iditem is for sent, sm.OpenDate IS FOR OPEN
      ,count(cast(sm.OpenDate as date)) as NumberOfOpensPerEmail
      , count(distinct sm.iditem) readCount
    from fff.dbo.v_rep_MessageQueue v (nolock)
     left join [FF].[dbo].[tblMessageOpenedSMTP] sm
      on v.IdItem = sm.iditem
    -- and cast(sm.OpenDate as date)  > @d
    where v.IdMessageType = 20
     and V.DateScheduled > @d
     group by sm.IP
    ) d
    ) j

Upvotes: 1

Views: 53

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

As had been pointed out in comments, your query should be like

declare @d date = getdate() - 60 -- outside the select


select * into tbl60D_AlertsFunnel from (

    select sum(IP) AS IP ,sum(sentCount) sentCount, sum(readCount) readCount, sum(NumberOfOpensPerEmail) OpenNumber
    from (
    select COUNT(distinct CAST(sm.IP AS nvarchar(20))) as IP
      , count(distinct v.iditem) sentCount
      , MAX(cast(sm.OpenDate as date)) as OpenDateShort -- v.iditem is for sent, sm.OpenDate IS FOR OPEN
      ,count(cast(sm.OpenDate as date)) as NumberOfOpensPerEmail
      , count(distinct sm.iditem) readCount
    from fff.dbo.v_rep_MessageQueue v (nolock)
     left join [FF].[dbo].[tblMessageOpenedSMTP] sm
      on v.IdItem = sm.iditem
    -- and cast(sm.OpenDate as date)  > @d
    where v.IdMessageType = 20
     and V.DateScheduled > @d
     group by sm.IP
    ) d
    ) j

Explanation:

Your problem was that while this query worked for you

declare @d date = getdate() - 60
    select sum(IP) AS IP ,sum(sentCount) sentCount, sum(readCount) readCount, sum(NumberOfOpensPerEmail) OpenNumber
    from (
    select COUNT(distinct CAST(sm.IP AS nvarchar(20))) as IP
      , count(distinct v.iditem) sentCount
      , MAX(cast(sm.OpenDate as date)) as OpenDateShort -- v.iditem is for sent, sm.OpenDate IS FOR OPEN
      ,count(cast(sm.OpenDate as date)) as NumberOfOpensPerEmail
      , count(distinct sm.iditem) readCount
    from fff.dbo.v_rep_MessageQueue v (nolock)
     left join [FF].[dbo].[tblMessageOpenedSMTP] sm
      on v.IdItem = sm.iditem
    -- and cast(sm.OpenDate as date)  > @d
    where v.IdMessageType = 20
     and V.DateScheduled > @d
     group by sm.IP
    ) d

you wanted to nest this as sub query to select into statement, but failed to move the declaration outside.

Upvotes: 1

Related Questions