Sum not adding up corectly

Transact-SQL
1

Hello, I have two queries that each of then giving me the right result but together something is wrong .

and you see the result below.
the first query sums all invoices for specific customer and the other sums the receipts.
now I'm substracting the invoice - receipts.
the first 3 columns give me the right results but the last column is giving me a wrong number .
it should give me 81250 - 83360.28 = -2110.68, (but I'm getting -85471.36) for some reason.
now when I'm connecting the 2 queries and the result.

adding the code here for convenient.

SELECT C.CUSTNAME, system.dbo.tabula_hebconvert(C.CUSTDES),
SUM(CASE WHEN DATEDIFF(DAY, system.dbo.tabula_dateconvert(I.IVDATE), GETDATE()) < 30 THEN I.TOTPRICE - recipe.[30 Days] ELSE 0 END) AS '30 Days',
SUM(CASE WHEN DATEDIFF(DAY, system.dbo.tabula_dateconvert(I.IVDATE), GETDATE()) BETWEEN 30 and 59 THEN I.TOTPRICE - recipe.[60 Days] ELSE 0 END) AS '60 Days',
SUM(CASE WHEN DATEDIFF(DAY, system.dbo.tabula_dateconvert(I.IVDATE), GETDATE()) BETWEEN 60 and 89 THEN I.TOTPRICE - recipe.[90 Days] ELSE 0 END) '90 Days',
SUM(CASE WHEN DATEDIFF(DAY, system.dbo.tabula_dateconvert(I.IVDATE), GETDATE()) > 89 THEN I.TOTPRICE - recipe.[more than 90 Days] ELSE 0 END) AS '> 90 Days'
FROM INVOICES I

INNER JOIN CUSTOMERS C ON I.CUST = C.CUST

LEFT JOIN(

			 SELECT  C.CUSTNAME AS 'Customer number', system.dbo.tabula_hebconvert(C.CUSTDES) AS 'Customer name',
		     SUM(CASE WHEN DATEDIFF(DAY, system.dbo.tabula_dateconvert(IV.IVDATE), GETDATE()) < 30 THEN IV.TOTPRICE ELSE 0 END) AS '30 Days',
		     SUM(CASE WHEN DATEDIFF(DAY, system.dbo.tabula_dateconvert(IV.IVDATE), GETDATE())  BETWEEN 30 and 59  THEN IV.TOTPRICE ELSE 0 END) AS '60 Days',
			 SUM(CASE WHEN DATEDIFF(DAY, system.dbo.tabula_dateconvert(IV.IVDATE), GETDATE())  BETWEEN 60 and 89  THEN IV.TOTPRICE ELSE 0 END) AS '90 Days',
			 SUM(CASE WHEN DATEDIFF(DAY, system.dbo.tabula_dateconvert(IV.IVDATE), GETDATE()) > 89 THEN IV.TOTPRICE ELSE 0 END) AS 'more than 90 Days'

FROM CUSTOMERS C, INVOICES IV
WHERE IV.CUST = C.CUST
AND C.CUSTNAME = '100542'
AND IV.TYPE = 'T'
GROUP BY C.CUSTNAME, system.dbo.tabula_hebconvert(C.CUSTDES)

)recipe ON C.CUSTNAME = recipe.[Customer number]

WHERE C.CUSTNAME = '100542'
AND I.CUST = C.CUST
AND I.TYPE = 'F'
GROUP BY C.CUSTNAME, system.dbo.tabula_hebconvert(C.CUSTDES)

3

Try this ..

Lot smaller code

SELECT 
        C.custname
       ,system.dbo.Tabula_hebconvert(C.custdes)
	   , Sum(CASE WHEN I.type = 'F' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate()) < 30 THEN I.totprice ELSE 0 END) 
	      - 
            Sum(CASE WHEN I.type = 'T' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate()) < 30 THEN I.totprice ELSE 0 END) 	
                 AS  '30 Days'	   
	   , Sum(CASE WHEN I.type = 'F' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate()) BETWEEN 30 AND 59 THEN I.totprice ELSE 0 END) 
	      - 
            Sum(CASE WHEN I.type = 'T' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate()) BETWEEN 30 AND 59 THEN I.totprice ELSE 0 END) 	
                 AS  '60 Days'	   
       , Sum(CASE WHEN I.type = 'F' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate()) BETWEEN 60 AND  89  THEN I.totprice ELSE 0 END) 
	      - 
            Sum(CASE WHEN I.type = 'T' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate()) BETWEEN 60 AND  89  THEN I.totprice ELSE 0 END) 	
                 AS  '90 Days'	   			
	   , Sum(CASE WHEN I.type = 'F' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate())  > 89 THEN I.totprice ELSE 0 END) 
	      - 
            Sum(CASE WHEN I.type = 'T' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate())  > 89   THEN I.totprice ELSE 0 END) 	
               AS  '> 90 Days' 	
FROM   invoices I
         INNER JOIN 
		   customers C  ON I.cust = C.cust
WHERE 
      C.custname = '100542'		   
GROUP  BY 
       C.custname
	   ,  system.dbo.Tabula_hebconvert(C.custdes)
4

Hi, I don't know why, but it's working now.
you are a genius.
thanks my friend.

5

hi

i added this to the WHERE clause .. for performance .. :+1: :grinning:

AND (I.type = 'F' OR I.type = 'T' )

SELECT 
        C.custname
       ,system.dbo.Tabula_hebconvert(C.custdes)
	   , Sum(CASE WHEN I.type = 'F' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate()) < 30 THEN I.totprice ELSE 0 END) 
	           - 
            Sum(CASE WHEN I.type = 'T' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate()) < 30 THEN I.totprice ELSE 0 END) 	
                 AS  '30 Days'	   
	   , Sum(CASE WHEN I.type = 'F' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate()) BETWEEN 30 AND 59 THEN I.totprice ELSE 0 END) 
	      - 
            Sum(CASE WHEN I.type = 'T' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate()) BETWEEN 30 AND 59 THEN I.totprice ELSE 0 END) 	
                 AS  '60 Days'	   
       , Sum(CASE WHEN I.type = 'F' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate()) BETWEEN 60 AND  89  THEN I.totprice ELSE 0 END) 
	      - 
           Sum(CASE WHEN I.type = 'T' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate()) BETWEEN 60 AND  89  THEN I.totprice ELSE 0 END) 	
                 AS  '90 Days'	   			
	   ,Sum(CASE WHEN I.type = 'F' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate())  > 89 THEN I.totprice ELSE 0 END) 
	      - 
           Sum(CASE WHEN I.type = 'T' AND Datediff(day, system.dbo.Tabula_dateconvert(I.ivdate), Getdate())  > 89   THEN I.totprice ELSE 0 END) 	
               AS  '> 90 Days' 	
FROM   invoices I
         INNER JOIN 
		   customers C  ON I.cust = C.cust
WHERE 
      C.custname = '100542'		
           AND 
      (	I.type = 'F' OR I.type = 'T' )	   
GROUP  BY 
       C.custname
	   ,  system.dbo.Tabula_hebconvert(C.custdes)