AndrewC10 AndrewC10 - 1 year ago 78
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 Source
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download