May 17, 2007 at 4:36 am
Hi All,
I have been having headaches with my problem in defining business rules of getting status based on the status of child data in a hierarchy. Below is an illustration of the data.
| IDENTITY_KEY | PARENT_CODE | CODE | STATUS | LEVEL |
| 1 | 100 | 100 | 1 | |
| 2 | 100 | 400 | OPEN | 2 |
| 3 | 100 | 200 | PENDING | 2 |
| 4 | 100 | 300 | CLOSED | 3 |
| 5 | 100 | 350 | PENDING | 4 |
| 6 | 500 | 500 | 1 | |
| 7 | 500 | 600 | CLOSED | 2 |
| 8 | 500 | 650 | CLOSED | 3 |
| 9 | 700 | 700 | 1 | |
| 10 | 700 | 900 | PENDING | 2 |
| 11 | 700 | 800 | PENDING | 2 |
In this dataset, I need to define the status of the parent data depending on the rules below:
1. If ANY of the STATUS is OPEN then the STATUS of the parent level is OPEN
2. If ALL of the STATUS is CLOSED then the STATUS of the parent level is CLOSED
3. If ALL of the STATUS is PENDING then the STATUS of the parent level is PENDING
The tricky part for this is the keyword: ANY. Anyone who can help me? Got ideas? I'll be greatful to get any advice/comments/tips and help. Thanks
goyers
May 17, 2007 at 6:40 am
Will this HELP?
declare
@Table1 as table (
IDENTITY_KEY
int
,PARENT_CODE int
,CODE int
,[STATUS] varchar(10)
,[LEVEL] int)
declare
@Table2 as table (
PARENT_CODE
int
,[STATUS] varchar(10)
,[counter] int)
declare
@table3 as table(
parent_code
int
,[open] int
,[closed] int
,pending int)
insert
into @Table1
select
1, 100, 100,'', 1
union
select
2, 100, 400, 'OPEN', 2
union
select
3, 100, 200, 'PENDING', 2
union
select
4, 100, 300, 'CLOSED', 3
union
select
5, 100, 350, 'PENDING', 4
union
select
6, 500, 500,'',1
union
select
7, 500, 600, 'CLOSED', 2
union
select
8, 500, 650, 'CLOSED', 3
union
select
9, 700, 700,'', 1
union
select
10, 700, 900, 'PENDING', 2
union
select
11, 700, 800, 'PENDING', 2
-- in case you want to pivot the data
insert
into @Table3
select
parent_code
,[OPEN]
,[CLOSED]
,[PENDING]
from
(
SELECT parent_code, code, [status] from @Table1) as pivotsource
PIVOT
(
COUNT
([status])
FOR
[status] in ([OPEN],[CLOSED],[PENDING])
)
as pivot_table
update
@table1
set
[status ] = case when B.[OPEN] <> 0 then 'OPEN'
when B.[OPEN] = 0 and B.[CLOSED] = 0 and B.[PENDING] > 0 then 'PENDING'
when B.[OPEN] = 0 and B.[CLOSED] > 0 and B.[PENDING] = 0 then 'CLOSED'
when B.[OPEN] = 0 and B.[CLOSED] = 0 and B.[PENDING] = 0 then ''
end
from
@Table1 A
inner
join (select
parent_code
,sum([open]) AS [OPEN]
,sum(closed) AS [CLOSED]
,sum(pending) AS [PENDING]
from @Table3
group by parent_code) B ON
A
.parent_code = B.parent_code
where
A.parent_code = A.
May 17, 2007 at 8:09 pm
thanks camilo
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply