DECLARE @FirstOfMonth datetime -- Calculate the first of the month @FirstOfMonth = DATEADD(mm,DATEDIFF(mm,0,EndDate),0) select Claim_Reference, Claim_Registered_Date, EndDate, CASE WHEN overall_stat = "Outstanding" AND Claim_Registered_Date >= @FirstOfMonth THEN "01 - Up to 1 Month" WHEN overall_stat = "Outstanding" AND Claim_Registered_Date >= DATEADD(mm, -2, @FirstOfMonth) AND Claim_Registered_Date < @FirstOfMonth THEN "02 - 1 to 2 Months" WHEN overall_stat = "Outstanding" AND Claim_Registered_Date >= DATEADD(mm, -6, @FirstOfMonth) AND Claim_Registered_Date < DATEADD(mm, -2, @FirstOfMonth) THEN "03 - 2 to 6 Months"