SELECT DISTINCT for multiple fields

Question

In my table, I have Collection_Date and Tag_Date fields, both of type date.

I need to query this table, separating each date into its component month, day, and year, but still keeping the distinction between the Collection_Date dates and the Tag_Date dates, and I don't want duplicates.

I tried this:

SELECT DISTINCT
    MONTH(Collection_Date) AS col_month,
    DAY(Collection_Date) AS col_day,
    YEAR(Collection_Date) AS col_year,
    MONTH(Tag_Date) AS tag_month,
    DAY(Tag_Date) AS tag_day,
    YEAR(Tag_Date) AS tag_year
FROM the_table

However, this only returns rows with a unique Collection_Date and Tag_Date.

What I think I want is to separate it into two queries, one SELECT DISTINCTing Collection_Date and one for Tag_Date. However, I would really like to avoid multiple queries, if possible.

How can I accomplish this?

Answer 1

I'm pretty sure you'll have to split it up. The following will return a consolidated result set but is of course essentially 2 separate queries joined together.

SELECT MONTH(Collection_Date) AS m,
    DAY(Collection_Date) AS d,
    YEAR(Collection_Date) AS y
 UNION --For the DISTINCT
 SELECT   MONTH(Tag_Date) AS m,
    DAY(Tag_Date) AS d,
    YEAR(Tag_Date) AS y

Or now with some added differentiation. It will return distinct dates and a type field indicating whether the date refers to a tag, a collection, or both.

WITH the_table AS
(
SELECT 
    CAST('2010-06-23 00:00:00.000' AS DATETIME) AS Collection_Date, 
    CAST('2010-06-22 00:00:00.000' AS DATETIME) AS Tag_Date
UNION ALL    
SELECT 
    CAST('2010-06-25 00:00:00.000' AS DATETIME) AS Collection_Date, 
    CAST('2010-06-23 00:00:00.000' AS DATETIME) AS Tag_Date
    )
    SELECT 
    MONTH(COALESCE(T1.Collection_Date, T2.Tag_Date)) AS m,
    DAY(COALESCE(T1.Collection_Date, T2.Tag_Date)) AS d,
    YEAR(COALESCE(T1.Collection_Date, T2.Tag_Date)) AS y,
    CASE WHEN T1.Collection_Date IS NOT NULL AND T2.Collection_Date IS NOT NULL THEN 'Both'  
         WHEN T1.Collection_Date IS NOT NULL THEN 'Collection' 
         ELSE 'Tag' END 
                 AS 'Type'
FROM the_table t1
FULL OUTER JOIN the_table t2
ON T1.Collection_Date = T2.Tag_Date

Returns

m           d           y           Type
----------- ----------- ----------- ----------
6           22          2010        Tag
6           23          2010        Both
6           25          2010        Collection

Answer 2

This query will return all unique collection_date and tag_date. If a collection_date is the same as a tag_date they are both returned. The query also returns what type of date it is:

SELECT DISTINCT MONTH(Collection_Date) AS m,
    DAY(Collection_Date) AS d,
    YEAR(Collection_Date) AS y
    'collection_date' AS dateType
  FROM the_table
 UNION ALL
 SELECT DISTINCT MONTH(Tag_Date) AS m,
    DAY(Tag_Date) AS d,
    YEAR(Tag_Date) AS y,
    'tag_date' AS dateType
   FROM the_table

If you want only one tag_date or collection_date when they have a date in common, then you can use

SELECT m, d, y, MIN(dateType) FROM (
    SELECT MONTH(Collection_Date) AS m,
        DAY(Collection_Date) AS d,
        YEAR(Collection_Date) AS y
        'collection_date' AS dateType
      FROM the_table
     UNION ALL
       SELECT MONTH(Tag_Date) AS m,
        DAY(Tag_Date) AS d,
        YEAR(Tag_Date) AS y,
        'tag_date' AS dateType
       FROM the_table      
) data
GROUP BY m, d, y