Help determining the correct approach

[dnfrantum]

I have two tables; One is a Standard Cost history table and one is a purchase order table. Both have a transaction date.

I am trying to figure out a way to compare the Purchase Order Price of an item on a particular day with the Standard Cost of an that item during that time.

Here an example record:

P.O.
Item Price Date Quantity
123 $1.00 01/01/2011 2

Standard Cost History
Item Cost Date
123 $0.98 09/01/2010
123 $1.02 04/01/2011

Because the Standard Cost of this item on 09/01/2010 was $0.98 and it didn't change again until 04/01/2011, the comparison should be against the this line, but how do I determine what line to compare against, since I require the nearest past date for Standard Cost history for the item in question?

Any help on this matter is greatly appreciated.

Thanks in advance,
Donald

 

[Qik3Coder]

Start by Joining the Standard Cost to itself, so that you can create windows of time.
If you are on SQL 2k5 or greater you can add a row number function and join to on item / date row = item/date row + 1.
Look at the SQL 2005 Find longest sequence from yesterday.
You will now have windows that you can join the orders into.

Start with that. Post when you get stuck.

Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[markros]

Try this query

select PO.*, SH.* from PurchaseOrder OP
OUTER APPLY (select top 1 * from StandardCostHistory S
where S.[Date]<=PO.[Date] ORDER BY S.[Date] DESC) SH

this will get the last Standard Cost record prior (or equal) to Purchase Order record.


PluralSight Learning Library

 

[PWise]

Select  itemid ,date Startdate ,cost,isnull(min(nextDate),getdate+1)enddate
from (
      Select itemid  ,Date ,
       CASE WHEN nextdate.Date > costtabletable.Date THEN nextdate.Date ELSE NULL END nextDate
        from costtabletable
       left join( 
		         Select itemid  ,Date 
			     from costtabletable
                 )nextdate
       on costtabletable.itemid=costtabletable.itemid
       and costtabletable.Date<>costtabletable.Date
       )DateAndnext
group by itemid ,date,cost

Create this view and name CostwithstartAndEnd


[cost]
Select itemid ,Price,Date, Quantity ,cost
from potable
inner join CostwithstartAndEnd
on potable.itemid=CostwithstartAndEnd.itemid
and potable.date between Startdate and enddate[/code]

 

[Qik3Coder]

markos,

That's slick.
Thanks for the new keyword, haven't seen that used before.

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[dnfrantum]

Thank you for the quick replies, unfortunately, none of these worked. The one that came closest was Markros, but it returned several lines for each value, but they were not distinct, so I couldn't eliminate the duplicates.

Here is my code

select T0.DocNum,T0.DocDate,T1.ItemCode, T2.TransactionDate,
T2.AddChangeDelete,T2.AtThisLevelMaterialCost,T2.CostType 
from OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry
             
OUTER APPLY (select top 1 * from FSE_HistoryItemCost S
where S.[TransactionDate]<=T0.[DocDate] ORDER BY S.[TransactionDate] DESC) T2

Thanks in advance,
Donald

 

[markros]

You also need to use ItemCode to connect OUTER APPLY with the main query, so

select T0.DocNum,T0.DocDate,T1.ItemCode, T2.TransactionDate,
T2.AddChangeDelete,T2.AtThisLevelMaterialCost,T2.CostType 
from OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry
             
OUTER APPLY (select top 1 * from FSE_HistoryItemCost S
where S.ItemCode = T1.ItemCode AND
S.[TransactionDate]<=T0.[DocDate] ORDER BY S.[TransactionDate] DESC) T2

PluralSight Learning Library

 

[dnfrantum]

Thanks again, but that returned null values for every column except 1 and 2.

I think I might try a stored proc for this.

Thanks in advance,
Donald

 

[gmmastros]

Try this:

;With HistoryData As
(
    Select Item,
           Date,
           Cost, 
           ROW_NUMBER() Over (Partition By Item Order BY Date) As RowId 
    From   FSE_HistoryItemCost 
), HistoryRange As 
(
    Select A.Item, 
           A.Cost, 
           A.Date As StartDate, 
           Coalesce(B.Date, GetDate()) As EndDate
    From   HistoryData A 
           Left Join HistoryData B 
              On  A.Item = B.Item 
              And A.RowId = B.RowId-1
)
Select * 
From   OPDN As PO
       Inner Join HistoryRange
         On PO.Item = HistoryRange.Item
         And PO.Date Between HistoryRange.StartDate And HistoryRange.EndDate

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom