Best way to do this.

[ssv45324]

I have table like this -

ID doc_id approval_status

1 1 0

2 1 1

3 1 3

4 2 1

5 2 1

6 3 1

I would want to write a sql which will give me the doc_id and the approval status

like

doc_id approval_status

1 3

2 1

3 1

where 1 stands for approved and 3 stands for denied.

Which is the best way in SQL to do this.

 

[gmmastros]

Select doc_id, Max(approval_status)
From Table
Group By doc_id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ssv45324]

Thanks for the reply. But i have aa status of 0(not approved) so if a doc_id has the following entries 0, 0, 1 it will be 1(approved) but thats not really the case.

 

[vbkris]

>>so if a doc_id has the following entries 0, 0, 1 it will be 1(approved) but thats not really the case.

dont understand, can u explain the rules for the status???

Known is handfull, Unknown is worldfull

 

[ssv45324]

Thanks for all your responses. The rules are as follows.
A document is considered to be approved if it has all 1 s in the approval status column.
Even if one entry is not = 1, then the document is not approved.

A document is considered denied if it has any one of the value as 3.

 

[gmmastros]

Does this satisfy all the rules?

Note: I added a temp table for testing purposes.

Declare @Temp Table (Id Integer, doc_id Integer, Approval_status Integer)

Insert into @temp Values(1,1,0)
Insert into @temp Values(2,1,1)
Insert into @temp Values(3,1,3)
Insert into @temp Values(4,2,1)
Insert into @temp Values(5,2,1)
Insert into @temp Values(6,3,1)

Select doc_id,
       Case When Min(approval_status)=1 And Max(approval_status)=1 Then 'approved' 
            When Max(approval_status) > 1 Then 'Denied'
            When Min(approval_status)=0 Then 'not approved'
            Else 'Denied'
            End
From   @Temp
Group By doc_id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

here is a variation on George's code

Declare @Temp Table (Id Integer, doc_id Integer, Approval_status Integer)

Insert into @temp Values(1,1,0)
Insert into @temp Values(2,1,1)
Insert into @temp Values(3,1,3)
Insert into @temp Values(4,2,1)
Insert into @temp Values(5,2,1)
Insert into @temp Values(6,3,1)

Select doc_id,
       Case When SUM(distinct approval_status)=1  Then 'approved' 
            When SUM(distinct approval_status)=0 Then 'not approved'
            Else 'Denied'
            End
From   @Temp
Group By doc_id

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

I'm not sure but wouldn't that code return 'approved' for 0's and 1's present?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[r937]

you're not sure, vongrunt, but i am -- yes, it would



r937.com | rudy.ca

 

[SQLDenis]

yes you are right

Declare @Temp Table (Id Integer, doc_id Integer, Approval_status Integer)

Insert into @temp Values(1,1,0)
Insert into @temp Values(2,1,1)
Insert into @temp Values(3,1,3)
Insert into @temp Values(4,2,1)
Insert into @temp Values(5,2,1)
Insert into @temp Values(6,3,1)
Insert into @temp Values(7,4,0)
Insert into @temp Values(8,4,1)

Select doc_id,
       Case When SUM(distinct approval_status)=1  and count(distinct approval_status) =1 Then 'approved' 
            When SUM(distinct approval_status)> 1 Then 'denied'
            Else 'not approved'
            End
From   @Temp
Group By doc_id

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Awright, let's do it without CASE statements. Best answer - star.

(btw. I'm not thinkin' about buncha NULLIF/ISNULL/SIGN() calls ).

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[r937]

when you say "let's do it" should we assume that you mean to generate the three strings 'approved', 'not approved', 'denied' without CASE?

r937.com | rudy.ca

 

[vongrunt]

Use string constants for these if you want. Just don't use CASE/WHEN anywhere in code, that's the only restriction.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

Here is one ugly example:

Select doc_id, 'Approved'
from @Temp T
where 1 = all(select approval_status from @Temp T2 where T2.doc_id = T.doc_id)
union
select doc_id, 'Denied'
from @Temp T
where 3 = any(select approval_status from @Temp T2 where T2.doc_id = T.doc_id)
union 
select doc_id, 'Not Approved'
from @Temp T
where 3 <> all(select approval_status from @Temp T2 where T2.doc_id = T.doc_id)
   and 1<> any(select approval_status from @Temp T2 where T2.doc_id = T.doc_id)

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

And another one

select distinct T.doc_id, coalesce(X1.status, X2.status, X3.status)
from @temp T
left outer join
(	select doc_id, 'Approved' as status
	from @Temp
	group by doc_id
	having min(approval_status)=1 and max(approval_status)=1
) X1 on T.doc_id = X1.doc_id
left outer join
(	select distinct doc_id, 'Denied' as status
	from @Temp
	where approval_status = 3
) X2 on T.doc_id = X2.doc_id 
left outer join
(	select distinct doc_id, 'Not Approved' as status
	from @Temp
	where approval_status not in (1, 3)
) X3 on T.doc_id = X3.doc_id

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]