Query - Duplicate dates with different activities counts

donnie91910
donnie91910 used Ask the Experts™
on
I need a query that shows me duplicate dates with a count of different activities on those duplicate dates.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Abhimanyu SuriDatabase Engineer
Top Expert 2016
Commented:
select date_column,activity_column,count(1) from table_name group by date_column,activity_column order by date_column.
donnie91910

Author

Commented:
also can this be added:

Where one of the records has a Status of Delete
slightwv (䄆 Netminder)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Please provide sample data and expected results.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Abhimanyu SuriDatabase Engineer
Top Expert 2016

Commented:
So you just want to check detail of records with delete status ?

Please post an example.
donnie91910

Author

Commented:
I need to produce a list of facilityID's (group onDuplicate dates and different Activities) that have duplicate Activity Dates with different Activities.
donnie91910

Author

Commented:
I need to produce a list of facilityID's (group on) that have duplicate Activity Dates with different Activities.
Please provide a query that does this.
donnie91910

Author

Commented:
All I need is the FacilityID displayed and the ActivityDate displayed that has the duplicate.  
Thanks in advance.
Data Engineer
Commented: 
SELECT FACILITYID, ACTIVITYDATE, COUNT(DISTINCT ACTIVITY) Cnt
  FROM your_table
 GROUP BY FACILITYID, ACTIVITYDATE
HAVING COUNT(DISTINCT ACTIVITY) > 1

Open in new window

donnie91910

Author

Commented:
Thanks this worked great!
donnie91910

Author

Commented:
The code worked great.  
Moderator please move  this to Private.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial