从SQL Server中的多个表获取两个日期之间的记录

我希望从以下两个表中获取记录

如果交易的ServiceDate在BenefitEffectiveDate和GracePeriodEndDate内，则该交易的参与者SSN和EIN的资格中没有记录。

对于一个给定的参与者SSN和EIN，资格表中可能有针对同一参与者的多个记录，且记录计划不同。

例如，在下面的脚本中，资格表中有参与者SSN = 645075498的两条记录，并且两条记录都有不同的BenefitEffectiveDate和GracePeriodEndDate，从此表中我们必须获取最小的BenefitEffectiveDate和最大的GracePeriodEndDate。这表示该参与者的最短日期为2015年1月1日，最大日期为2018年1月1日

现在在交易表中我具有相同的参与者SSN = 645075498，交易日期为2016-02-10，介于参与者SSN = 645075498资格表中的最小BenefitEffectiveDate和最大GracePeriodEndDate之间

我想在我的参与者的输出中获得记录SSN = 645075498

我该如何实现？到目前为止，我已经在下面的查询中编写了以下查询，该查询未在输出结果中给我参与者SSN = 645075498。

这是我的预期结果

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

我当前的SQL查询

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
 )

在它上面，我当前的查询，下面是示例创建表并插入示例数据脚本

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