sql case when 较比复杂的用法

sql case when 较复杂的用法

select emp_id,
       biz_date,
       case when
   sum(case when morning =1 and afternoon =1
     then 1
else 0
   end
               )
   +
   max(case when morning =1 and afternoon =0
then 1
else 0
   end
               )
               +
   max(case when morning = 0 and afternoon =1
     then 1
else 0
   end
               )
   >=2
      then 2
               else
  sum(case when morning =1 and afternoon =1
   then 1
  else 0
  end)
  +
  max(case when morning =1 and afternoon =0
   then 1
  else 0
  end)
  +
  max(case when morning = 0 and afternoon =1
   then 1
  else 0
  end)
       end as vsttimes
from
(
     SELECT  
             emp_id,
             biz_date,
             sr_id,
             max(case when convert(varchar(2),intime,108) < 12
                           then 1
                      else 0
                 end) as morning,

             max(case when convert(varchar(2),intime,108) >= 12
                           then 1
                      else 0
                 end) as afternoon
 
FROM RPT_INOUT_STORE WITH (nolock)
WHERE FUNC_CODE='F50S01'
      AND BIZ_DATE>= @current_year+'-01-01'
      AND BIZ_DATE<= @current_year+'-12-31'
GROUP BY EMP_ID,biz_date,sr_id

) a
group by a.emp_id,a.biz_date