CODEWITHSUNDEEP
sqlsql-server
Deepleeqe
Deepleeqe

Reputation: 317

SQL Server : error MSG 102 and MSG 156

Please help me. I wrote the query

with cte as
(   
    select 
        *, 
        row_number() over ( partition by product order by date desc ) as rownumber
    from 
        saleslist
    where 
        datediff( month, date, getdate() ) < 2 
    )
    select 
        product, 
        ((max(case when rownumber = 1 then price end) - 
          max(case when rownumber = maxn then price)) /
          max(case when rownumber = maxn then price end)
       )
  from 
      (select cte.*, max(rownumber) over (partition by product) as maxn   
       from cte) 
group by product

and I got the following messages

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ')'.

Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'group'.

Could someone please kindly tell me how to fix this?

Upvotes: 0

Views: 260

Answers (3)

dnoeth
dnoeth

Reputation: 60482

SQL server 2014 supports FIRST/LAST_VALUE

with cte as 
  (   
      select *,
           product, 
           price as first_price, 
           row_number() over (partition by product order by date) as rownumber,
           last_value(price) -- price of the row with the latest date
           over (partition by product 
                 order by date rows
                 rows between unbounded preceding and unbounded following) as last_price
           count(*) over (partition by product) as maxrn
      from saleslist sl
      where datediff( month, date, getdate() ) < 2 
   )
select product, 
       (last_price - first_price) / first_price
from cte
where rownumber = 1;

Upvotes: 1

M.Ali
M.Ali

Reputation: 69564

with cte as
(   
    select *, 
    row_number() over ( partition by product order by date desc ) as rownumber
    from saleslist
    where datediff( month, [date], getdate() ) < 2 
    )
select product, 
     (
     (max(case when rownumber = 1 then price end) - 
         max(case when rownumber = maxn then price end) --< missing end here 
        )  /
         max(case when rownumber = maxn then price end)
       )
from 
(select cte.*, max(rownumber) over (partition by product) as maxn 
   from cte ) t  --< needs an alias here
group by product

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You need a table alias for the subquery. However, your query is overcomplicated. The maximum row number is the count(*) of the rows:

with cte as (   
      select sl.*, 
             row_number() over (partition by product order by date desc) as rownumber,
             count(*) over (partition by product) as maxrn
      from saleslist sl
      where datediff( month, date, getdate() ) < 2 
    )
select product, 
     (
     (max(case when rownumber = 1 then price end) - 
         max(case when rownumber = maxn then price)
        )  /
         max(case when rownumber = maxn then price end)
       )
from cte
group by product;

Upvotes: 0

Related Questions