Solved

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

Posted on 2008-06-15
8
265 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
[X]
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
  • 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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

739 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