Averaging TAT calculation 1

[xrayr]

I'm trying to average a TAT calculation and am having issues. The data being returned is always 0.00. The TAT calculation comes in with the use of a stored procedure called GetWorkDays. The stored procedure basically does a DateDiff while factoring weekends and company holidays out of the resulting TAT number. The stored procedure works fine and has been in place for a very long time.

I've looked through archives here and have tried various cast and conversions. Still can't get this.

Would appreciate any help......



SELECT AVG((CASE WHEN [SSQIPC_End] BETWEEN DateADD(day, - 27, @End_Week_4) AND DateADD(day, - 21, @End_Week_4) AND
[Lead_Time_Activity] = 'Fully Insured Preferred Portfolio' AND [Activity Type Txt] IN ('Maintenance / Re-work', 'Off Renewal Plan Change') AND
[SSQIPC_Start] IS NOT NULL THEN dbo.GetWorkDays([SSQIPC_Start], [SSQIPC_End]) END)) AS Week1_SSR_PP_Renew_AllSites_TAT_In_Target
FROM tbl_Functional_Dates

 

[starsky51]

The value returned from dbo.GetWorkDays in this context is just the status of the execution of the stored proc. A return value of 0 means the execution was successful, which is why your formula is returning zero every time .

To have it work the way you want, you would have to either convert your stored proc into a scalar function or look into using the OUTPUT parameter with the stored proc to retrieve the result.

 

[starsky51]

What does TAT stand for btw?

 

[xrayr]

TAT refers to Turn Around Time. Thank you! I'll look into your suggestions...