-1

I'm trying to get last Vendor No. from Item Ledger Entry table with no result.

What should I put to my query to get this latest Vendor No.?

SQL query looks as follow:

SELECT 
    ile.[Posting Date], 
    CASE
        WHEN ile.[Entry Type]=1 THEN 'Sale'
        WHEN ile.[Entry Type]=5 THEN 'Consumption'
    END AS Entry_Type, 
    ,ile.[Item No_]
    ,ile.[Description] 
    ,ile.[Quantity]
    ,ile.[Unit of Measure Code]
    ,item.[Description]
    --,Last Vendor No.
FROM [MyServer]..[MyDatabase$Item Ledger Entry] as ile with (nolock)
LEFT JOIN [MyServer]..[MyDatabase$Item] as item  ON item.[No_]=ile.[Item No_]
--LEFT JOIN 
-- place for query to find last Vendor No. for particular Item No.
-- ... FROM [MyServer]..[MyDatabase$Item Ledger Entry] WHERE [Entry Type]=0 and [Source Type]=2
--
WHERE ile.[Entry Type] In (1,5) AND ile.[Item No_] ='123456789';
amaslow
  • 1
  • 1

1 Answers1

0
select top 1 [Source No_]
from [DatabaseName].dbo.[CompanyName$Item Ledger Entry]
    where [Source Type] = 2 /* Vendor */
        and [Entry Type] = 0 /* Purchase */
        and [Item No_] = '<Some Item No.>'
order by [Entry No_] desc
Alexander Drogin
  • 586
  • 3
  • 9