October 1, 2014 at 9:26 am
Good Morning,
I am trying to figure out a method to check if the values in 3 columns are the same. I just can't figure this logic.
Anyhow, here is a piece of code with what I'm attempting. Thanks
SELECT DISTINCT po.whse
,COALESCE(po.buyer, 'Blank') AS buyer
,poitem.po_num
,vendaddr.name
,po.stat
,Sum(poitem.qty_ordered) AS Qty_Ord
,Sum(poitem.qty_received) AS Qty_Recv
,Sum(poitem.qty_voucher) AS Qty_Vch
,Sum(poitem.qty_received) - Sum(poitem.qty_voucher) AS Variance
--(IF THE Sum(poitem.qty_ordered) = Sum(poitem.qty_received) AND Sum(poitem.qty_voucher),0,1)
,po.terms_code
,po.vend_num
,po.po_cost
,po.CreateDate
FROM poitem WITH (Nolock)
INNER JOIN po WITH (Nolock)
ON poitem.po_num = po.po_num
INNER JOIN vendaddr WITH (Nolock)
ON po.vend_num = vendaddr.vend_num
WHERE ( 1 = 1 )
AND ( po.stat = 'o' )
AND po.CreateDate >= '10/1/2014'
GROUP BY poitem.po_num
,po.stat
,vendaddr.name
,po.buyer
,po.terms_code
,po.vend_num
,po.whse
,po.po_cost
,po.CreateDate
Order by po.CreateDate DESC
October 1, 2014 at 9:35 am
Are you looking for something like this?
CASE WHEN Sum(poitem.qty_ordered) = Sum(poitem.qty_received) AND Sum(poitem.qty_voucher) = Sum(poitem.qty_received)
THEN 0 ELSE 1 END
Are you aware on what the NOLOCK hint does? Are your users aware that they can get inconsistent results? Are they fine with that? Do you know that it's not a go-fast option?
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
October 1, 2014 at 12:08 pm
Thanks so much!! This was perfect and, perfectly logical.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply