Last Cost Compared to Current

Kinetic ERP
, ,
1

I am writing a BAQ/Report and the end user is wanting the RcvDtl info for each part. Then he wants the last unit cost from before the one listed. Meaning, if we have part ABC1 and we purchased it in March 2024 for $1. But the last time we purchased that material was in 2019 and we paid $0.65. He wants to see it as:

Company, PartNumber, Current Cost, Last purchased cost.

I am having trouble wihit the last purchased cost. How do I do that?

2

I would look at the part cost table for that information. It should exist there you can verify that by looking in part tracker and it should show it there as well.

1 Like
3

I am thinking that last could already be updated, so you need to do something with RcvDtl a CTE and a rank over partition to find the rank -1 of the current receipt.

2 Likes
4

Here is a small snippet that might get you started.

SELECT 
    [RcvDtl].[Company] AS [RcvDtlMain_Company], 
    [RcvDtl].[PartNum] AS [RcvDtlMain_PartNum],
    [RcvDtl].[ReceiptDate] AS [RcvDtlMain_ReceiptDate], 
    [RcvDtl].[OurQty] AS [RcvDtlMain_OurQty],
    [RcvDtl].[OurUnitCost] AS [RcvDtlMain_OurUnitCost],  
    (ROW_NUMBER() OVER (PARTITION BY RcvDtl.PartNum ORDER BY RcvDtl.ReceiptDate DESC, RcvDtl.SysRevID)) AS [Calculated_RowNum]
FROM 
    Erp.RcvDtl AS RcvDtl

Then you select only where the RowNum is = to 1.

2 Likes