|
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. |
|
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'
);
|
Hm, is that not already answered in your question from last week?
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.