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