SQL to get list of dates as well as days before and after without duplicates
- by Nathan Koop
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, 2009 - 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?
