AndrewC10 AndrewC10 - 2 months ago 11
SQL Question

SQL Count Aggregate with Multiple Having Conditions

I am attempting to write a SQL query that only returns employees that have multiple transactions on the same date (count(TransactionDate) > 1), but the transactions occurred within different store IDs. I am attempting to use a combination of the count aggregate and having, but can't seem to return the correct values. Would a temp table be a better way to do it, or maybe a sub query? My below query is not returning accurate records. Any help is appreciated. Thanks!

EmployeeID | StoreID | TransactionDate
--------------------------------------
1 | 1 | 2016-09-09 --should be returned
--------------------------------------
1 | 2 | 2016-09-09 --should be returned
--------------------------------------
1 | 3 | 2016-09-09 --should be returned
--------------------------------------
1 | 1 | 2016-09-18 --should not be returned
--------------------------------------
2 | 1 | 2016-09-09 --should not be returned
--------------------------------------
2 | 1 | 2016-09-09 --should not be returned
--------------------------------------
3 | 1 | 2016-09-09 --should not be returned
--------------------------------------
4 | 5 | 2016-09-09 --should be returned
--------------------------------------
4 | 6 | 2016-09-09 --should be returned

select top 1000 EmployeeID, StoreID, TransactionDate, count(StoreID)[StoreCount], count(TransactionDate)[Transaction Count]
from myTable
group by EmployeeID, StoreID, TransactionDate
having count(StoreID) > 1 and count(TransactionDate) > 1
order by TransactionDate desc

Answer
SELECT t.*
FROM
    (
       SELECT
          EmployeeId, TransactionDate
       FROM
          Table
       GROUP BY
          EmployeeId, TransactionDate
       HAVING
          COUNT(DISTINCT StoreId) > 1
    ) e
    INNER JOIN Table t
    ON e.EmployeeId = t.EmployeeId
    AND e.TransactionDate = t.TransactionDate

Actually window functions wouldn't be a lot of help here because the key is to COUNT(DISTINCT StoreId) Grouped by Employee & TransactionDate and COUNT(DISTINCT ) OVER () is not allowed. So the derived table is the way to go and this syntax will work with pretty much all of the typical RDBMS.

Comments