Logic Help

Analytics
Fan

Logic Help

Need help in making the logic work in this query. The logic works for the first case statement for the TAT_APL_ACK, but the second

CASE statement we need help on, it’s not failing, or throwing any error, but the calculation done in that part is somehow wrong.

 

 

 

Appeals Team also wanted to add a column to the TAT report that would calculate the Appeals Acknowledgement TAT by business days.  Currently there is an Ack TAT column that calculates by calendar days. 

 

,CASE WHEN DIM_APPEAL_INFO.ACKNMT_LETTER_SENT_DATE IS NOT NULL THEN cast(DIM_APPEAL_INFO.ACKNMT_LETTER_SENT_DATE as date)-CAST( DIM_APPEAL_INFO.APPEAL_RECEIVED_DATE AS DATE)

                ELSE CURRENT_DATE-CAST( DIM_APPEAL_INFO.APPEAL_RECEIVED_DATE AS DATE) END AS TAT_APL_ACK

               

 

 

                ,CASE WHEN DIM_APPEAL_INFO.ACKNMT_LETTER_SENT_DATE IS NOT NULL THEN CAST((cast(DIM_APPEAL_INFO.ACKNMT_LETTER_SENT_DATE as date)-CAST( DIM_APPEAL_INFO.APPEAL_RECEIVED_DATE AS DATE)) as int)-CAST((select

count(wknd_ind)

FROM    ETL_ACCESS_OWN.DIM_DATE

where

date_date between DIM_APPEAL_INFO.ACKNMT_LETTER_SENT_DATE and DIM_APPEAL_INFO.APPEAL_RECEIVED_DATE

and wknd_ind in ('Y'))as int)

                ELSE CAST((CURRENT_DATE-CAST( DIM_APPEAL_INFO.APPEAL_RECEIVED_DATE AS DATE))as INT)-CAST((select

count(wknd_ind)

FROM    ETL_ACCESS_OWN.DIM_DATE

where

date_date between CURRENT_DATE and DIM_APPEAL_INFO.APPEAL_RECEIVED_DATE

and wknd_ind in ('Y'))as int)

                END AS TAT_APL_ACK_BUS