Query to use GROUP BY multiple columns

Question

I have a table full of patients/responsible parties/insurance carrier combinations (e.g. patient Jim Doe's responsible party is parent John Doe who has insurance carrier Aetna Insurance). For each of these combinations, they have a contract that has multiple payments. For this particular table, I need to write a query to find any parent/RP/carrier combo that has multiple contract dates in the same month. Is there anyway to do this?

Example table:

ContPat | ContResp | ContIns | ContDue
------------------------------------------------------
53      | 13       | 27      | 2012-01-01 00:00:00.000
53      | 13       | 27      | 2012-02-01 00:00:00.000
53      | 15       | 27      | 2012-03-01 00:00:00.000
12      | 15       | 3       | 2011-05-01 00:00:00.000
12      | 15       | 3       | 2011-05-01 00:00:00.000
12      | 15       | 3       | 2011-06-01 00:00:00.000
12      | 15       | 3       | 2011-07-01 00:00:00.000
12      | 15       | 3       | 2011-08-01 00:00:00.000
12      | 15       | 3       | 2011-09-01 00:00:00.000

In this example, I would like to generate a list of all the duplicate months for any Patient/RP/Carrier combinations. The 12/15/3 combination would be the only row returned here, but I'm working with thousands of combinations.

Not sure if this is possible using a GROUP BY or similar functions. Thanks in advance for any advice!

Answer 1

If all you care about is multiple entries in the same calendar month:

SELECT
    ContPat,
    ContResp,
    ContIns,
    MONTH(ContDue) as Mo,
    YEAR(ContDue) as Yr,
    COUNT(*) as 'Records'
FROM
    MyTable
GROUP BY
    ContPat,
    ContResp,
    ContIns,
    MONTH(ContDue),
    YEAR(ContDue)
HAVING
    COUNT(*) > 1

This will show you any Patient/Responsible Party/Insurer/Calendar month combination with more than one record for that month.