Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Intermediate Query, not sure how to word this...

Posted on 2008-06-15
8
255 Views
Last Modified: 2010-03-19

I'm wanting to get the last vendor used for the item, and I'm not sure how to word the max statement and whether I have to use a subquery, or whether it can be done in a join... I just want something that is fast...

Below is the query that I got now.  I need some info from 3 more tables.  

PurchaseOrder
------------------
keyPurchaseOrder
keyVendor
othCreationTime

PurchaseOrderItem
-----------------------
keyPurchaseOrder
keyItem

Vendor
------------------
keyVendor
nameVendor

So if I need the last vendor, then I need to find the purchaseorder with the latest creation time that contains the item which is i.keyItem.  

So you need v.nameVendor where i.keyItem = por.keyItem with max(p.creationTime)

Sorry thats the best pseudo I can use to explain it.  I think its understandable.  I guess I could finish off the query if I could do a little better pseudo...



Alter PROCEDURE [dbo].[p_GetTotalInventoryByVendorPar]
      @keyCompany int,
      @keyLocation int      
as

set nocount on

Begin Try

 Select
  i.keyItem,  
  i.nameItem,
  i.othCategoryType,
  i.othDescription,
  i.othItemType,
  i.othItemValue,
  i.othMinorClassification,
  i.othMinorType,
  i.othMinorValue,
  i.sysUpc,  
  sum(iv.othQuantity) as othQuantity,
  p.othPar
  v.keyVendor,
  v.nameVendor  
From ItemGrouping ig
  Inner Join Item i
    Left Join Inventory iv on iv.keyItem = i.keyItem and iv.keyLocation = @keyLocation
    Left Join Par p on i.keyItem = p.keyItem and p.keyLocation = @keyLocation
Where (i.keyLocation = @keyLocation      
              or (i.keyLocation is null and i.keyCompany = @keyCompany))
  and  i.keyChildItem < 1
  and  i.sysActive = 1  
  and  othQuantity < p.othPar
  and  
Group By
  i.keyItem,  
  i.nameItem,
  i.othCategoryType,
  i.othDescription,
  i.othItemType,
  i.othItemValue,
  i.othMinorClassification,
  i.othMinorType,
  i.othMinorValue,
  i.sysUpc,
  p.othPar
Order By i.nameItem          
             
return 0

End Try

Begin Catch      
      return error_number()
End Catch

Tony

0
Comment
Question by:picsnet
  • 3
  • 3
  • 2
8 Comments
 
LVL 10

Expert Comment

by:Banthor
ID: 21790709
A sub-query is always more efficient than a outer join (Left or right)
I am also betting that ItemGrouping is a view.
If that is so, and the view needs only the last vender, make the change and get the information closer to those indexes.

This is really badly written from the start,
Left Joining on our conditional column
Grouping all things that provided by a Key
And returns a fail even when successful.

I'll dump some tsql in a minute



0
 
LVL 2

Expert Comment

by:howyue
ID: 21790736
i somewhat disagree with banthor that a sub-query is ALWAYS more efficient than a join. it can be true  only when ur resultset or the master table being select has a very limited record and do a complex calculation, otherwise join can be faster. imagine if u hav 100 records, the subquery will probably execute 100 times where a join query to execute only 1 times. anyway, i do agree that certain situation sub-query is faster. which method to use is very much depends on ur situation and indexing.
0
 
LVL 10

Accepted Solution

by:
Banthor earned 500 total points
ID: 21790740
POP this in your try block

 
 Select 
  i.keyItem,  
  sum(iv.othQuantity) as othQuantity,
  p.othPar,
	Cast(0 as int) as keyVendor,
	Cast('' as varchar(400) as nameVendor 
Into #TempStore
From ItemGrouping ig
  Inner Join Item i
    Left Join Inventory iv on iv.keyItem = i.keyItem and iv.keyLocation = @keyLocation
    Left Join Par p on i.keyItem = p.keyItem and p.keyLocation = @keyLocation
Where (i.keyLocation = @keyLocation      
              or (i.keyLocation is null and i.keyCompany = @keyCompany))
  and  i.keyChildItem < 1
  and  i.sysActive = 1  
  and  othQuantity < p.othPar
 
/*
	 If this table has an incremental Identity key (INT) , 
	use that instead of the sale date, 
	unless your sales take such a long time 
	that the last sale, may not be the last sale added to the table (yachts and Land might be this way)
*/
UPdate #TempStore Set keyVendor = (Select top 1 KeyVendor from dbo.purchaseorders  where keyItem=#TempStore.KeyITem order by PurchaseDate desc)  
/* INDEX Seek */
UPdate #TempStore Set nameVendor = (Select top 1 NameVendor from dbo.purchaseorders  where keyVendor=#TempStore.keyVendor)
 
 
Select 
  i.keyItem,  
  i.nameItem,
  i.othCategoryType,
  i.othDescription,
  i.othItemType,
  i.othItemValue,
  i.othMinorClassification,
  i.othMinorType,
  i.othMinorValue,
  i.sysUpc,  
  ts.othQuantity,
  ts.othPar
  ts.keyVendor,
  ts.nameVendor   
From 
	#TempStore ts
	Inner Join Item i on ts.KeyItem = i.KeyITem 
Order By 
	i.nameItem 

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 10

Expert Comment

by:Banthor
ID: 21790746
Careful there howyue

I said an Outer (Left or Right)

Inner Joins are very efficient, even without indexing, becuase the Seeks/Scans are executed seperatly one after the other. The Minute you have an outer join. you have to retrieve the complete indexes and all table data before the outer can compare. If you have a large enough database to play with, watch the memory usage for a big surprise.
0
 
LVL 2

Expert Comment

by:howyue
ID: 21790780
whoops banthor, my apology here.
i really overlooked that u r saying outer join. anyway what query n methods to use stil very much depends on the situation. certain query can be slow while processing a huge table, but it can be a fast query while processing smaller table.
thanks for remind banthor.
0
 
LVL 1

Author Comment

by:picsnet
ID: 21790784
i'll look at this in the morning... I don't have any views... Item Grouping is just a table.  

ItemGrouping holds the tree-representation, and Item holds the Item.  ItemGrouping holds Groups and Items and their relationships...

Tony
0
 
LVL 1

Author Comment

by:picsnet
ID: 21790791
and i don't see what is so badly written about it... I don't say that you have to include every column in the group by.  

Sql does... I would much rather shorten it down instead of that long group by statement.  

That being said, I'm probably pretty ignorant on it cause I'm self taught on sql, and it is far weaker than my .Net code.  
0
 
LVL 1

Author Comment

by:picsnet
ID: 21793949
pretty close... i saw where you were coming from on it being bad last night... i didn't see that half of the stuff i'd copied over from the other query wasn't needed, and i'd removed some stuff, and left other stuff, so it was kinda in a mess...

ALTER PROCEDURE [dbo].[p_GetTotalInventoryByVendorPar]
      @keyCompany int,
      @keyLocation int      
as

set nocount on

Begin Try

Select
  i.keyItem,  
  sum(iv.othQuantity) as othQuantity,
  p.othPar,
  Cast(0 as int) as keyVendor,
  Cast('' as varchar(100)) as nameVendor
Into #TempStore
From Item i
    Left Join Inventory iv on iv.keyItem = i.keyItem and iv.keyLocation = @keyLocation
    Left Join Par p on i.keyItem = p.keyItem and p.keyLocation = @keyLocation
Where (i.keyLocation = @keyLocation      
       or (i.keyLocation is null and i.keyCompany = @keyCompany))
  and  i.keyChildItem < 1
  and  i.sysActive = 1  
  and  othQuantity <= p.othPar
Group By
 i.keyItem,
 p.othPar

Update #TempStore
Set keyVendor =
(Select top 1 keyVendor
From PurchaseOrder p
      Inner Join PurchaseOrderItem po on p.keyPurchaseOrder = po.keyPurchaseOrder
Where po.keyItem = #TempStore.KeyItem
and   p.keyLocation = @keyLocation
Order by p.keyPurchaseOrder Desc)

Update #TempStore
Set nameVendor =
(Select nameVendor
From Vendor
Where keyVendor = #TempStore.keyVendor)
 
Select
  i.keyItem,  
  i.nameItem,
  i.othCategoryType,
  i.othDescription,
  i.othItemType,
  i.othItemValue,
  i.othMinorClassification,
  i.othMinorType,
  i.othMinorValue,
  i.sysUpc,  
  ts.othQuantity,
  ts.othPar,
  ts.keyVendor,
  ts.nameVendor  
From  #TempStore ts
      Inner Join Item i on ts.KeyItem = i.KeyItem
Order By i.nameItem  
            
return 0

End Try

Begin Catch      
      return error_number()
End Catch





0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question