CODEWITHSUNDEEP
mysqlsqlsql-server
Rajan
Rajan

Reputation: 37

Getting error while implementing CASE WHEN statement in SQL

I have to write a query and check whether sales has increased (use yes) and decreased (use no in column SaleIncreased) for every salesman compared to his previous year's performance.

sample table output should be as follows

EmpID        salesman    year  SaleIncreased
7843921      John        2016   Null
7843921      John        2017   Yes 
7843934      Neil        2016   Null
7843934      Neil        2017   No

I have used self join with CASE WHEN statement as follows

select t1.empid, t1.salesman, t1.year
from Sales_temp as t1
inner join Sales_temp as t2
on t1.empid = t2.empid and t2.year = t1.year - 1 
case when t1.sale > t2.sale 
then 'Yes' 
else 'No' 
end as 'SaleIncreased'

I'm unable to get the desired output.

Upvotes: 0

Views: 45

Answers (2)

Sahi
Sahi

Reputation: 1484

Is this useful.?

DECLARE @tab1 TABLE(EMPID BIGINT,Saleman VARCHAR(100),[Year] BIGINT,Sales BIGINT)
INSERT INTO @tab1
SELECT 7843921,'John',2016,100 Union ALL
SELECT 7843921,'John',2017,150 Union ALL
SELECT 7843934,'Neil',2016,120 Union ALL
SELECT 7843934,'Neil',2017,90

Select *,CASE 
        WHEN LAG(Sales) OVER(Partition by EmpID order by [year]) IS NULL then NULL
        WHEN Sales - LAG(Sales) OVER(Partition by EmpID order by [year])>0 THEN 'Yes' 
        ELSE 'No' END 
from @tab1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522181

Your CASE expression appears to be out of place, and you probably intended for it to be in the SELECT clause:

SELECT
    t1.empid,
    t1.salesman,
    t1.year,
    CASE WHEN t1.sale > t2.sale 
         THEN 'Yes' 
         ELSE 'No' 
    END AS SaleIncreased
FROM Sales_temp AS t1
LEFT JOIN Sales_temp AS t2
    ON t1.empid = t2.empid AND t2.year = t1.year - 1
ORDER BY
    t1.empid,
    t1.year;

Another change I made is to use a left join instead of an inner join. This is important, because it would ensure that the earliest year records for each employee would appear in the result set (these would be the records having a NULL value for the increase in sales).

Upvotes: 1

Related Questions