Using Case Statement for Dates in Where Clause RRS feed

  • Question

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

    Thursday, July 11, 2019 2:14 AM