Find first value with datediff larger than 1 week from table and join with initial table

Question

I have two tables:

• dbo.HistPrices (Which has all Historic Prices for every PId and some non interesting metadata... etc. )

PId (non-unique)	Price	Pricedate	...
1	5	2022-11-03
2	3	2022-11-03
2 (more than 1 date per week)	3.2	2022-11-02
1	6	2022-10-27
2	3.4	2022-10-27

• and dbo.Stuff (which is like a shopping cart of some sort where the given Price is the price in the current week for a specific item which is encapsulated in the Sid )

SId (unique)	Price	Pricedate	desc	...
1	9	2022-11-10
2	2.9	2022-11-10
3	7	2022-11-10

The SId and PId have different names also the HistPrices table carries also information for items which are not related to the stuff Table.

What I want is a Table like this:

SId	Price	Pricedate	desc	...	last_week_Price	Last_week_PriceDAte	week_before Price	week before date
1	9	2022-11-10			5	2022-11-03	6	2022-10-27
2	2.9	2022-11-10			3	2022-11-03	3.4	2022-10-27

So, I want to create two columns in the dbo.Stuff table which get last week's price and the price from the week before. I can not be sure that there is only one price from last week (see the 2022-11-02 Price for PId 2).

So, if there are more prices from last week, I just want to grab the first one which is at least a week old. Similar for the price from the week before. Only the first price which is at least 2 weeks older needs to be fetched.

Another must is, that the length of dbo.Stuff is not changed. So, if no price is found, None should be inserted.

I got a solution for an individual part with a CTE, but I don't know how to form the correct join/insert statement.

My CTE for an individual SId, which I set manually, looks like this:

DECLARE @get_Date VARCHAR(100)
DECLARE @SId int
DECLARE @week_offset int
SET @get_Date = 'teststring in date format' 
SET @SId = 12345
SET @week_offset  = -1;

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (
                  PARTITION BY hp.PId
                  ORDER BY  hp.PriceDate DESC 
--- i also thoguth abut DATEDIFF(week, hp.Pricedate,CONVERT(DATETIME,@get_Date) )
              ) rn
    FROM   dbo.HistPrices hp
    WHERE (hp.Pricedate >= DATEADD(Week, @week_offset,CONVERT(DATETIME,@get_Date))
    AND hp.Pricedate < CONVERT(DATETIME,@get_Date) ) 
     AND hp.PId = @SId
)

SELECT *
FROM cte
WHERE rn = 1
ORDER BY PId

I'm struggling to join the two tables for all ids like this. So, I think I get the correct result when I choose an ID manually, but I can somehow not join the two tables with this information.

Edit: I added some actual dates as requested in the comment

Answer 1

You could solve this a variety of ways depending on the requirements, but the first option that comes to mind would be to simply use an outer apply. Other options would include using ranking functions like row_number or analytic functions like first_value/last_value, or standard joins and CTEs if the requirements allow for it.

A simple example using a CROSS APPLY operation would be as follows:

select  s.sid, s.price, s.pricedate,
        l.price as last_week_price,
        l.pricedate as last_week_pricedate
from    dbo.Stuff s
outer apply
        (
        select  top 1
                h.price, h.pricedate
        from    dbo.HistPrices h
        where   h.pid = s.sid
                -- Starting 1 week (i.e. 7 days) before the sid.pricedate...NOT on the week start of the week before
                and h.pricedate >= dateadd(week, -1, s.pricedate)
                -- If you'd rather start on the start of the week of the week before sid.pricedate, it'd be this
                -- and h.pricedate >= dateadd(week, datediff(week, 0, s.pricedate) - 1, 0)
                and h.pricedate < dateadd(week, datediff(week, 0, s.pricedate), 0)
        order by
                h.pricedate
        ) l;