I have a CTE that has three fields in the table:
CTE Table: Customer C
CustomerId, StartDate and EndDate
I then need to check if I have at least one record in a many table based on if the StartDate and EndDate is between two dates where the CustomerId = CustomerId.
Many Table: CustomerAccounts CA
CustomerId, AcctNumber, StartDate, EndDate
I want to check the CustomerAccounts table for at least one record return WHERE C.CustomerId = CA.CustomerId AND
(CA.StartDate BETWEEN C.StartDate AND C.EndDate) AND (CA.EndDate BETWEEN C.StartDate AND C.EndDate)
How can I do this with a Exists or whatever way is best. The result set returned should have the records from the Customer table only.