Finding Last History Price

[Former Member]

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

[Former Member · Accepted solution]

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

[former_member375912]

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