Date interval + 7 day

Queries and Resources
1

Hello I am fairly new to retool!
I have a table that contains a schedule for employees, events are 1 week duration and only the first day of the event is recorded in the table. I have another table that contains employees availability/vacation, time off by day. I am trying to figure out how to filter events that are scheduled during an employees vacation or requested days off? (I cannot see to get the 7 day interval to work)

SELECT SA.eid, SA.last, SA.first, SA.week, SA.event, AA.reason, AA.date
FROM schedule_assignments as SA
LEFT OUTER JOIN
availability_and_attendance as AA
ON SA.eid = AA.eid
WHERE AA.date BETWEEN SA.week and SA.week + 7 day
Group by SA.eid, SA.last, SA.first, SA.week, SA.event, AA.reason, AA.date

HAVING (AA.Reason = 'R') OR
(AA.Reason = 'V')

Schedule Assignments:

image
image747×72 3.13 KB

Availability:
image

2

Hello all,
I was able to adjust my code and got it to work:

SELECT SA.eid, SA.last, SA.first, TO_CHAR(SA.week, 'YYYY-MM-DD') as Week, SA.event, AA.reason, TO_CHAR(AA.date, 'YYYY-MM-DD') as Date
FROM schedule_assignments as SA
LEFT OUTER JOIN
availability_and_attendance as AA
ON SA.eid = AA.eid
WHERE AA.date BETWEEN SA.week and SA.week + INTERVAL '7 DAY'
Group by SA.eid, SA.last, SA.first, SA.week, SA.event, AA.reason, AA.date

HAVING (AA.Reason = 'R') OR
(AA.Reason = 'V')