Skip to Content
avatar image
Former Member

Finding Last History Price

SELECT distinct
AIT1.ItemCode, AITM.UpdateDate,
ait1.Price [Old Price],ait1.LogInstanc
FROM
aitm 
inner join ait1 on ait1.itemcode=aitm.itemcode and aitm.loginstanc=AIT1.loginstanc + 1
where ait1.price<>0
order by ait1.LogInstanc DESC,AITM.UpdateDate DESC

I using distinct keyword but still get result duplicated 
example as below :

itemcode updatedate old price loginstan
WB123    2017-02-15 63900   25
WB123    2017-01-17 58390   23
WB123    2017-02-15 64400   21
WB125    2017-05-17 25574   23

Then how to get only the top result and next item also want to get the first result? I tying to get old price/last history price. This query will put on the store procedure. after that this store proc will use it for SAP b1
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Mar 17, 2017 at 03:53 AM
    SELECT itemcode
           ,(
                  SELECT TOP 1 price
                  FROM itm1
                  WHERE itm1.itemcode = oitm.itemcode
                         AND pricelist = 1
                  ) AS currentprice
           ,(
                  SELECT TOP 1 price
                  FROM ait1
                  WHERE ait1.itemcode = oitm.itemcode
                         AND pricelist = 1
                  ORDER BY LogInstanc DESC
                  ) AS lastprice
    FROM oitm
    Add comment
    10|10000 characters needed characters exceeded

  • Mar 07, 2017 at 07:57 AM

    Hi

    To achieve your goal, you can use temporary table.

    Please try this query :

    --------------------------------------------------------------------------------------------------------------------------------- start

    CREATE TABLE [dbo].[#temptable2](
    [itemcode] [varchar](50) NULL,
    [updatedate] [date] NULL,
    [oldprice] [numeric](18, 0) NULL,
    [loginstan] [numeric](18, 0) NULL
    )
    declare @flag int
    declare @item varchar(50)

    -- YOUR QUERY HERE

    SELECT distinct
    AIT1.ItemCode, AITM.UpdateDate,
    ait1.Price [Old Price],ait1.LogInstanc

    into COBA
    FROM
    aitm
    inner join ait1 on ait1.itemcode=aitm.itemcode and aitm.loginstanc=AIT1.loginstanc + 1
    where ait1.price<>0
    order by ait1.LogInstanc DESC,AITM.UpdateDate DESC



    select ROW_NUMBER() over(order by itemcode asc) as ID,itemcode into #temptable1 from COBA group by itemcode

    select @flag=count(*) from #temptable1
    while (@flag>0)
    Begin
    select @item=itemcode from #temptable1 where ID=@flag
    insert into #temptable2 select top 1 * from COBA where itemcode=@item order by loginstan desc,updatedate desc
    set @flag=@flag-1
    end

    select * from #temptable2 order by itemcode asc

    drop table COBA

    drop table #temptable1
    drop table #temptable2

    --------------------------------------------------------------------------------------------------------------------------------- end

    The Result

    itemcode updatedate oldprice loginstan
    WIB123 2017-02-15 63900 25
    WIB125 2017-05-17 25574 23

    Good Luck :)

    Regards,

    Rizki

    Add comment
    10|10000 characters needed characters exceeded