dcsimg
Results 1 to 2 of 2

Thread: Help Required to create stored prcedure

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Sep 2010
    Posts
    254

    Help Required to create stored prcedure

    Hi there,

    I have a Stored procedure for single item, please have a look

    Code:
    	@ActCode nvarchar(30),
    	@ItemIDNumber nvarchar(30),
    	@Year1 integer,
    	@Year2 Integer,
    	@Year3 Integer,
    	@Year4 Integer,
    	@Year5 Integer,
    	@Year6 Integer
    	
    AS
    BEGIN
    	SET NOCOUNT ON;
    	Select Distinct(ItemIDNumber), ActCode    Into #CurMain From QryImportOrderSummaryVsDetail Where ActCode = @ActCode And ItemIDNumber = @ItemIDNumber Group by ItemIDNumber, ActCode
    	Select Top 1 Dated, ItemIDNumber, (Price/ConversionRate) as Price, CurrencyCode   Into #Cur1 From QryImportOrderSummaryVsDetail    Where ActCode = @ActCode And YEAR(Dated) = @Year1 And ItemIDNumber = @ItemIDNumber Order by Dated Desc
    	Select Top 1 Dated, ItemIDNumber, (Price/ConversionRate) as Price, CurrencyCode   Into #Cur2 From QryImportOrderSummaryVsDetail    Where ActCode = @ActCode And YEAR(Dated) = @Year2 And ItemIDNumber = @ItemIDNumber Order by Dated Desc
    	Select Top 1 Dated, ItemIDNumber, (Price/ConversionRate) as Price, CurrencyCode   Into #Cur3 From QryImportOrderSummaryVsDetail    Where ActCode = @ActCode And YEAR(Dated) = @Year3 And ItemIDNumber = @ItemIDNumber Order by Dated Desc
    	Select Top 1 Dated, ItemIDNumber, (Price/ConversionRate) as Price, CurrencyCode   Into #Cur4 From QryImportOrderSummaryVsDetail    Where ActCode = @ActCode And YEAR(Dated) = @Year4 And ItemIDNumber = @ItemIDNumber Order by Dated Desc
    	Select Top 1 Dated, ItemIDNumber, (Price/ConversionRate) as Price, CurrencyCode   Into #Cur5 From QryImportOrderSummaryVsDetail    Where ActCode = @ActCode And YEAR(Dated) = @Year5 And ItemIDNumber = @ItemIDNumber Order by Dated Desc
    	Select Top 1 Dated, ItemIDNumber, (Price/ConversionRate) as Price, CurrencyCode   Into #Cur6 From QryExportersQuoteVsSummary       Where ActCode = @ActCode And YEAR(Dated) = @Year6 And ItemIDNumber = @ItemIDNumber Order by Dated Desc
    	
    	Select z.ItemIdNumber, z.ActCode, a.Dated as DateYear1, isnull(a.Price,0) as PriceYear1, a.CurrencyCode as CurrencyCodeYear1, b.Dated as DateYear2, isnull(b.Price,0) as PriceYear2, b.CurrencyCode as CurrencyCodeYear2, c.Dated as DateYear3, isnull(c.Price,0) as PriceYear3, c.CurrencyCode as CurrencyCodeYear3,
    	d.Dated as DateYear4,ISNULL( d.Price,0) as PriceYear4, d.CurrencyCode as CurrencyCodeYear4, e.Dated as DateYear5, Isnull(e.Price,0) as PriceYear5, e.CurrencyCode as CurrencyCodeYear5, f.Dated as DateYear6, Isnull(f.Price,0) as PriceYear6, f.CurrencyCode as CurrencyCodeYear6 Into #CurToDisplay
    	From #CurMain z
    	Left Outer Join #Cur1 a on z.ItemIdNumber = a.ItemIdNumber 
    	Left Outer Join #Cur2 b on z.ItemIdNumber = b.ItemIdNumber 
    	Left Outer Join #Cur3 c on z.ItemIdNumber = c.ItemIdNumber 
    	Left Outer Join #Cur4 d on z.ItemIdNumber = d.ItemIdNumber 
    	Left Outer Join #Cur5 e on z.ItemIdNumber = e.ItemIdNumber 
    	Left Outer Join #Cur6 f on z.ItemIdNumber = f.ItemIdNumber 
    	
    	Select * From #CurToDisplay Where PriceYear1 + PriceYear2 + PriceYear3 + PriceYear4 + PriceYear5 + PriceYear6 <> 0 Order by ItemIdNumber
    	
        
    END

    This SP is getting ItemIDNumber and last import price with date for single item for 6 years, right now its working perfectly and i am using it. The problem is if i want list of single supplier (ActCode) i have to create a loop in VB.net and it takes lots of time because one ActCode contains almost 2500 records. So the loop runs for 2500 records.
    My issue is how can i get list of all itemIDnumber belong to specific ActCode.
    Query QryImportOrderSummaryVsDetail have almost 9000 records belong to different ActCode (ActCode is supplier account code).

    Please help me out
    Thanks
    Ladak

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,537

    Re: Help Required to create stored prcedure

    I've changed your approach quite a bit but I believe this will give you what you need:-
    Code:
    	with cteNumberedImports as
    	(
    		Select	Dated, 
    				ItemIDNumber,
    				(Price/ConversionRate) as Price,
    				CurrencyCode,
    				ROW_NUMBER() Over (Partition By ItemIDNumber, Year(Dated) Order By Dated Desc) as RowNum
    				From QryImportOrderSummaryVsDetail 
    				Where ActCode = @ActCode
    	),
    	cteLastImports as
    	(
    		Select	ItemIdNumber,
    				YEAR(Dated) Year,
    				Dated,
    				Price,
    				CurrencyCode
    		From cteOrderedImports
    		Where RowNum = 1
    	)
    	Select distinct ItemIDNumber,
    					Year1.Dated as DateYear1,
    					Year1.CurrencyCode as CurrencyCodeYear1,
    					Year1.Price as PriceYear1,
    					Year2.Dated as DateYear2,
    					Year2.CurrencyCode as CurrencyCodeYear2,
    					Year2.Price as PriceYear2,
    					Year3.Dated as DateYear3,
    					Year3.CurrencyCode as CurrencyCodeYear3,
    					Year3.Price as PriceYear3,
    					Year4.Dated as DateYear4,
    					Year4.CurrencyCode as CurrencyCodeYear4,
    					Year4.Price as PriceYear4,
    					Year5.Dated as DateYear5,
    					Year5.CurrencyCode as CurrencyCodeYear5,
    					Year5.Price as PriceYear5,
    					Year6.Dated as DateYear6,
    					Year6.CurrencyCode as CurrencyCodeYear6,
    					Year6.Price as PriceYear6
    	From QryImportOrderSummaryVsDetail D
    	Left Join cteLastImports Year1
    		on D.ItemIDNumber = Year1.ItemIDNumber
    		and Year1.Year = @Year1
    	Left Join cteLastImports Year2
    		on D.ItemIDNumber = Year2.ItemIDNumber
    		and Year2.Year = @Year2
    	Left Join cteLastImports Year3
    		on D.ItemIDNumber = Year3.ItemIDNumber
    		and Year3.Year = @Year3
    	Left Join cteLastImports Year4
    		on D.ItemIDNumber = Year4.ItemIDNumber
    		and Year4.Year = @Year4
    	Left Join cteLastImports Year5
    		on D.ItemIDNumber = Year5.ItemIDNumber
    		and Year5.Year = @Year5
    	Left Join cteLastImports Year6
    		on D.ItemIDNumber = Year6.ItemIDNumber
    		and Year6.Year = @Year6
    	Where D.ActCode = @ActCode
    Note how the first cte selects all the imports for the given account code and gives each row a sequential number. The sequence is partitioned by item and year and is ordered by date desc, so the latest import per year and item will be number 1. The second cte selects all the rows with number 1 - or the last import in each year for each item. Finally the select selects all the distinct items and left joins to the last imports in much the same way as you did your original query.

    I haven't been able to test this as I don't have your tables or data but I believe it's what you need.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width