Help with SQL query - 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

General SQL Server Forums

New to SQL Server Programming

Help with SQL query

Author

Topic

Phantasm99
Starting Member

1 Post

Posted - 2012-01-06 : 00:21:00

I have learned most of what I know by searching forums, and learning about sql on the internet so please forgive me if this is a little basic. I have the following query to select the cost of an item on an estimate. It uses a stored procedure to retrieve the price as it can be stored in a few different ways.

select m.description AssemblyDesc
,m.community
,m.model
,m.assembly
,m.description as Assdesc
,d.phase
,d.item
,i.description
,i.jccostcode
,i.jccategory
,d.orderqty
,i.poindex
,v.vendor_ID
,v.Vendor_Name
,case when dbo.Purch_GetItemRateQuality(0,0,m.community,'',m.assembly,m.model,m.optionid,d.phase,d.item,d.sequence,v.vendor_id,getdate()) in (4,5,6,9,10,11) then 1 else 0 end Contract
,dbo.Purch_GetItemRate(0,0,m.community,'',m.assembly,m.model,m.optionid,d.phase,d.item,d.sequence,v.vendor_id,getdate()) Rate

from tbldbassemblymaster m
join tbldbassemblydetails d on (m.assembly=d.assembly and m.model=d.model and m.community=d.community and m.optionid=d.optionid)
join tblphaseitem i on(i.phase=d.phase and d.item=i.item)
left outer join tblvendors v on(v.vendor_id=dbo.purch_getcommunityvendor(m.community,i.poindex))

Normally it works however recently I have found that if you have a model that is not community specific (comes from the tbldbassemblymaster) but the costs are community specific they don't get pulled up. makes sence as it uses the community from the assembly to pull the cost and in this case there isn't one. I have come up with the following that gets me close but I am getting duplicate lines as there are two vendors in the database with a price and it needs to pull the vendor specified on that community not the global vendor.

select m.description AssemblyDesc
,z.community
,m.model
,m.assembly
,m.description as Assdesc
,d.phase
,d.item
,i.description
,i.jccostcode
,i.jccategory
,d.orderqty
,i.poindex
,v.vendor_ID
,v.Vendor_Name
,case when dbo.Purch_GetItemRateQuality(0,0,z.community,'',m.assembly,m.model,m.optionid,d.phase,d.item,d.sequence,v.vendor_id,getdate()) in (4,5,6,9,10,11) then 1 else 0 end Contract
,dbo.Purch_GetItemRate(0,0,z.community,'',m.assembly,m.model,m.optionid,d.phase,d.item,d.sequence,v.vendor_id,getdate()) Rate
,z.current_cost

from tbldbassemblymaster m
join tbldbassemblydetails d on (m.assembly=d.assembly and m.model=d.model and m.community=d.community and m.optionid=d.optionid)
join tblphaseitem i on(i.phase=d.phase and d.item=i.item)
left outer join tblvendors v on(v.vendor_id=dbo.purch_getcommunityvendor(Z.community,i.poindex))
left outer join tblvendorcost z on (m.assembly=z.assembly and m.model=z.model and d.phase=z.phase and d.item=z.item and v.vendor_id=z.vendor)

Essentially my problem is this: I need the vendor included in the tblvendorcost selection but need the community for the tblvendor retrieval and don't know how to get both. Is there a way around this?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-06 : 07:47:50

From your description, it is hard for someone like me who is completely unfamiliar with the problem to understand what needs to be done. If you can post more info, I am sure some of the people on the forum would be able to help. Take a look at Brett's blog for some guidance. ( http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx )

My guess is that something like this may be what you need, but it is really a wild guess on my part. Here I am replacing the left join with an OUTER APPLY

       OUTER APPLY
       (
       		SELECT TOP1 v.vendor_id FROM tblvendors v -- the TOP 1 so you get only one vendor
       		WHERE v.vendor_id = dbo.purch_getcommunityvendor(Z.community, i.poindex)
       		-- some other and condition here to pick the community-specific vendor
       ) v

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources