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);


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.

asked 23 Feb, 19:00

Rolle's gravatar image

accept rate: 0%


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

(25 Feb, 03:56) Volker Barth

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.

(26 Feb, 16:05) Rolle

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'
permanent link

answered 24 Feb, 14:32

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 23 Feb, 19:00

question was seen: 76 times

last updated: 26 Feb, 16:05