SQL to get list of dates as well as days before and after without duplicates

Question

I need to display a list of dates, which I have in a table

SELECT mydate AS MyDate, 1 AS DateType
FROM myTable
WHERE myTable.fkId = @MyFkId;

Jan 1, 2010 - 1
Jan 2, 2010 - 1
Jan 10, 2010 - 1

No problem. However, I now need to display the date before and the date after as well with a different DateType.

Dec 31, 2009 - 2
Jan 1, 2010 - 1
Jan 2, 2010 - 1
Jan 3, 2010 - 2
Jan 9, 2010 - 2
Jan 10, 2010 - 1
Jan 11, 2010 - 2

I thought I could use a union

SELECT MyDate, DateType
FROM (
    SELECT mydate - 1 AS MyDate, 2 AS DateType
    FROM myTable
    WHERE myTable.fkId = @MyFkId;

    UNION

    SELECT mydate + 1 AS MyDate, 2 AS DateType
    FROM myTable
    WHERE myTable.fkId = @MyFkId;

    UNION

    SELECT mydate AS MyDate, 1 AS DateType
    FROM myTable
    WHERE myTable.fkId = @MyFkId;
) AS myCombinedDateTable

This however includes duplicates of the original dates.

Dec 31, 2009 - 2
Jan 1, 2010 - 2
Jan 1, 2010 - 1
Jan 2, 2010 - 2
Jan 2, 2010 - 1
Jan 3, 2010 - 2
Jan 9, 2010 - 2
Jan 10, 2010 - 1
Jan 11, 2010 - 2

How can I best remove these duplicates? I am considering a temporary table, but am unsure if that is the best way to do it.

This also appears to me that it may provide performance issues as I am running the same query three separate times.

What would be the best way to handle this request?

Answer 1

This should work for you:

SELECT MyDate, min(DateType) as DateType
FROM (
    SELECT mydate - 1 AS MyDate, 2 AS DateType
    FROM myTable
    WHERE myTable.fkId = @MyFkId;

    UNION

    SELECT mydate + 1 AS MyDate, 2 AS DateType
    FROM myTable
    WHERE myTable.fkId = @MyFkId;

    UNION ALL

    SELECT mydate AS MyDate, 1 AS DateType
    FROM myTable
    WHERE myTable.fkId = @MyFkId;
) AS myCombinedDateTable
group by MyDate

Note: I changed the second UNION to a UNION ALL for better performance; the last subquery will never have duplicates with the first two subqueries, since DateType is always 2 for the first two, and 1 for the last UNIONed query.

Answer 2

Tried this and it works. Note my use of DATEADD to get it to work with my local copy of SQL which is SQL2008.

SELECT MyDate, Min(DateType)
FROM (
    SELECT DATEADD(DAY,-1,mydate) AS MyDate, 2 AS DateType
    FROM myTable
    WHERE myTable.fkId = @MyFkId

    UNION

    SELECT DATEADD(DAY,1,mydate) as MyDate, 2 AS DateType
    FROM myTable
    WHERE myTable.fkId = @MyFkId

    UNION

    SELECT mydate AS MyDate, 1 AS DateType
    FROM myTable
    WHERE myTable.fkId = @MyFkId
) AS myCombinedDateTable
group by Mydate

Answer 3

Although you have accepted the solution, let me give this solution for the reference:

SELECT MyDate, Min(DateType)
From
(
  SELECT MyDate + T1.RecordType AS MyDate, T1.DateType
  FROM
  (
    Select 1 AS RecordType, 2 AS DateType
    Union ALL
    Select 0 AS RecordType, 1 AS DateType
    Union ALL
    Select -1 AS RecordType, 2 AS DateType
  ) AS T1
  CROSS JOIN myTable
  Where myTable.fkId = @MyFkId
) AS CombinedTable
Group By MyDate

Advantage of this solution, myTable is queried only once, current case we are having a filter on fkID so right now performance will not matter, but if we have to evaluate complex query then this technique can work fine with respect to Union.