Can addition or sustraction be performemed on two case statments

[evr72]

Hello,

I have a report that get the data from sql statements,
it is looks something like this

SELECT
I9ADR7,
CUSNM,
SUM((case when yr = YEAR( CURRENT DATE)-4 then sales end)) AS "2013",
SUM((case when yr = YEAR( CURRENT DATE)-3 then sales end)) AS "2014",
SUM((case when yr = YEAR( CURRENT DATE)-2 then sales end)) AS "2015",
SUM((case when yr = YEAR( CURRENT DATE)-1 then sales end)) AS "2016",
SUM((case when yr = YEAR( CURRENT DATE) then sales end)) AS "2017",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'01' THEN SALES END)) AS "January",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'02' THEN SALES END)) AS "February",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'03' THEN SALES END)) AS "March",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'04' THEN SALES END)) AS "April",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'05' THEN SALES END)) AS "May",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'06' THEN SALES END)) AS "June",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'07' THEN SALES END)) AS "July",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'08' THEN SALES END)) AS "August",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'09'THEN SALES END)) AS "September",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'10' THEN SALES END)) AS "October",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'11' THEN SALES END)) AS "November",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'12' THEN SALES END)) AS "December"
FROM DAVIESF.BOOKPRLINK
WHERE (CUSNM ='CENTURY TOOLS & MACHINERY LTD      ')
GROUP BY I9ADR7, CUSNM

this works well, but I need to subtract 2016 form 2017 I was wondering if I can do something like this

[highlight #FCE94F]SUM((case when yr = YEAR( CURRENT DATE) then sales end)-(case when yr = YEAR( CURRENT DATE)-1 then sales end)) AS "2017",[/highlight]

I tried the above code but I get an empty field

any help is much welcomed!!!

thanks!!

 

[IanWaterman]

Why not just perform a query on your query

Select .....
From
(SELECT
I9ADR7,
CUSNM,
SUM((case when yr = YEAR( CURRENT DATE)-4 then sales end)) AS "2013",
SUM((case when yr = YEAR( CURRENT DATE)-3 then sales end)) AS "2014",
SUM((case when yr = YEAR( CURRENT DATE)-2 then sales end)) AS "2015",
SUM((case when yr = YEAR( CURRENT DATE)-1 then sales end)) AS "2016",
SUM((case when yr = YEAR( CURRENT DATE) then sales end)) AS "2017",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'01' THEN SALES END)) AS "January",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'02' THEN SALES END)) AS "February",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'03' THEN SALES END)) AS "March",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'04' THEN SALES END)) AS "April",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'05' THEN SALES END)) AS "May",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'06' THEN SALES END)) AS "June",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'07' THEN SALES END)) AS "July",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'08' THEN SALES END)) AS "August",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'09'THEN SALES END)) AS "September",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'10' THEN SALES END)) AS "October",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'11' THEN SALES END)) AS "November",
SUM((CASE WHEN YRMO = YEAR( CURRENT DATE)||'12' THEN SALES END)) AS "December"
FROM DAVIESF.BOOKPRLINK
WHERE (CUSNM ='CENTURY TOOLS & MACHINERY LTD ')
GROUP BY I9ADR7, CUSNM) A