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
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments