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.
Author |
Topic |
latture
Starting Member
24 Posts |
Posted - 2013-04-26 : 09:55:22
|
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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-26 : 10:57:10
|
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 querySELECT * 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 psLEFT 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] = 1ORDER BY SiteId, MfrCode, OnHandQty DESC; |
|
|
latture
Starting Member
24 Posts |
Posted - 2013-04-26 : 11:44:58
|
That actually did it. Now I'm getting the result that I was expecting. I appreciate it bunch James. |
|
|
|
|
|
|
|