CODEWITHSUNDEEP
sql-server-2008
SuperKings
SuperKings

Reputation: 89

Get Max in a Group based on a condition

ProjID  Dno RNo Status       DateApproved
  100   1   1   Initiated   2014-12-31 09:15:58.000
  100   1   1   Approved    2015-01-31 09:15:58.000
  100   1   1   Approved    2015-02-01 09:15:58.000
  100   1   1   Approved    2015-05-28 09:15:58.000
  100   1   1   Approved    2015-06-20 09:15:58.000
  101   1   1   Approved    2014-12-31 09:15:58.000
  101   1   1   Approved    2015-01-31 09:15:58.000
  101   1   1   Approved    2015-02-01 09:15:58.000
  101   1   1   Approved    2015-05-28 09:15:58.000
  101   1   1   Approved    2015-08-20 09:15:58.000

In the above example i have to get max(Dateapproved) as Dateapproved for each projectid. if all the revision Status are approved in a particular group for eg :project id=101 has all rows in its group having a status as Approved so i have to get the max date : '2015-08-20 09:15:58.000'.But for Projectid=100 one status is still in Initiated State so we have to show Null as Dateapproved .

Thanks in Advance

My output should be like:

ProjId Dno Rno DateApproved
100      1  1   NUll
101      1  1   2015-08-20 09:15:58.000

Example code:

Create table #temp(
ProjectID varchar(35),
Documentno int,
Revisionno int,
Status varchar(35),
DateApproved Datetime)

insert into #temp values ( '100',    1,  1,  'Initiated','2014-12-31 09:15:58')
insert into #temp values ( '100',    1,  1,  'Approved','2015-01-31 09:15:58 ')
insert into #temp values ( '100',    1,  1,  'Approved','2015-02-01 09:15:58 ')
insert into #temp values ( '100',    1,  1,  'Approved','2015-05-28 09:15:58 ')
insert into #temp values ( '100',    1,  1,  'Approved','2015-06-20 09:15:58 ')


insert into #temp values ( '101',    1,  1,  'Approved','2014-12-31 09:15:58 ')
insert into #temp values ( '101',    1,  1,  'Approved','2015-01-31 09:15:58 ')
insert into #temp values ( '101',    1,  1,  'Approved','2015-02-01 09:15:58 ')
insert into #temp values ( '101',    1,  1,  'Approved','2015-05-28 09:15:58 ')
insert into #temp values ( '101',    1,  1,  'Approved','2015-08-20 09:15:58 ')


select * from #temp

Upvotes: 3

Views: 48

Answers (2)

connectedsoftware
connectedsoftware

Reputation: 7087

Try this: 

SELECT T.ProjectID,
       Documentno as Dno, 
       Revisionno  as RNo,
       CASE WHEN SUM(CASE WHEN T.Status <> 'Approved' THEN 1 ELSE 0 END) = 0 
            THEN Max(T.DateApproved) ELSE NULL
       END as DateApproved 
from #temp T
GROUP BY T.ProjectId, Documentno , Revisionno

This gives the following output when run against your test data:

PROJECT ID  DNo    TNo   DateApproved
100         1      1      NULL
101         1      1      2015-08-20 09:15:58.000

Upvotes: 2

GarethD
GarethD

Reputation: 69769

You can do this with a case statement and a conditional aggregate. Get the count of statuses that are not Approved using COUNT(NULLIF(Status, 'Approved')). If this is 0 then get the max date approved:

SELECT  ProjectID,
        DateApproved = CASE WHEN COUNT(NULLIF(Status, 'Approved')) = 0 THEN MAX(DateApproved) END
FROM    #Temp
GROUP BY ProjectID;

Upvotes: 1

Related Questions