Stinkys Stinkys - 6 days ago 6
MySQL Question

MySQL duplicate entries search with selective date criteria

Having trouble wrapping my head around having an efficient "duplicate entries" select in a single query.

In the below example, duplicate

StockNo
can exist spanning multiple
Date
. I want to search
StockNo
for duplicate entries, and if at least 1
StockNo
record is found within the
Date
current YEAR-MONTH, then I also need to select its partner that could exist in any other YEAR-MONTH. Is this possible?

Example Query:

SELECT * FROM `sales`
WHERE `StockNo` IN
(SELECT `StockNo` FROM `sales` GROUP BY `StockNo` HAVING COUNT(*) > 1)
AND `Date` LIKE '2016-11-%'
ORDER BY `StockNo`, `TransactionID`;


Example Data:

ID | StockNo | Date
1 | 1 | 2016-11-01
2 | 1 | 2016-11-10
3 | 2 | 2016-11-05
4 | 2 | 2016-10-29
5 | 3 | 2016-10-25
6 | 3 | 2016-10-15


With my example query and data, I have 3 pairs of duplicate entries. It's pretty obvious that I will only return 3 records (ID's 1, 2 & 3) due to
AND Date LIKE '2016-11-%'
, however I need to return ID's 1, 2, 3, 4. I want to ignore ID's 5 & 6 because neither of them fall within the current month.

Hope that makes sense. Thanks for any help you can provide.

Answer
SELECT StockNo
FROM sales
GROUP BY StockNo
HAVING SUM(CASE WHEN DATE_FORMAT(Date, '%Y-%m') = '2016-11' THEN 1 ELSE 0 END) > 0

If you also want to retrieve the full records for those matching stock numbers in the above query, you can just add a join:

SELECT s1.*
FROM sales s1
INNER JOIN
(
    SELECT StockNo
    FROM sales
    GROUP BY StockNo
    HAVING SUM(CASE WHEN DATE_FORMAT(Date, '%Y-%m') = '2016-11' THEN 1 ELSE 0 END) > 0
) s2
    ON s1.StockNo = s2.StockNo

Demo here:

SQLFiddle

Comments