0

I have a table that has a column date. Dates range from Jan 01 2002 to today. However there are several dates missing in the year.

How can I find out which distinct dates are not in the table?

For example, from this table (The first and third columns are irrelevant):

+------------+----------+-----------+
| subject    | date     | value     |
+------------+----------+-----------+
| Biology    |2016-01-02|  90000.00 |
| Comp. Sci. |2016-01-03| 100000.00 |
| Elec. Eng. |2016-01-04|  85000.00 |
| Finance    |2016-01-06| 120000.00 |
| History    |2016-01-07|  50000.00 |
| Music      |2016-01-10|  80000.00 |
| Physics    |2016-01-11|  70000.00 |
+------------+----------+-----------+

I would like the query to return the following:

2016-01-01
2016-01-05
2016-01-08
2016-01-09
2016-01-12
....
2016-12-21

Furthermore, can I find which of these missing dates are weekdays?

So, I can get

2016-01-01
2016-01-05
2016-01-08
2016-01-12
....

2016-01-09 is missing since it is a Saturday.

Thanks.

fractal5
  • 2,034
  • 4
  • 29
  • 50
  • if there isn't a default value, try to check with if they're empty. If that's what the question is about. – Funk Forty Niner Jan 19 '17 at 20:32
  • I guess I wasn't clear. I added an example. – fractal5 Jan 19 '17 at 21:02
  • 2
    You want to generate a temporary table, filled with all the dates in the range you're considering, and then perform a left join or similar from the temporary table to your table, and select only those rows with null in your table's date field. This question is similar except in the last bit: http://stackoverflow.com/questions/14105018/generating-a-series-of-dates – gandaliter Jan 19 '17 at 21:16

0 Answers0