Return null in aggregate column if where clause returns nothing

Question

I have this query in MsSQL

SELECT PersonnelID, Name, EmpStartCalc,
    MAX(PositionDetailsValidFromCalc) PD , 
    Max(PositionHierValidFromCalc) PH, 
    Max(PWAValidFromCalc) Position, 
    Max(RowId) As RowId
    
FROM TV_IAMintegration_VW
    
where 
    EmpStartCalc >= 20200101 and
    EmpStartCalc <= 20200131 and
    
    ((20200131 > PositionDetailsValidFromCalc And 
    20200101 < PositionDetailsValidToCalc) or
    PositionDetailsValidToCalc is null)
    
GROUP BY PersonnelID, Name, EmpStartCalc
    
ORDER BY PersonnelID Asc.

I have a scenario where I need the query to return Null, example:

When where clause doesn't successfully meet I need the query to return null in the following columns

MAX(PositionDetailsValidFromCalc) PD , Max(PositionHierValidFromCalc) PH, Max(PWAValidFromCalc) Position

but rest of the row remains same.

The answer in the other post is inefficient. I have a better answer with conditional aggregation, please reopen. The question here is also much clearer than there — Charlieface, Jan 12 '21 at 14:47
Totally agreed, this question is different than the mentioned duplicate. — AliNawaz, Jan 13 '21 at 07:39
If you edit the post to explain that, you may get it reopened — Charlieface, Jan 13 '21 at 09:09

score 1 · Answer 1 · answered Jan 12 '21 at 14:44

1

You could use a CTE:

with cte as (
      your query here
     )
select cte.*
from cte
union all
select null, null, null, null, null, null
from (values (1)) v(x)
where not exists (select 1 from cte);

answered Jan 12 '21 at 14:44

Gordon Linoff

1,242,037
58
646
786

Return null in aggregate column if where clause returns nothing

1 Answers1