Hello,
I've data with start and end dates. Each data has the version type. One of them is the old version and other one is the new version. If the client has been migrated to new version than we need to return the data with the new version type.
Here is the sample data :
Create Table #temp (MemberID INT
,StartDate DATETIME
,EndDate Datetime,
VersionType CHAR(1),
ClientID INT
)
INSERT INTO #temp
SELECT 123, '2018-01-12','2019-01-01',3, 321 UNION
SELECT 124, '2018-04-12','2019-01-01',3, 321 UNION
SELECT 125, '2018-04-12','2019-04-19',3, 241 UNION
SELECT 127, '2019-04-15','2019-05-01',3, 241 UNION
SELECT 127, '2019-04-15','2019-05-01',4, 241
Now, when I query to get the data between the start and end dates of 2018-01-01 to 2019-01-01 for Client ID 321 I should get both the records. Client ID - 321 has not been migrated to new version so I should get both the records with version type -3
But for client -241, it has been migrated on April 20,2019. So, now when I run the query to get the records for Client ID - 241 with start and end dates of 2019-04-01 to 2019-05-01 I should get the record of Member ID - 125 with version type -3 and for member
ID 127, since the dates for end date is outside migration date, it should return the record for 127 with version type - 4
Once the Client has been migrated instead of just the new version, for now we have both the old and new version type.
So, when we are returning the date if the start and end dates are outside of migration date than we need to return the new version other wise old version.
I was trying to use the case logic in the where clause but not able to use it between start and end dates without using UNION Clause.
Can I get some help.
