I am looking to get entries from the following two tables
if the Application for participation in SSN and EIN does not contain a record of eligibility in the transaction in which the ServiceDate of the transaction is within the BenefitEffectiveDate and GracePeriodEndDate limits.
there may be several entries for one participant in the eligibility table with a different planning period for this participant SSN and EIN.
for example, below the script there are two entries for the participant SSN = 645075498 in the correspondence table, and both entries have different BenefitEffectiveDate and GracePeriodEndDate, from this table we should take the minimum BenefitEffectiveDate and the maximum GracePeriodEndDate. this means that for this participant the minimum date is 2015-01-01, and the maximum date is 2018-01-01
Now in the transaction table I have the same participant SSN = 645075498, which has a transaction date 2016-02-10 that falls between the minimum value of BenefitEffectiveDate and the maximum GracePeriodEndDate in the acceptability table for the participantSSN = 645075498
I want to get an entry in my output for a participantSSN = 645075498
How can i achieve this? So far I have written a query below that does not give me memberSSN = 645075498 as a result of the output.
This is my expected result.
| Tid | TPAId | EIN | ParticipantSSN | ParticipantFirstName | ParticipantLastName | TPAParentTransactionId | TPATransactionId | ServiceDate | TransactionDate | Amt |
| 118 | PayFlex | 54401 | 852258852 | GABRIEL | BRYANT | 45758201 | 55277801 | 2016-01-01 | 2016-01-02 | 15 |
| 124 | PayFlex | 54407 | 420145857 | CAROLYN | WOMAC | 45758207 | 55277807 | 2016-03-15 | 2016-03-15 | 60 |
| 125 | PayFlex | 54408 | 345658570 | THOMAS | FAVELA | 45758208 | 55277808 | 2016-03-16 | 2016-03-18 | 60 |
| 126 | PayFlex | 54409 | 541575015 | BETTY | DAVIS | 45758209 | 55277809 | 2016-03-17 | 2016-03-20 | 60 |
| 127 | PayFlex | 54410 | 541575015 | BETTY | DAVIS | 45758209 | 55277809 | 2016-03-17 | 2016-03-20 | 60 |
| 128 | PayFlex | 54409 | 541575015 | BETTY | DAVIS | 45758210 | 55277809 | 2016-03-17 | 2016-03-20 | 60 |
| 129 | PayFlex | 54409 | 541575016 | MANDY | THOMPSON | 45758211 | 55277810 | 2016-03-18 | 2016-03-20 | 80 |
| 130 | PayFlex | 54409 | 541575018 | FRANCIS | GRAYER | 45758212 | 55277811 | 2016-03-19 | 2016-03-22 | 79 |
| 122 | PayFlex | 54405 | 645075498 | MARY | WILSON | 45758205 | 55277805 | 2016-02-07 | 2016-02-10 | 100 |
My current SQL query
SELECT ParticipantFirstName, ServiceDate, *
FROM DebitCardTransaction d
WHERE NOT EXISTS
(
SELECT 1
FROM Eligibility e
WHERE e.TPAId = d.TPAId
AND e.EIN = d.EIN
AND e.ParticipantSSN = d.ParticipantSSN
AND d.ServiceDate BETWEEN BenefitEffectiveDate AND GracePeriodEndDate
)
Above this my current query and below is an example of a table for creating and pasting sample script data
CREATE TABLE Eligibility(
EligibilityId INTEGER PRIMARY KEY
,TPAId VARCHAR(7)
,EIN INTEGER
,SubscriberId INTEGER
,ParticipantFirstName VARCHAR(9)
,ParticipantLastName VARCHAR(9)
,ParticipantSSN INTEGER
,BenefitEffectiveDate DATE
,GracePeriodEndDate DATE
);
INSERT INTO Eligibility(EligibilityId,TPAId,EIN,SubscriberId,ParticipantFirstName,ParticipantLastName,ParticipantSSN,BenefitEffectiveDate,GracePeriodEndDate) VALUES (227,'PayFlex',54406,15857506,'TIM','HOPE',138764141,'1/1/2016','2/2/2017');
INSERT INTO Eligibility(EligibilityId,TPAId,EIN,SubscriberId,ParticipantFirstName,ParticipantLastName,ParticipantSSN,BenefitEffectiveDate,GracePeriodEndDate) VALUES (228,'PayFlex',54401,15857501,'BRIEL','BRYANT',852258851,'1/1/2016','2/2/2017');
INSERT INTO Eligibility(EligibilityId,TPAId,EIN,SubscriberId,ParticipantFirstName,ParticipantLastName,ParticipantSSN,BenefitEffectiveDate,GracePeriodEndDate) VALUES (229,'PayFlex',54402,15857502,'LISA','PEREZ',292225757,'1/1/2016','2/2/2017');
INSERT INTO Eligibility(EligibilityId,TPAId,EIN,SubscriberId,ParticipantFirstName,ParticipantLastName,ParticipantSSN,BenefitEffectiveDate,GracePeriodEndDate) VALUES (230,'PayFlex',54403,15857503,'ALEXANDER','JEFFERSON',643035714,'1/1/2016','2/2/2017');
INSERT INTO Eligibility(EligibilityId,TPAId,EIN,SubscriberId,ParticipantFirstName,ParticipantLastName,ParticipantSSN,BenefitEffectiveDate,GracePeriodEndDate) VALUES (231,'PayFlex',54404,15857504,'SHIRLEY','PEREZ',458250578,'1/1/2016','2/2/2017');
INSERT INTO Eligibility(EligibilityId,TPAId,EIN,SubscriberId,ParticipantFirstName,ParticipantLastName,ParticipantSSN,BenefitEffectiveDate,GracePeriodEndDate) VALUES (232,'PayFlex',54405,15857505,'MARY','WILSON',645075498,'1/1/2015','2/2/2016');
INSERT INTO Eligibility(EligibilityId,TPAId,EIN,SubscriberId,ParticipantFirstName,ParticipantLastName,ParticipantSSN,BenefitEffectiveDate,GracePeriodEndDate) VALUES (233,'PayFlex',54405,15857505,'MARY','WILSON',645075498,'1/1/2016','2/2/2018');
CREATE TABLE DebitCardTransaction(
Tid INTEGER PRIMARY KEY
,TPAId VARCHAR(7)
,EIN INTEGER
,ParticipantSSN INTEGER
,ParticipantFirstName VARCHAR(9)
,ParticipantLastName VARCHAR(9)
,TPAParentTransactionId INTEGER
,TPATransactionId INTEGER
,ServiceDate DATE
,TransactionDate DATE
,Amt INTEGER
);
INSERT INTO DebitCardTransaction(Tid,TPAId,EIN,ParticipantSSN,ParticipantFirstName,ParticipantLastName,TPAParentTransactionId,TPATransactionId,ServiceDate,TransactionDate,Amt) VALUES (118,'PayFlex',54401,852258852,'GABRIEL','BRYANT',45758201,55277801,'1/1/2016','1/2/2016',15);
INSERT INTO DebitCardTransaction(Tid,TPAId,EIN,ParticipantSSN,ParticipantFirstName,ParticipantLastName,TPAParentTransactionId,TPATransactionId,ServiceDate,TransactionDate,Amt) VALUES (119,'PayFlex',54402,292225757,'LISA','PEREZ',45758202,55277802,'2/1/2016','2/2/2016',50);
INSERT INTO DebitCardTransaction(Tid,TPAId,EIN,ParticipantSSN,ParticipantFirstName,ParticipantLastName,TPAParentTransactionId,TPATransactionId,ServiceDate,TransactionDate,Amt) VALUES (120,'PayFlex',54403,643035714,'ALEXANDER','JEFFERSON',45758203,55277803,'2/2/2016','2/3/2016',50);
INSERT INTO DebitCardTransaction(Tid,TPAId,EIN,ParticipantSSN,ParticipantFirstName,ParticipantLastName,TPAParentTransactionId,TPATransactionId,ServiceDate,TransactionDate,Amt) VALUES (121,'PayFlex',54404,458250578,'SHIRLEY','PEREZ',45758204,55277804,'2/3/2016','2/5/2016',50);
INSERT INTO DebitCardTransaction(Tid,TPAId,EIN,ParticipantSSN,ParticipantFirstName,ParticipantLastName,TPAParentTransactionId,TPATransactionId,ServiceDate,TransactionDate,Amt) VALUES (122,'PayFlex',54405,645075498,'MARY','WILSON',45758205,55277805,'2/7/2016','2/10/2016',100);
INSERT INTO DebitCardTransaction(Tid,TPAId,EIN,ParticipantSSN,ParticipantFirstName,ParticipantLastName,TPAParentTransactionId,TPATransactionId,ServiceDate,TransactionDate,Amt) VALUES (123,'PayFlex',54406,138764141,'TIM','HOPE',45758206,55277806,'2/10/2016','2/11/2016',110);
INSERT INTO DebitCardTransaction(Tid,TPAId,EIN,ParticipantSSN,ParticipantFirstName,ParticipantLastName,TPAParentTransactionId,TPATransactionId,ServiceDate,TransactionDate,Amt) VALUES (124,'PayFlex',54407,420145857,'CAROLYN','WOMAC',45758207,55277807,'3/15/2016','3/15/2016',60);
INSERT INTO DebitCardTransaction(Tid,TPAId,EIN,ParticipantSSN,ParticipantFirstName,ParticipantLastName,TPAParentTransactionId,TPATransactionId,ServiceDate,TransactionDate,Amt) VALUES (125,'PayFlex',54408,345658570,'THOMAS','FAVELA',45758208,55277808,'3/16/2016','3/18/2016',60);
INSERT INTO DebitCardTransaction(Tid,TPAId,EIN,ParticipantSSN,ParticipantFirstName,ParticipantLastName,TPAParentTransactionId,TPATransactionId,ServiceDate,TransactionDate,Amt) VALUES (126,'PayFlex',54409,541575015,'BETTY','DAVIS',45758209,55277809,'3/17/2016','3/20/2016',60);
INSERT INTO DebitCardTransaction(Tid,TPAId,EIN,ParticipantSSN,ParticipantFirstName,ParticipantLastName,TPAParentTransactionId,TPATransactionId,ServiceDate,TransactionDate,Amt) VALUES (127,'PayFlex',54410,541575015,'BETTY','DAVIS',45758209,55277809,'3/17/2016','3/20/2016',60);
INSERT INTO DebitCardTransaction(Tid,TPAId,EIN,ParticipantSSN,ParticipantFirstName,ParticipantLastName,TPAParentTransactionId,TPATransactionId,ServiceDate,TransactionDate,Amt) VALUES (128,'PayFlex',54409,541575015,'BETTY','DAVIS',45758210,55277809,'3/17/2016','3/20/2016',60);
INSERT INTO DebitCardTransaction(Tid,TPAId,EIN,ParticipantSSN,ParticipantFirstName,ParticipantLastName,TPAParentTransactionId,TPATransactionId,ServiceDate,TransactionDate,Amt) VALUES (129,'PayFlex',54409,541575016,'MANDY','THOMPSON',45758211,55277810,'3/18/2016','3/20/2016',80);
INSERT INTO DebitCardTransaction(Tid,TPAId,EIN,ParticipantSSN,ParticipantFirstName,ParticipantLastName,TPAParentTransactionId,TPATransactionId,ServiceDate,TransactionDate,Amt) VALUES (130,'PayFlex',54409,541575018,'FRANCIS','GRAYER',45758212,55277811,'3/19/2016','3/22/2016',79);