This doesn't add up

[123FakeSt]

I have this in a stored procedure. With the comments, I get a balance of -59.56. When I remove the comments, the toal becomes 0.00.

One of the rows (hppty=9) only appears when select the hppty field, it doesn't add up in the sum otherwise.

Any ideas?

SELECT  
	SUM(sBegin) + SUM(sMTD) BeginingAmt--, hppty
FROM  
	TOTAL  (NOLOCK) 
WHERE  
	hacct =  1984  
	AND iBook =  1  
	AND uMonth BETWEEN convert(datetime,'04/01/2006',101) AND convert(datetime,'05/01/2006',101) 
	AND hPpty IN 
		(SELECT   
			COALESCE(l5.hproperty,l4.hproperty,l3.hproperty,l2.hproperty,l1.hproperty) 
		FROM 
			(listprop l1 
			LEFT JOIN listprop l2 ON l1.hproperty = l2.hproplist 
			LEFT JOIN listprop l3 ON l2.hproperty = l3.hproplist 
			LEFT JOIN listprop l4 ON l3.hproperty = l4.hproplist 
			LEFT JOIN listprop l5 ON l4.hproperty = l5.hproplist) 
			INNER JOIN property p ON p.hmy = COALESCE(l5.hproperty,l4.hproperty,l3.hproperty,l2.hproperty,l1.hproperty) 
		WHERE 
			l1.hproplist = 458 
			AND p.ITYPE = 3)
 
--GROUP BY hppty
--ORDER BY hppty

The early bird gets the worm, but the second mouse gets the cheese.

 

[SQLBill]

WHat does this return?

(SELECT   
            COALESCE(l5.hproperty,l4.hproperty,l3.hproperty,l2.hproperty,l1.hproperty) 
        FROM 
            (listprop l1 
            LEFT JOIN listprop l2 ON l1.hproperty = l2.hproplist 
            LEFT JOIN listprop l3 ON l2.hproperty = l3.hproplist 
            LEFT JOIN listprop l4 ON l3.hproperty = l4.hproplist 
            LEFT JOIN listprop l5 ON l4.hproperty = l5.hproplist) 
            INNER JOIN property p ON p.hmy = COALESCE(l5.hproperty,l4.hproperty,l3.hproperty,l2.hproperty,l1.hproperty) 
        WHERE 
            l1.hproplist = 458 
            AND p.ITYPE = 3)

-SQLBill

Posting advice: FAQ481-4875

 

[123FakeSt]

Well that would give too many hacct's, and the balance would not be the 0 that you would expect with a balance sheet. For some reason , I changed the subquery to a table variable and it picks up hppty 9. The subquery picks up 9 when run alone but not part of the entire query. This defies logic .. I will just accept it and move on like i've seen some alien...

DECLARE @mnyBeginAmt	MONEY 
DECLARE @mnyEndAmt 	MONEY 
DECLARE @tblProps		TABLE(intProp INT) 
SET NOCOUNT ON
INSERT INTO @tblProps 
SELECT 
	COALESCE(l5.hproperty,l4.hproperty,l3.hproperty,l2.hproperty,l1.hproperty) 
		FROM 
			(listprop l1 
			LEFT JOIN listprop l2 ON l1.hproperty = l2.hproplist 
			LEFT JOIN listprop l3 ON l2.hproperty = l3.hproplist 
			LEFT JOIN listprop l4 ON l3.hproperty = l4.hproplist 
			LEFT JOIN listprop l5 ON l4.hproperty = l5.hproplist) 
			INNER JOIN property p ON p.hmy = COALESCE(l5.hproperty,l4.hproperty,l3.hproperty,l2.hproperty,l1.hproperty) 
		WHERE 
			l1.hproplist = 458 
			AND p.ITYPE = 3 
 
SET @mnyBeginAmt = (SELECT SUM(sBegin) BeginingAmt 
FROM TOTAL (NOLOCK) 
WHERE hacct =  1984 AND iBook =  1  
	AND uMonth BETWEEN convert(datetime,'04/01/2006',101) AND convert(datetime,'05/01/2006',101) 
	AND hPpty IN 
		(SELECT intProp FROM @tblProps))

The early bird gets the worm, but the second mouse gets the cheese.