T-SQL Query Help

  • Hello,

    I am working on one of the SQL Logic, i need help to build core logic to do the rest,

    below is ddl,

    create table #x1
    (
    task varchar(100),
    subtask varchar(100),
    status varchar(100)
    )


    insert into #x1 values ('a','x','completed')
    insert into #x1 values ('b','l','overdue')
    insert into #x1 values ('b','m','completed')
    insert into #x1 values ('b','n','not started')
    insert into #x1 values ('c','p','not started')
    insert into #x1 values ('d','q','overdue')
    insert into #x1 values ('d','r','completed')

    select *
    from #x1

    The requirement is, when any of subtask got completed status, I need to display 1 for the Task

    desire output is as below

    task   subtask   status            Status(1/0)    FinalStatus

    a        x              completed     1                       1

    b        l               overdue         0                      1

    b       m              completed     1                      1

    b        n              not started    0                      1

    c       p               not started    0                      0

    d      q                overdue         0                      1

    d      r                 completed    1                       1

    Please help me to build this.

    Thanks for your help or advise.

    • This topic was modified 1 year ago by  yogi123.
  • Sorguyu deneyin.

    select H.*,CASE WHEN H.STATUS = 'completed' THEN '1' ELSE 0 END [Status(1/0)],
    CASE WHEN SAY.DEGER IS NULL THEN 0 ELSE 1 END [FinalStatus]
    from #x1 H
    OUTER APPLY (select 1 AS DEGER from #x1 J WHERE J.STATUS = 'completed' AND H.TASK=J.TASK ) SAY

    Allah bize yeter, O ne güzel vekildir.

    vedatoozer@gmail.com

  • Thank You! That works ...

  • This was removed by the editor as SPAM

  • Just be careful if there's more than 1 completed subtask for the same task 🙂

     

     

  • Could you use EXISTS () with a correlated subquery instead of APPLY? (or is that just brutally slow?)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply