I'm getting the following error :
Conversion failed when converting the varchar value 'Jan 1 1900 12:00AM' to data type int.
I have tried casting the problematic field which is in my third CTE(CTE4)
WHEN max(Date_Last_Data_Changed )= '1900-01-01 00:00:00.000' THEN 'Has never been changed'
I have looked at different places
But still was not able to figure out this problem
;with CTE1 AS ( Select Year_Last_Accesed, (Count(Year_Last_Accesed)* 100 / (select Count(*) From _Admin_Database_Objects_Analysis_Tbl)) as Percentage_Result From _Admin_Database_Objects_Analysis_Tbl Group by Year_Last_Accesed ), CTE2 AS( Select Year_Last_Accesed, (Count(Year_Last_Accesed)* 100 / (select Count(*) From _Admin_Database_Objects_Analysis_Tbl)) as [10 Years older Tables] From _Admin_Database_Objects_Analysis_Tbl Where Date_Created > DATEADD(year,-10,GETDATE()) Group by Year_Last_Accesed ) , CTE4 AS ( SELECT Object_Name, MAX(Date_Last_Data_Changed) as [Last Time Data Changed] ,CASE WHEN max(cast(Date_Last_Data_Changed as varchar(255))) >1900 THEN 'Has Been changed recently' WHEN max(Date_Last_Data_Changed )= '1900-01-01 00:00:00.000' THEN 'Has never been changed' end as New_Date_Last_Data_Changed FROM _Admin_Database_Objects_Analysis_Tbl GROUP BY Object_Name ), CTE5 AS ( SELECT ct4.New_Date_Last_Data_Changed ,(Count(New_Date_Last_Data_Changed)* 100 / (select Count(*) From _Admin_Database_Objects_Analysis_Tbl)) as [Data Changed %] FROM _Admin_Database_Objects_Analysis_Tbl ct5 Inner Join CTE4 ct4 on ct4.Object_Name = ct5.Object_Name Group by ct4.New_Date_Last_Data_Changed ) Select Percentage_Result, case When c1.Year_Last_Accesed = '1900' then 'Never Used' When c1.Year_Last_Accesed = '2018' then '2018' end as Year_Last_Accesed ,[10 Years older Tables] FROM CTE1 c1 INNER JOIN CTE2 c2 ON c1.Year_Last_Accesed=c2.Year_Last_Accesed INNER JOIN CTE5 CT5 ON ct5.New_Date_Last_Data_Changed = c1.Year_Last_Accesed