Find common data between groups
I have this data
DeveloperId Date remark
--------------------------------------
1 02/01/2013 Google
1 02/02/2013 MSN
1 02/03/2013 Google
1 02/02/2013 MSN
1 02/01/2013 Yahoo
2 02/01/2013 Google
2 02/02/2013 Yahoo
2 02/03/2013 Google
2 02/01/2013 Google
How to find common dates for Google and Msn and yahoo dynamically?
I would like to put such a request in the report.
+3
source to share
5 answers
Try the following:
with temp as
(
SELECT Date, Remark
From MyTable
GROUP BY Date, Remark
)
SELECT Date
FROM temp
GROUP BY Date
Having COUNT(*) > 1
Working fiddle here: http://sqlfiddle.com/#!3/d41d8/9105
+1
source to share
-- Query for dates that have all types of remarks
SELECT
Date
FROM
data
GROUP BY
Date
HAVING COUNT(DISTINCT remark) = (
SELECT COUNT(DISTINCT remark)
FROM data
);
Or, if this was not a requirement ...
-- Query for dates that have more than one remark.
SELECT
Date
FROM
data
GROUP BY
Date
HAVING COUNT(DISTINCT remark) > 1;
Sql Fiddle
+2
source to share