CODEWITHSUNDEEP
sql-server
PJLG
PJLG

Reputation: 105

Query to get last cost

I'm trying to do this query, in sql server, but something is wrong. Need some help...

I have a table with item movements and another one with other movements (buy) where I find the cost of each item in each date when I buy it. So, I just need first table with last cost based on the date of movement finding the cost on second table on the last date.

In other words, only must search the records from the second table with date lower than the first table date for that item and return the cost of the most recent date.

Examples:

First Table

REF     DATE
1       2015-10-15
1       2015-08-30
2       2015-09-11
3       2015-05-22
2       2015-03-08
2       2015-07-15
3       2015-11-14
1       2015-11-20

Second Table (Buy)

REF     DATE         COST
1       2015-08-20   150
1       2015-10-12   120
2       2015-04-04   270
2       2015-06-15   280
3       2015-03-01   75
3       2015-10-17   80

I need this result:

REF     DATE        Cost
1       2015-10-15  120
1       2015-08-30  150
2       2015-09-11  280
3       2015-05-22  75
2       2015-03-08  -
2       2015-07-15  280
3       2015-11-14  80
1       2015-11-20  120

Any help appreciated.

Upvotes: 1

Views: 253

Answers (2)

JamieD77
JamieD77

Reputation: 13949

;WITH cte AS (
    SELECT  ft.*,
            st.[Cost],
            ROW_NUMBER() OVER (PARTITION BY ft.[Ref],ft.[Date] ORDER BY st.[Date] DESC) RN
     FROM   FirstTable ft
            LEFT JOIN SecondTable st ON ft.[Ref] = st.[Ref]
                                    AND ft.[Date] >= st.[Date]
)
SELECT  Ref,
        [Date],
        [Cost]
FROM    cte
WHERE   RN = 1

or if you dont want to use a cte.

SELECT
    Ref,
    [Date],
    [Cost]
FROM
    (SELECT
        ft.*,
        st.[Cost],
        ROW_NUMBER() OVER (PARTITION BY ft.[Ref],ft.[Date] ORDER BY st.[Date] DESC) RN
     FROM
        FirstTable ft
        LEFT JOIN SecondTable st ON ft.[Ref] = st.[Ref]
                                    AND ft.[Date] >= st.[Date]
    ) t
WHERE
    t.RN = 1

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can do it using OUTER APPLY:

SELECT [REF], [DATE], [COST]
FROM Table1 AS t1
OUTER APPLY (
  SELECT TOP 1 COST
  FROM Table2 AS t2
  WHERE t1.REF = t2.REF AND t1.DATE >= t2.DATE
  ORDER BY t2.DATE DESC) AS t3

Demo here

Upvotes: 3

Related Questions