Skip to Content
0
Mar 27, 2019 at 07:24 PM

Trying to get data from preivous year regardless if it is a leap year or not.

88 Views

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'