Group by issue

Transact-SQL
1

Hi - I'm having issues selecting duplicate records from a specific date using a group by / having.

select * from [dbo].[entrytable]
WHERE (CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, entrytime))) = CONVERT(DateTime, DATEDIFF(DAY, 2, GETDATE())))
and stat != 'allowed' and staffnumber
IN (SELECT staffnumber
FROM entrytable
GROUP BY staffnumber
HAVING COUNT(staffnumber ) > 1)

As you can see the select should be selecting records from 2 days before today which works fine but the group by seams to be grouping all records irrespective of the date.

Can anyone help ?

2

Try casting your date columns in the criteria as date. (Only SQL Server 2008 +)
SELECT
*
FROM
[dbo].[entrytable]
WHERE
Cast(entrytime AS date) = Convert(date, DateDiff(DAY, 2, GetDate())) )
AND stat != 'allowed'
AND staffnumber IN ( SELECT
staffnumber
FROM
entrytable
GROUP BY
staffnumber
HAVING
Count(staffnumber) > 1 );

3 
select * 
from dbo.entrytable
WHERE entrytime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 2, 0) 
and entrytime < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)
and stat != 'allowed' and staffnumber 
IN (SELECT staffnumber
FROM entrytable
GROUP BY staffnumber, DATEADD(DAY, DATEDIFF(DAY, 0, entrytime), 0)
HAVING COUNT(*) > 1)
4

Hi many thanks but this did not work

5

Hi Scott,

This took an over 2 mins to run and did not show the number is desired record as set by the count so for example if I set the count(*) to > 3 then it still included records outside of the date

6

How about something like:

select * 
from dbo.entrytable
WHERE   entrytime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 2, 0) 
    and entrytime < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)
    and stat <> 'allowed' 
    and staffnumber IN (SELECT staffnumber
                        FROM entrytable
                        WHERE   entrytime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 2, 0) 
                            and entrytime < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)
                        GROUP BY staffnumber
                        HAVING COUNT(*) > 1);

Also is there an index on entrytime?

1 Like
7

Dunno if it would be faster, but couldn't the

and staffnumber IN (SELECT staffnumber
                        FROM entrytable
                        WHERE   ...
                        HAVING COUNT(*) > 1);

be done with an OVER in the main SELECT, and then an outer SELECT to only select the ones that have COUNT(*) > 1?

However, the main select also has stat <> 'allowed' which is not present in the IN clause

8

Hi Thanks for the assistance I did not need to use the over as the select took milliseconds to run