How can I use Group By (instead of self-joins) to get correct data (specific example included)

Question

Looking at the related questions, I don't think this specific question has been asked, so here goes.

I had a situation where I joined on a table three times to get different data based on dates.

This took too long, so in an effort to optimize, I rewrote it using a group by as defined here: http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/06/12/60230.aspx

I'm having a hard time with the logic, and I'm beginning to think it's not possible to get exactly what I want through this. I'll show you my current code then describe what I need from it (tables/variables changed to protect the innocent).

SELECT
 upc,
 MAX(CASE WHEN ip_start_date <= GETDATE() THEN ip_unit_price END) AS retail_amount,
 MAX(CASE WHEN ip_start_date <= GETDATE() THEN ip_price_multiple END) AS retail_multiplier_num,    
 MAX(CASE WHEN ip_start_date BETWEEN GETDATE() AND DATEADD(ww,1,GETDATE()) THEN ip_unit_price END) AS retail_amt_nxt_wk,
 MAX(CASE WHEN ip_start_date BETWEEN GETDATE() AND DATEADD(ww,1,GETDATE()) THEN ip_price_multiple END) AS retail_multipler_num_nxt_wk,
 MAX(CASE WHEN ip_start_date BETWEEN DATEADD(ww,1,GETDATE()) AND DATEADD(ww,2,GETDATE()) THEN ip_unit_price END) AS retail_amt_wk_after_nxt,
 MAX(CASE WHEN ip_start_date BETWEEN DATEADD(ww,1,GETDATE()) AND DATEADD(ww,2,GETDATE()) THEN ip_price_multiple END) AS retail_multiplier_num_wk_after_nxt
FROM 
 items AS im WITH (NOLOCK)
  retails AS ip WITH (NOLOCK)
   ON im.ID = ip.ID
GROUP BY
 upc

So looking at first line, this gets me the max retail with a date less than today. I actually need the most recent one, not the largest. This used to be handled with a sub-query which got me the MAX(start_date) less than today. I can't do a MAX within a MAX, for what are most likely good reasons. I was considering LAST, but I'm not quite sure the last record will always be the most recent in our system (new system).

Does anyone see a solution to this? The BETWEENS work fine, MAX retail within that week is good enough as those are for estimation. The other one must be accurate, though.

(Feel free to edit title..I couldn't come up with a succinct way to ask this)

Answer 1

In SQL Server 2005+:

SELECT  upc, retail_today.*, retail_next_week.*, retail_two_weeks.*
FROM    items im
OUTER APPLY
        (
        SELECT  TOP 1
                ip_unit_price, ip_price_multiple
        FROM    retail ip
        WHERE   ip.ip_start_date <= GETDATE()
                AND ip.id = im.id
        ORDER BY
                ip_start_date DESC
        ) retail_today
OUTER APPLY
        (
        SELECT  TOP 1
                ip_unit_price, ip_price_multiple
        FROM    retail ip
        WHERE   ip.ip_start_date BETWEEN GETDATE() AND DATEADD(ww, 1, GETDATE())
                AND ip.id = im.id
        ORDER BY
                ip_start_date DESC
        ) retail_next_week
OUTER APPLY
        (
        SELECT  TOP 1
                ip_unit_price, ip_price_multiple
        FROM    retail ip
        WHERE   ip.ip_start_date BETWEEN DATEADD(ww, 1, GETDATE()) AND DATEADD(ww, 2, GETDATE())
                AND ip.id = im.id
        ORDER BY
                ip_start_date DESC
        ) retail_two_weeks

In SQL Server 2000:

SELECT  upc,
        (
        SELECT  TOP 1
                ip_price
        FROM    retail ip
        WHERE   ip.ip_start_date <= GETDATE()
                AND ip.id = im.id
        ORDER BY
                ip_start_date DESC
        ) AS ip_price_today,
        (
        SELECT  TOP 1
                ip_price_multiple
        FROM    retail ip
        WHERE   ip.ip_start_date <= GETDATE()
                AND ip.id = im.id
        ORDER BY
                ip_start_date DESC
        ) AS ip_price_multiple_today,
        …
FROM    items im