Average Days Open Report-Conversion to Kace
Can someone please help me convert this sql statement from my Old helpdesk into a Kace report. I am trying to calculate average days open.
Select help_category,cast(isnull(AVG(CAST(case when help_status = 'OPEN' then GETDATE()
when help_status = 'CLOSED' then help_close_datetime end - [help_datetime] AS float)),0) as decimal(7,3)) as average_days
where (help_status <> 'CLOSED' or (help_status = 'CLOSED' and cast(help_datetime AS date) = cast(help_close_datetime AS date) and cast(help_datetime AS date) = cast(GETDATE()-1 as DATE))) and help_priority not in('6')
group by help_category
order by help_category
I believe I am close, any and all assistance is helpful....
Just as there are many ways to bake a cake, so are there may ways to do this via SQL. Two are listed below.