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 #x1The 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, 12 months 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 ) SAYAllah 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