CODEWITHSUNDEEP
sql-server
Anita Prasad
Anita Prasad

Reputation: 25

how to find out max of a field for two different conditions in sql

I have a table EMPDATA with the following data:

EntityId    MeetDate    SourceCode  Status
1           06.11.2017   AB          FNL
1           05.2.2018    AB          NO
1           09.3.2018    AB          FNL
3           07.12.2016   AB          FNL
3           09.2.2015    AB          FNL
3           07.8.2014    IU          FNL
3           08.7.2017    IU          FNL

Conditions :

1) MeetDate Column will be split in two columns in the output

1a) LastDate: This will be the latest Meetdate for SourceCode ‘AB’ and status ‘FNL’.

1b) InterimDate: This will be the latest Meetdate for SourceCode ‘IU’ which has occurred after the latest Meetdate for SourceCode ‘AB’ and Status ‘FNL’.

For records with no IU sourcecode interimdate will be null.

That is: Output will be as follows:

EntityId    LastDate    InterimDate 
1           09.3.2018      NULL          
3           07.12.2016    08.7.2017

Upvotes: 0

Views: 109

Answers (2)

Emdad
Emdad

Reputation: 832

@Anita, I coudn't provide answer your latest question "Finding max date with different conditions [duplicate]" due to set as duplicate question. You can try the following query 

SELECT B.EntityId,A.Eligibility,A.MeetDate,B.LastDate, B.InterimDate, 
(CASE WHEN A.Eligibility=1 THEN DATEADD(MONTH,6,A.MeetDate) ELSE DATEADD(Month,4,A.MeetDate) END) AS BusinessDate FROM StackTable A
INNER JOIN
(SELECT EntityId,
       MAX(CASE 
              WHEN SourceCode = 'AB' AND status = 'FNL' THEN MeetDate 
           END) AS LastDate,
       MAX(CASE WHEN SourceCode = 'IU' THEN MeetDate END) AS InterimDate

FROM StackTable
GROUP BY EntityId) B
ON A.EntityId=B.EntityId AND A.MeetDate = B.LastDate

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can easily achieve the expected output using conditional aggregation:

SELECT EntityId,
       MAX(CASE 
              WHEN SourceCode = 'AB' AND status = 'FNL' THEN MeetDate 
           END) AS LastDate,
       MAX(CASE WHEN SourceCode = 'IU' THEN MeetDate END) AS InterimDate          
FROM mytable
GROUP BY EntityId

This query implements all logic described in the OP except for:

  • InterimDate ... which has occurred after the latest Meetdate for SourceCode ‘AB’ and Status ‘FNL’.

You can implement this using a CTE so that the code looks cleaner:

;WITH CTE AS (
SELECT EntityId,
       MAX(CASE 
              WHEN SourceCode = 'AB' AND status = 'FNL' THEN MeetDate 
           END) AS LastDate,
       MAX(CASE WHEN SourceCode = 'IU' THEN MeetDate END) AS InterimDate          
FROM mytable
GROUP BY EntityId
)
SELECT LastDate, 
       CASE 
          WHEN InterimDate > LastDate THEN InterimDate
       END AS InterimDate
FROM CTE

Demo here

Upvotes: 1

Related Questions