May 25, 2023 at 2:44 pm
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.
May 25, 2023 at 3:27 pm
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.
vedatozer@outlook.com
May 25, 2023 at 3:30 pm
Thank You! That works ...
May 25, 2023 at 3:42 pm
This was removed by the editor as SPAM
May 25, 2023 at 4:39 pm
Just be careful if there's more than 1 completed subtask for the same task 🙂
May 26, 2023 at 7:20 pm
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