How to select records in SQL where one SKU has 2 or more of the same end date-CodePudding

I'm trying to pull out a list of items in a table which have more than 1 end date of 12/31/2100, as items should only have 1 (per price line). Here's the query I have so far:

SELECT PCL.*, SKUP.SKU_DESC,COUNT(CASE WHEN CONVERT(DATE,PCL.DATEEND) = CONVERT(DATE,"12/31/2100")) AS [Count]
FROM PCL LEFT JOIN SKUP ON PCL.SKU = SKUP.SKU
WHERE PCL.PRICELINE = "R12-RETAILER"
GROUP BY PCL.SKU
WHERE [Count] > 1

PCL is the price table, just bringing in the SKUP table for the item descriptions. When I run this query in Access, I get an error "Syntax error (missing operator) in query expression 'COUNT(CASE WHEN CONVERT(DATE,PCL.DATEEND) = CONVERT(DATE,"12/31/2100"))'."

Would someone be able to help me identify how this could be corrected or help point me toward an article which may better explain this?

I've been googling a lot this morning trying to find better examples for this specific application, but still learning a lot about the Group By and Count functions in SQL queries

Edit to describe my desired result, I'm just trying to pull out the records from the table which have 2 of the date 12/31/2100, because there should only be 1 "ongoing" price for each item. If I can export it, I should be able to determine where the previous price should have ended based on the start dates.

CodePudding user response：

I'm not familiar with Access specifically but shouldn't this line:

COUNT(CASE WHEN CONVERT(DATE,PCL.DATEEND) = CONVERT(DATE,"12/31/2100"))

be something like:

SUM(CASE WHEN CONVERT(DATE,PCL.DATEEND) = CONVERT(DATE,"12/31/2100" THEN 1 ELSE 0 END))

CodePudding user response：

I would try something like

SELECT PCL.*, SKUP.SKU_DESC, COUNT(1)
FROM PCL LEFT JOIN SKUP ON PCL.SKU = SKUP.SKU
WHERE PCL.PRICELINE = "R12-RETAILER" AND PCL.DATEEND=#2100-12-31#
GROUP BY PCL.SKU
HAVING COUNT(1) > 1

which I have not tested.

Note that it is possible to use date literals if you surround them with #.

Note that WHERE clause is filtering the result set before grouping, whereas HAVING is filtering the result set after grouping. Your query had two WHERE clauses which is not possible anyways.

COUNT(1) is just counting the number of records, to allow you to filter using HAVING clause.