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?

They are not technically duplicates, as the date types are different. Which date type do you want to show when there are duplicates? — D'Arcy Rittich, Mar 11 '10 at 17:55
@OrbMan, you are correct, I would like to display Type 1 if the date is repeated and ignore Type 2. — Nathan Koop, Mar 11 '10 at 17:57
where does `Jan 1, 2009 - 2` come from ... (notice the jan and 2009). — Gabriele Petrioli, Mar 11 '10 at 17:58

score 4 · Answer 1 · answered Mar 11 '10 at 17:57

4

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.

answered Mar 11 '10 at 17:57

D'Arcy Rittich

167,292
40
290
283

props for getting back quicker than me! – CResults Mar 11 '10 at 18:02
Thanks!! Now I need to look into UNION vs UNION ALL. You learn something new every day. – Nathan Koop Mar 11 '10 at 18:37
For other's not aware of the difference see this question http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all – Nathan Koop Mar 11 '10 at 18:38

score 1 · Accepted Answer · edited Mar 11 '10 at 19:32

1

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.

edited Mar 11 '10 at 19:32

Nathan Koop

24,803
25
90
125

answered Mar 11 '10 at 19:00

Nitin Midha

2,258
20
22

This looks really good, I'm comparing the efficincy right now. I had to make a couple changes to your code though. I'll update the sample – Nathan Koop Mar 11 '10 at 19:31
Thanks, I've switched the answer over here because the performance is better. The IO reads are about 1/3rd of the other solution and the CPU is consistently less. The duration is rather similar, but your query is always less. Thanks to everyone for their assistance – Nathan Koop Mar 11 '10 at 20:31

score 0 · Answer 3 · answered Mar 11 '10 at 18:02

0

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

answered Mar 11 '10 at 18:02

CResults

5,100
1
22
28

`select getdate() - 1` works fine on my SQL Express 2008, what is the issue you are running into? – D'Arcy Rittich Mar 11 '10 at 18:04
My bad, I defined MyDate as a Date rather than a DateTime. Declare as a Date and SQL2008 chucks an error. I have a habit these days of only using Date type as I hate dealing with time! :-) – CResults Mar 11 '10 at 18:09

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

3 Answers3

Linked