Having - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Having

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2012-09-06 : 16:46:02

I would like to compare the Tender Amount value in one table vs the NetSalese + Tax in another table and return those Tender records if there are differences.
What I currently am using is returning many more records then I anticipate. Does the Having Sum(t.Amount) also utilize the Where conditions such as t.[type] = 1) same as the Sum(amount) in the select portion?

Thanks,
JAdauto

Select t.checknumber, t.FKStoreID, t.Dateofbusiness, Sum(t.amount)
from HstGndTender t
where t.storeID = 123
and t.dateofbusiness = '2012-09-05'
and t.[type] = 1
group by t.checknumber, t.dateofbusiness, t.fkstoreId
Having Sum(t.amount) <>
(Select Sum(s.NetSales + s.Tax)
from dpvhstchecksummary s
where s.storeID = t.storeID
and s.dateofbusiness = t.Dateofbusiness
and s.checkid = t.checknumber
group by s.checkid, s.dateofbusiness, s.fkstoreid)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-06 : 19:32:22

Is fkstoreId and storeid the same thing? I am assuming that it is, because if that were not the case, the query could fail if the subquery returns more than one value. You might also try this:

SELECT t.checknumber,
       t.FKStoreID,
       t.Dateofbusiness,
       SUM(t.amount)
FROM   HstGndTender t
	   LEFT JOIN dpvhstchecksummary s ON
		s.storeId = t.StoreId
		AND s.dateofbusiness = t.Dateofbusiness
		AND s.checkid = t.checknumber
WHERE  t.storeID = 123
       AND t.dateofbusiness = '2012-09-05'
       AND t.[type] = 1
GROUP BY
       t.checknumber,
       t.dateofbusiness,
       t.fkstoreId
HAVING
       SUM(t.amount) <> SUM(s.NetSales + s.Tax)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources