I hope I can convey this clearly, been wracking my brain and can't figure it out. We have monthly historical data in which we calculate median spend for each type and level combination for our two segments.

I want to create two fields that display the most recent med spend value for that level by each segment and type combination.

I started with this, but I know it needs more.

        select Segment, Type, (select max([med spend]) from source where level = 'Gold') as 'Gold Spend',
    (select max([med spend]) from source where level = 'Silver') as 'Silver Spend'
        from source a
where a.date = (select max(b.date) from source b
where b.segment = a.segment and b.type = a.type)

Source Table

Desired Output

CodePudding user response：

you can use an aggregation with GROUP BY

CREATE TABLE table1
    ([Date] varchar(13), [Segment] varchar(1), [Type] int, [Level] varchar(6), [Med Spend] int)
;
    
INSERT INTO table1
    ([Date], [Segment], [Type], [Level], [Med Spend])
VALUES
    ('December 2022', 'A', 0, 'Gold', 1303),
    ('December 2022', 'A', 1, 'Gold', 1500),
    ('December 2022', 'A', 0, 'Silver', 1000),
    ('December 2022', 'A', 1, 'Silver', 1111),
    ('November 2022', 'A', 0, 'Gold', 500),
    ('November 2022', 'A', 1, 'Gold', 600),
    ('November 2022', 'A', 0, 'Silver', 450),
    ('November 2022', 'A', 1, 'Silver', 110),
    ('December 2022', 'B', 0, 'Gold', 210),
    ('December 2022', 'B', 1, 'Gold', 145),
    ('December 2022', 'B', 0, 'Silver', 540),
    ('December 2022', 'B', 1, 'Silver', 360),
    ('November 2022', 'B', 0, 'Gold', 777),
    ('November 2022', 'B', 1, 'Gold', 888),
    ('November 2022', 'B', 0, 'Silver', 125),
    ('November 2022', 'B', 1, 'Silver', 123)
;

16 rows affected

SELECT
[Segment], [Type],
  SUM(CASE WHEN [Level] = 'Silver' THEN  [Med Spend] ELSE 0 END) Siver_spent,
  SUM(CASE WHEN [Level] = 'Gold' THEN  [Med Spend] ELSe 0 END) Gold_spent
FROM table1
GROUP BY [Segment], [Type]

fiddle

CodePudding user response：

A windowed ROW_NUMBER() function can be used to prioritize your spend values within each Segment/Type/Level context. From there you can group the results and use "conditional aggregation" to select the top value for each.

One change needed before this can work is to have a sortable date column in your source. A "Monthname-YYYY" column will not work.

SELECT Segment, Type,
    MAX(CASE WHEN Level = 'Silver' AND rn = 1 THEN [Med Spend] END) AS [Silver Spend],
    MAX(CASE WHEN Level = 'Gold' AND rn = 1 THEN [Med Spend] END) AS [Gold Spend]
FROM (
    SELECT T.*,
        ROW_NUMBER() OVER(PARTITION BY T.Segment, T.Type, T.Level ORDER BY T.Date DESC) AS rn
    FROM table1 T
) TN
GROUP BY Segment, Type
ORDER BY Segment, Type

Or, if you prefer, you can use a Common Table Expression (CTE) instead of a subquery:

; WITH CTE1 AS (
    SELECT T.*,
        ROW_NUMBER() OVER(PARTITION BY T.Segment, T.Type, T.Level ORDER BY T.Date DESC) AS rn
    FROM table1 T
)
SELECT Segment, Type,
    MAX(CASE WHEN Level = 'Silver' AND rn = 1 THEN [Med Spend] END) AS [Silver Spend],
    MAX(CASE WHEN Level = 'Gold' AND rn = 1 THEN [Med Spend] END) AS [Gold Spend]
FROM CTE1
GROUP BY Segment, Type
ORDER BY Segment, Type

Results:

The "1500" value is different from the OP expected results, but I believe these results are correct given the requested criteria.

See this db<>fiddle.