Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Indicator logic

Author  Topic 

rsmohankumar
Starting Member

11 Posts

Posted - 2015-02-02 : 05:54:45

Hi,

I have data like below, I need to populate the ID_INDICATOR columns with the below condition

ID TASK_ID TASK_COMPLETEDTS TASK_DUETS TASK_INDICATOR ID_INDICATOR
1 1 2014-06-09 00:00:00 2014-06-11 00:00:00 GREEN
1 2 2014-06-13 00:00:00 2014-06-14 00:00:00 AMBER
1 3 2014-06-17 00:00:00 2014-06-16 00:00:00 RED
1 4 2014-06-17 00:00:00 2014-06-18 00:00:00 AMBER

Condition:
##########


Red = If ID due date was overdue, i.e. if last task completed after the ID end date(2014-06-18 00:00:00).
AMBER= If any task in the ID is overdue but completed before the ID end date(2014-06-18 00:00:00)
Green = If all tasks were completed on time.

I am looking for the logic to implement the AMBER for the whole ID, because the TASK_ID 3 is overdue, but
completed before the ID end date (2014-06-18 00:00:00).

Can anyone help me on this?

Thanks in advance.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-02 : 07:09:44
code your conditions in case expressions.
Go to Top of Page

rsmohankumar
Starting Member

11 Posts

Posted - 2015-02-02 : 08:04:54
Hi,

I tried the case conditon.Third records matches the condition 2, so I need the AMBER in the ID_INDICATOR colums for the whole ID.... I need to check all the rows and how to display the common output on ID_INDICATOR on the ID 1.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-02 : 09:38:48
I think I have a possible solution (or a least an approach). Note, there is at least one condition you have not accounted for, as I read it:

What if the ID is neither RED nor AMBER but some task was completed late? e.g. task 3 was late, but the last task was completed on the 17th which is before the 18th and task 3 was completed before the 18th.


declare @t table (ID int,TASK_ID int,TASK_COMPLETEDTS datetime, TASK_DUETS datetime, TASK_INDICATOR varchar(10), ID_INDICATOR varchar(10))
insert into @t
(
ID, TASK_ID, TASK_COMPLETEDTS, TASK_DUETS, TASK_INDICATOR
)
values
(1, 1,'2014-06-09 00:00:00',' 2014-06-11 00:00:00',' GREEN'),
(1, 2,'2014-06-13 00:00:00',' 2014-06-14 00:00:00',' AMBER'),
(1, 3,'2014-06-17 00:00:00',' 2014-06-16 00:00:00',' RED '),
(1, 4,'2014-06-17 00:00:00',' 2014-06-18 00:00:00',' AMBER')

select id, TASK_ID, TASK_COMPLETEDTS, TASK_DUETS, TASK_INDICATOR
, case
when mx.comp > mx.due then 'Red'
when TASK_COMPLETEDTS > TASK_DUETS
and TASK_COMPLETEDTS <= mx.comp then 'AMBER'
when not exists (
select 1 from @t
where TASK_COMPLETEDTS > TASK_DUETS
) Then 'Green'
END as ID_INDICATOR

from @t t
cross apply (
select max(task_completedts), max(task_duets)
from @t t1
where t.id = t1.id
group by id
) mx(comp, due)
Go to Top of Page

rsmohankumar
Starting Member

11 Posts

Posted - 2015-02-03 : 08:42:13
Hi,

Thanks for your inputs.But, we are getting AMBER is corresponding row only. I am looking for the AMBER to be displayed for all the rows. Now, We are getting the NULL values in other rows.
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-02-03 : 09:09:38
Maybe:

WITH MaxVals
AS
(
SELECT *
,MAX(TASK_COMPLETEDTS) OVER (PARTITION BY ID) AS MaxTASK_COMPLETEDTS
,MAX(TASK_DUETS) OVER (PARTITION BY ID) AS MaxTASK_DUETS
,SUM(CASE WHEN TASK_COMPLETEDTS > TASK_DUETS THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS IsAmber
FROM @t
)
UPDATE MaxVals
SET ID_INDICATOR =
CASE
WHEN MaxTASK_COMPLETEDTS > MaxTASK_DUETS THEN 'Red'
WHEN IsAmber > 0 THEN 'Amber'
ELSE 'Green'
END;
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-03 : 09:27:23
Did you try to rework my solution to better fit your needs?
Go to Top of Page
   

- Advertisement -