Skip to Content
0
Oct 06, 2015 at 10:19 AM

2014 V 2015 Invoiced by Item

12 Views

Hi all,

I am trying to show 2014 Invoiced figure v 2015 invoiced figure per item.

I have tried the below, which has worked to some degree but is missing some figures and I can't quite figure out why.

SELECT * FROM

(
SELECT ItemCode,
ISNULL((SELECT SUM(LineTotal) FROM INV1 WHERE ItemCode = T0.ItemCode AND CANCELED = 'N' AND DocDate BETWEEN '2014-01-01' AND '2014-12-31'), 0)
- ISNULL((SELECT SUM(LineTotal) FROM RIN1 WHERE ItemCode = T0.ItemCode AND CANCELED = 'N' AND DocDate BETWEEN '2014-01-01' AND '2014-12-31'), 0)
Total2014,
ISNULL((SELECT SUM(LineTotal) FROM INV1 WHERE ItemCode = T0.ItemCode AND CANCELED = 'N' AND DocDate BETWEEN '2015-01-01' AND '2015-12-31'), 0)
- ISNULL((SELECT SUM(LineTotal) FROM RIN1 WHERE ItemCode = T0.ItemCode AND CANCELED = 'N' AND DocDate BETWEEN '2015-01-01' AND '2015-12-31'), 0)Total2015
FROM OITM T0
) T
WHERE T.Total2014 <> 0 OR T.Total2015 <> 0


Any ideas of how I can improve on this?


Thanks,


Kirstie