Compare the values within the same column against other columns in SQL Server

Question

I have a table as below

id      ParentName    HandleName       CreatedDate       
===================================================
139       MI          MI-Chart-QL       2018-02-20       
139       MI          MI-chart-act      2018-02-20       
139       MI          MI-chart-act      2018-02-20       
139       CRA         CRA-chart-act     2018-02-20
139       CRA         CRA-Chart-act     2018-02-20

I want to add a column with values - intentionally or unintentionally if the HandleName with Act has the same id, CreatedDate and ParentName as the HandleName with QL's id, CreatedDate and ParentName, then it is intentionally. The final table I'm looking for is

id      ParentName    HandleName       CreatedDate    Intentionally/unintentionally   
====================================================================================
139       MI          MI-Chart-QL       2018-02-20       Intentionally
139       MI          MI-chart-act      2018-02-20       Intentionally
139       MI          MI-chart-act      2018-02-20       Intentionally
139       CRA         CRA-chart-act     2018-02-20       Unintentionally   
139       CRA         CRA-Chart-act     2018-02-20       Unintentionally 
  

The HandleName with 'CRA-chart-act' is Unintentionally because the ParentName doesn't match the 'MI-Chart-QL'

I used the below code (if Row_Number()>2 I can mark them intentionally) but how I can check if their parent names are same to mark them Intentionally or unintentionally?

Row_Number() over (Partition by id, CreatedDate ORDER BY createdDate asc)

Answer 1

You can use window functions:

select t.*,
    case when max(case when handlename like '%-QL%' then 1 else 0 end)
                  over(partition by id, parentname, createddate) = 1
        then 'Intentionally'
        else 'Unintentionally'
    end as status
from mytable t