Thread: MAX(Case When and also Compare Dates)

1. Registered User
Join Date
Oct 2016
Posts
11

Unanswered: MAX(Case When and also Compare Dates)

This is probably an easy one but not getting my head around it, script all below.

DROP TABLE #TestLogic;

CREATE TABLE #TestLogic
( [Price List] [varchar](20) NOT NULL,
[Product] [varchar](20) NOT NULL,
[Colour] [varchar](10) 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. Registered User
Join Date
Oct 2016
Posts
11
Just looked at maybe doing this but feels a bit messy and I think there must be an easy cleaner way...

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. Registered User
Join Date
Oct 2016
Posts
11
No my test script above 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. Registered User
Join Date
Oct 2014
Posts
291
SELECT
p.[Product]
,p.[Colour]
,p.[Expected Date]
,p.[Start Date] AS MASTER_ST
,b.[Start Date] AS COMPANY_ST
, CASE WHEN p.[Price List] LIKE ('%Master%') THEN p.[The Price] END AS [Master List]
, CASE WHEN p.[Price List] LIKE ('%Master%') AND p.[Start Date] > B.[Start Date] THEN NULL ELSE B.[The Price] END AS [Company List]

FROM

( SELECT * FROM #TestLogic WHERE [PRICE LIST] LIKE ('%Master%')) AS P

INNER JOIN

( SELECT * FROM #TestLogic WHERE [PRICE LIST] LIKE ('%Company%')) AS B
ON P.PRODUCT = B.PRODUCT
I tried to piece something together, I wasn't 100% sure on the requirements. I can probably piece something together. Maybe if you could explain it again I could get a better vision on what you are trying to accomplish.

I'm confused on the data set, you have one key but its many to many so I wasn't sure where you were going with that. Did you want all possible joins with master leading the way?

Does each record come in sequentially? Can we make the assumption that master will always have company behind it and master1 with company1? I'm just not sure man.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•