IF exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'dd_Top10CustomersByYear') begin
DROP proc dd_Top10CustomersByYear
end
GO
CREATE proc dd_Top10CustomersByYear
@intYear int
as
select top 10 t.custnmbr, C.CUSTNAME,
sum(case when year(t.GLPOSTDT) = @intYear - 1 then t.Amount else 0 end) as LY,
sum(case when year(t.GLPOSTDT) = @intYear then t.Amount else 0 end) as TY
-- dd_Top10CustomersByYear '2016'
from (
SELECT CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR,
case when RMDTYPAL in (1,3,4) then 1 else -1 end * (SLSAMNT + MISCAMNT - TRDISAMT) as Amount
FROM RM20101
WHERE VOIDSTTS = 0
UNION ALL
SELECT CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR,
case when RMDTYPAL in (1,3,4) then 1 else -1 end * (SLSAMNT + MISCAMNT - TRDISAMT) as Amount
FROM RM30101
WHERE VOIDSTTS = 0
) T
JOIN RM00101 C ON T.CUSTNMBR = C.CUSTNMBR
GROUP BY T.CUSTNMBR, C.CUSTNAME
order by sum(case when year(t.GLPOSTDT) = @intYear then t.Amount else 0 end) desc
GO
GRANT EXEC ON dd_Top10CustomersByYear TO PUBLIC