Sam M Sam M - 3 months ago 7
SQL Question

Displaying duplicate values in sql

I need to find all records where two fields in one table occur more than once in a specified date range.
I have used the below which enables me to display the duplicates but i need to specify within a period of time, whenever i try to include a date range it starts displaying all records not just the duplicates

Any help would be much appreciated!

SELECT
y.ID,y.site,y.machine
FROM calls y
INNER JOIN (SELECT
site,machine, COUNT(*) AS CountOf
FROM calls
GROUP BY site,machine
HAVING COUNT(*)>1
) dt ON y.site=dt.site and y.machine=dt.machine

Answer

You have to place the date range where/on condition in both sql statements (inner & outer) in order to get the exact records you are looking for. This can be done through where statements as follows:

SELECT  
y.ID,y.site,y.machine
  FROM calls y
    INNER JOIN (SELECT
                    site,machine, COUNT(*) AS CountOf
                    FROM calls
                    WHERE Date BETWEEN StartOfRange AND EndOfRange
                    GROUP BY site,machine
                    HAVING COUNT(*)>1
                ) dt ON y.site=dt.site and y.machine=dt.machine
WHERE
    y.Date BETWEEN StartOfRange AND EndOfRange
Comments