I have two tables elig(mID, startDate, endDate) and claim(mID, serviceDate). I have to check if the serviceDate is between the startDate and endDate, and insert into a new table hasClaim(mID,startDate,endDate,Flag). The flag is to be set to 'Y' if the condition in satisfied and 'N' otherwise. What I have done is:
INSERT INTO hasClaim (mID, startDate, endDate, has_claim) SELECT e.mID, e.startDate, e.endDate, 'Y' FROM elig e inner JOIN claim c ON e.mID=c.mID WHERE c.serviceDate BETWEEN e.startDate AND e.endDate
But this only returns those values whose serviceDate is between the startDate and endDate with the flag set to 'Y'. How to retrieve other values as well and set the flag to 'N'? I am using oracle.