Dateadd function and dates from table

[former_SQLA_member1694869]

I use datadd to back the specified date 1 week. It works. But now I also want to take into account whether the exception dates are in my table tblExcludeDates. If the date is in that table between the specified date and 1 week back in my dateded then these day/days should also be count off in my dateadd

CREATE TABLE tblExcludeDates ( 
   ExceptionDate DATE, 
   Type  INTEGER );
INSERT tblExcludeDates VALUES ('2019-01-22', 1);
INSERT tblExcludeDates VALUES ('2019-04-24', 1);
INSERT tblExcludeDates VALUES ('2019-04-25', 1);
INSERT tblExcludeDates VALUES ('2019-05-01', 1);
INSERT tblExcludeDates VALUES ('2019-12-24', 1);
INSERT tblExcludeDates VALUES ('2019-12-25', 1);

COMMIT;


Select date(dateadd(wk,-1,'2019-01-24'));

This gives me '2019-01-17'.

But if dates exist in tblExcludeDates between 2019-01-17 and 2019-01-24 it should count off these days also.

So in this case, date 2019-01-22 exists in the table tblExcludeDates, that day should be counted off and results should instead be 2019-01-16.

[VolkerBarth]

Hm, is that not already answered in your question from last week?

[former_SQLA_member1694869]

I thought this part question was different and would be dealt with in a separate question. But you are right, parts of your example are the answer to this question as well. Thanks again.

[MarkCulp · Accepted solution]

Here is an idea to try: count the number of entries in your exclusion table between the specified date and the week (7 days) earlier and then subtract 7+(the count) from the date to get the date that you are wanting. Something like this (not tested):

select dateadd( day, -7-(
         select count(*)
           from tblExcludeDates
          where exceptionDate between
             dateadd( day, -7, '2019-01-24' ) and date('2019-01-24')
         ), '2019-01-24'
       );