Getting error while implementing CASE WHEN statement in SQL

Question

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.

First, decide which database product you're using. mysql and sql-server are two distinctly different products. Questions are rarely correctly tagged with both — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 24, 2018 at 6:35
@Damien_The_Unbeliever Although with the advent of MySQL 8, this is less of a bad thing now ... sort of ^ ^ — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 24, 2018 at 6:41

Tim Biegeleisen · Accepted Answer · 2018-07-24 06:08:29Z

1

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).

answered Jul 24, 2018 at 6:08

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rajan Over a year ago

perfect!! thanks buddy. I'm pretty new and do make silly mistakes.

Sahi · Accepted Answer · 2018-07-24 06:17:00Z

0

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

answered Jul 24, 2018 at 6:17

Sahi

1,4841 gold badge15 silver badges34 bronze badges

Collectives™ on Stack Overflow

Getting error while implementing CASE WHEN statement in SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related