Sql To Get List Of Dates As Well As Days Before And After Without Duplicates

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

Solution 1:

This should work for you:

SELECTMyDate, min(DateType) asDateTypeFROM (
    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;
) ASmyCombinedDateTablegroupbyMyDate

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.

Solution 2:

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
  (
    Select1AS RecordType, 2AS DateType
    Union ALL
    Select0AS RecordType, 1AS DateType
    Union ALL
    Select -1AS RecordType, 2AS DateType
  ) AS T1
  CROSS JOIN myTable
  Where myTable.fkId = @MyFkId
) AS CombinedTable
GroupBy 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.

Solution 3:

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

SELECTMyDate, 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
) ASmyCombinedDateTablegroupbyMydate