Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-15
8
Medium Priority
?
271 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 2000 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 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