I am using SQL server 2012 with our ERP SAP Business One and I see that some years might be a leap year. For now I am trying to with static dates see if I could get the previous year sales. In the ERP I will be replacing the dates with a special variable that end users can select dates. But I get this error when running my code in SQL Manager
"Msg 402, Level 16, State 1, Line 3
The data types varchar and varchar are incompatible in the subtract operator."
SELECT YEAR(getdate())-1 As 'Year', (SELECT ISNULL(SUM(DocTotal - VatSum) , 0) FROM OINV T0 WHERE T0.DocDate >= (CONVERT(decimal(10,2),'2019-03-01') - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END ) AND T0.[DocDate] <= '2019-03-31' -CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND DocCur = '$') - (SELECT ISNULL(SUM(DocTotal - VatSum) , 0) FROM ORIN T1 WHERE T1.DocDate BETWEEN '2019-03-01' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND '2019-03-31' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND DocCur = '$') AS 'Total CND Sales', (SELECT ISNULL(SUM(DocTotalFC - VatSumFC), 0) FROM OINV T0 WHERE T0.DocDate BETWEEN '2019-03-01' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND '2019-03-31' -CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND DocCur = 'USD') - (SELECT ISNULL(SUM(DocTotalFC - VatSumFC), 0) FROM ORIN T1 WHERE T1.DocDate BETWEEN '2019-03-01' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND '2019-03-31' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND DocCur = 'USD') AS 'Total US Sales', (SELECT SUM(TaxSum) FROM OINV T0 INNER JOIN INV4 T1 ON T1.DocEntry = T0.DocEntry AND staType = 2 WHERE T0.DocDate BETWEEN '2019-03-01' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND '2019-03-31' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END) - (SELECT SUM(TaxSum) FROM ORIN T0 INNER JOIN RIN4 T1 ON T1.DocEntry = T0.DocEntry AND staType = 2 WHERE T0.DocDate BETWEEN '2019-03-01' -CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND '2019-03-31' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END) AS 'Total GST', (SELECT SUM(TaxSum) FROM OINV T0 INNER JOIN INV4 T1 ON T1.DocEntry = T0.DocEntry AND staType = 1 WHERE T0.DocDate BETWEEN '2019-03-01' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND '2019-03-31' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END) - (SELECT SUM(TaxSum) FROM ORIN T0 INNER JOIN RIN4 T1 ON T1.DocEntry = T0.DocEntry AND staType = 1 WHERE T0.DocDate BETWEEN '2019-03-01' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND '2019-03-31' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END) AS 'Total PST'