Query not showing the expected result.

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 2008 Forums

Transact-SQL (2008)

Query not showing the expected result.

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 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;

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources