SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query not showing the expected result.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

latture
Starting Member

24 Posts

Posted - 04/26/2013 :  09:55:22  Show Profile  Reply with Quote
I have what seems to be a simple problem but I can't seem to get it right. Below is my query.

SELECT ps.SiteId, ps.MfrCode, ps.PartCode, ps.OnHandQty, ps.OnOrderQty
,ps.OnTransferQty, pm.StatusWH, ps.YTDSalesQty, pm.PopularityWH,ps.OnHandQty+ps.OnOrderQty+ps.OnFbkoQty+ps.OnTransferQty AS [TotalOH or OO]
, convert(integer,Round((ps.YTDSalesQty/10.5),0)) AS [30DaySupply]
, convert(integer,(((Round(ps.YTDSalesQty/10.5,0))-(ps.OnHandQty+ps.OnOrderQty+ps.OnFbkoQty+ps.OnTransferQty)))) AS TransferPossible
, (case when (Round((ps.YTDSalesQty/10.5),0,0)/2.01)<(ps.OnHandQty+ps.OnOrderQty+ps.OnFbkoQty+ps.OnTransferQty) then '0' else '1' end) [1FORTRANSFER]
FROM central.dbo.PartSite ps
LEFT JOIN central.dbo.vPartMasterWarehouseAll_Summary pm ON (ps.MfrCode = pm.MfrCode) AND (PS.PartCode = pm.PartCode)
WHERE (((ps.SiteId)>700) AND ((ps.MfrCode)='DEL') AND ((pm.StatusWH)<>'R') AND ((ps.YTDSalesQty)>1) AND ((pm.PopularityWH)<>'9')
AND ((ps.SubLine)=1 Or (ps.SubLine)=10 Or (ps.SubLine)=11)
AND ((ps.IsStocked)<>0))
OR (((ps.SiteId)>700) AND ((ps.MfrCode)='MTC') AND ((ps.PartCode)<>'VC10A')
AND ((pm.StatusWH)<>'R') AND ((ps.YTDSalesQty)>1) AND ((pm.PopularityWH)<>'9')
AND ((ps.SubLine)=1 Or (ps.SubLine)=15) AND ((ps.IsStocked)<>0))
ORDER BY ps.SiteId, ps.MfrCode, ps.OnHandQty DESC;

The user now only wants to see 1FORTRANSFER field = 1. So I changed the query to

SELECT ps.SiteId, ps.MfrCode, ps.PartCode, ps.OnHandQty, ps.OnOrderQty
,ps.OnTransferQty, pm.StatusWH, ps.YTDSalesQty, pm.PopularityWH,ps.OnHandQty+ps.OnOrderQty+ps.OnFbkoQty+ps.OnTransferQty AS [TotalOH or OO]
, convert(integer,Round((ps.YTDSalesQty/10.5),0)) AS [30DaySupply]
, convert(integer,(((Round(ps.YTDSalesQty/10.5,0))-(ps.OnHandQty+ps.OnOrderQty+ps.OnFbkoQty+ps.OnTransferQty)))) AS TransferPossible
, (case when (Round((ps.YTDSalesQty/10.5),0,0)/2.01)<(ps.OnHandQty+ps.OnOrderQty+ps.OnFbkoQty+ps.OnTransferQty) then '0' else '1' end) [1FORTRANSFER]
FROM central.dbo.PartSite ps
LEFT JOIN central.dbo.vPartMasterWarehouseAll_Summary pm ON (ps.MfrCode = pm.MfrCode) AND (PS.PartCode = pm.PartCode)
WHERE (case when (Round((ps.YTDSalesQty/10.5),0,0)/2.01)<(ps.OnHandQty+ps.OnOrderQty+ps.OnFbkoQty+ps.OnTransferQty) then '0' else '1' end) = '1'
AND ((((ps.SiteId)>700) AND ((ps.MfrCode)='DEL') AND ((pm.StatusWH)<>'R') AND ((ps.YTDSalesQty)>1) AND ((pm.PopularityWH)<>'9')
AND ((ps.SubLine)=1 Or (ps.SubLine)=10 Or (ps.SubLine)=11)
AND ((ps.IsStocked)<>0))
OR (((ps.SiteId)>700) AND ((ps.MfrCode)='MTC') AND ((ps.PartCode)<>'VC10A')
AND ((pm.StatusWH)<>'R') AND ((ps.YTDSalesQty)>1) AND ((pm.PopularityWH)<>'9')
AND ((ps.SubLine)=1 Or (ps.SubLine)=15) AND ((ps.IsStocked)<>0)))
ORDER BY ps.SiteId, ps.MfrCode, ps.OnHandQty DESC;

The problem is that the result isn't what I'm expecting. In the first query there are 65 records with 1FORTRANSFER = 1 in the result. However, if I run the second query there are 54 records instead of 65 which is what I was expecting. What am I doing wrong?

Thanks in advance.

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 04/26/2013 :  10:57:10  Show Profile  Reply with Quote
I don't see anything obviously wrong with your query, so I would have expected the same number of rows. But, without the ability to run it, I can't debug it. So, can you try the following? What I am doing is simply taking your working query and making it into a subquery, then applying the where clause on 1FORTRANSFER on the outer query
SELECT * FROM 
(
SELECT ps.SiteId, ps.MfrCode, ps.PartCode, ps.OnHandQty, ps.OnOrderQty
,ps.OnTransferQty, pm.StatusWH, ps.YTDSalesQty, pm.PopularityWH,ps.OnHandQty+ps.OnOrderQty+ps.OnFbkoQty+ps.OnTransferQty AS [TotalOH or OO]
, convert(integer,Round((ps.YTDSalesQty/10.5),0)) AS [30DaySupply] 
, convert(integer,(((Round(ps.YTDSalesQty/10.5,0))-(ps.OnHandQty+ps.OnOrderQty+ps.OnFbkoQty+ps.OnTransferQty)))) AS TransferPossible 
, (case when (Round((ps.YTDSalesQty/10.5),0,0)/2.01)<(ps.OnHandQty+ps.OnOrderQty+ps.OnFbkoQty+ps.OnTransferQty) then '0' else '1' end) [1FORTRANSFER]
FROM central.dbo.PartSite ps
LEFT JOIN central.dbo.vPartMasterWarehouseAll_Summary pm ON (ps.MfrCode = pm.MfrCode) AND (PS.PartCode = pm.PartCode)
WHERE (((ps.SiteId)>700) AND ((ps.MfrCode)='DEL') AND ((pm.StatusWH)<>'R') AND ((ps.YTDSalesQty)>1) AND ((pm.PopularityWH)<>'9') 
AND ((ps.SubLine)=1 Or (ps.SubLine)=10 Or (ps.SubLine)=11)
AND ((ps.IsStocked)<>0)) 
OR (((ps.SiteId)>700) AND ((ps.MfrCode)='MTC') AND ((ps.PartCode)<>'VC10A') 
AND ((pm.StatusWH)<>'R') AND ((ps.YTDSalesQty)>1) AND ((pm.PopularityWH)<>'9') 
AND ((ps.SubLine)=1 Or (ps.SubLine)=15) AND ((ps.IsStocked)<>0))
)  s WHERE [1FORTRANSFER] = 1
ORDER BY SiteId, MfrCode, OnHandQty DESC;

Edited by - James K on 04/26/2013 10:57:33
Go to Top of Page

latture
Starting Member

24 Posts

Posted - 04/26/2013 :  11:44:58  Show Profile  Reply with Quote
That actually did it. Now I'm getting the result that I was expecting. I appreciate it bunch James.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000