MAX(Case When and also Compare Dates)

Transact-SQL
1

This is probably an easy one but not getting my head around it, script all below.
(I'm using Evaluation SQL2016)

DROP TABLE #TestLogic;

CREATE TABLE #TestLogic
( [Price List] varchar NOT NULL,
[Product] varchar NOT NULL,
[Colour] varchar NOT NULL,
[Start Date] [datetime] NOT NULL,
[Expected Date] [datetime] NULL,
[The Price] [decimal](38, 20) NOT NULL
)
;

INSERT #TestLogic ([Price List], [Product], [Colour], [Start Date], [Expected Date], [The Price]) VALUES (N'Master List', N'P12345', N'', CAST(N'2017-01-25 00:00:00.000' AS DateTime), CAST(N'2017-02-05 00:00:00.000' AS DateTime), CAST(50.00000000000000000000 AS Decimal(38, 20)))
INSERT #TestLogic ([Price List], [Product], [Colour], [Start Date], [Expected Date], [The Price]) VALUES (N'Company List', N'P12345', N'', CAST(N'2016-12-25 00:00:00.000' AS DateTime), CAST(N'2017-02-05 00:00:00.000' AS DateTime), CAST(100.00000000000000000000 AS Decimal(38, 20)))
;

--SELECT * FROM #TestLogic;

-- I want to create this list -- OK to here
SELECT p.[Product],p.[Colour],p.[Expected Date]
,MAX(CASE WHEN p.[Price List] = 'Master List' THEN p.[The Price] END) AS [Master List]
,MAX(CASE WHEN p.[Price List] = 'Company List' THEN p.[The Price] END) AS [Company List]

FROM #TestLogic AS p

GROUP BY p.[Product],p.[Colour],p.[Expected Date]
;

-- BIT STUCK ON!

-- But I also need to say when Price List is 'Master List' and the [Start Date] is greater than Price List 'Company List' [Start Date]
-- then make Price List 'Company List' [The Price] NULL and then do above to put lines together

SELECT p.[Product],p.[Colour],p.[Expected Date]
,MAX(CASE WHEN p.[Price List] = 'Master List' THEN p.[The Price] END) AS [Master List]
,NULL AS [Company List]

FROM #TestLogic AS p

GROUP BY p.[Product],p.[Colour],p.[Expected Date]
;

What's the best way to do this, do I need to do a cross join or is it something simple I'm not seeing.

Thanks,
Rog

2

I have tried this:

SELECT p.[Product],p.[Colour],p.[Expected Date]
,MAX(CASE WHEN p.[Price List] = 'Master List' THEN p.[The Price]END) AS [Master List]
,MAX(CASE WHEN (CASE WHEN p.[Price List] = 'Company List' THEN p.[Start Date] END) > x.[Start Date] THEN p.[The Price] END) AS [Company List]

FROM #TestLogic AS p

LEFT OUTER JOIN
(
SELECT DISTINCT p.[Product],p.[Colour],p.[Expected Date],p.[Price List],p.[Start Date]
FROM #TestLogic AS p
WHERE p.[Price List] = 'Master List'

) AS x
ON p.[Product] = x.[Product]
AND p.[Colour] = x.[Colour]
AND p.[Expected Date] = x.[Expected Date]

GROUP BY p.[Product],p.[Colour],p.[Expected Date]
;

3

but above acript will not work because what if a table had multiple master lists in to compare i.e. these inserts.

INSERT #TestLogic ([Price List], [Product], [Colour], [Start Date], [Expected Date], [The Price]) VALUES (N'Master List', N'P12345', N'', CAST(N'2017-01-25 00:00:00.000' AS DateTime), CAST(N'2017-02-05 00:00:00.000' AS DateTime), CAST(50.00000000000000000000 AS Decimal(38, 20)))
INSERT #TestLogic ([Price List], [Product], [Colour], [Start Date], [Expected Date], [The Price]) VALUES (N'Company List', N'P12345', N'', CAST(N'2016-12-25 00:00:00.000' AS DateTime), CAST(N'2017-02-05 00:00:00.000' AS DateTime), CAST(100.00000000000000000000 AS Decimal(38, 20)))
INSERT #TestLogic ([Price List], [Product], [Colour], [Start Date], [Expected Date], [The Price]) VALUES (N'Master List2', N'P12345', N'', CAST(N'2015-01-01 00:00:00.000' AS DateTime), CAST(N'2016-02-05 00:00:00.000' AS DateTime), CAST(25.00000000000000000000 AS Decimal(38, 20)))
INSERT #TestLogic ([Price List], [Product], [Colour], [Start Date], [Expected Date], [The Price]) VALUES (N'Company List2', N'P12345', N'', CAST(N'2016-12-25 00:00:00.000' AS DateTime), CAST(N'2016-02-05 00:00:00.000' AS DateTime), CAST(75.00000000000000000000 AS Decimal(38, 20)))
INSERT #TestLogic ([Price List], [Product], [Colour], [Start Date], [Expected Date], [The Price]) VALUES (N'Master List3', N'P12345', N'', CAST(N'2017-01-01 00:00:00.000' AS DateTime), CAST(N'2015-02-05 00:00:00.000' AS DateTime), CAST(200.00000000000000000000 AS Decimal(38, 20)))
INSERT #TestLogic ([Price List], [Product], [Colour], [Start Date], [Expected Date], [The Price]) VALUES (N'Company List3', N'P12345', N'', CAST(N'2016-01-01 00:00:00.000' AS DateTime), CAST(N'2015-02-05 00:00:00.000' AS DateTime), CAST(400.00000000000000000000 AS Decimal(38, 20)))
;

4

You might try adding an ELSE like

MAX(CASE WHEN p.[Price List] = 'Master List' THEN p.[The Price] ELSE 0 END) AS [Master List]
5

Hi djj55, not sure what adding else 0 will change as without it, it would return a Null anyway... I think...