CREATE procedure rp_alarm_by_day
/* rp_alarm_by_day (1.0)
* Description: Collects and returns data for Alarm by Day Report called by Impromptu
* Parameters: @start and @finish datetime parameters. @fed_flag to select Feds only or Non-Feds
* Returns: Nothing
* Resultset: Data for Alarms by Day Report
* Creator: Citrix
* Date: 04/03/2002
*/
@start datetime,
@finish datetime,
@fed_flag char(1),
@dst_flag char(1)
AS
SET nocount ON
/* Populates #alarm_mins with all alarms occurring during report period */
SELECT ai.alarminc_no, priority, full_clear_opactdisp_id, event_id,
'GC_Date'=case when @dst_flag='N' then dateadd(hh,2,ai.alarm_server_date) else
dateadd(hh,1,ai.alarm_server_date) END
INTO #alarm_mins
FROM alarm_incident ai(nolock)
JOIN client_case cc (nolock) ON ai.case_no=cc.case_no
JOIN customer cu (nolock) ON cc.cust_no=cu.cust_no
WHERE ((dateadd(hh,2,alarm_server_date) between @start and @finish and @dst_flag in ('n','N')) or
(dateadd(hh,1,alarm_server_date) between @start and @finish and @dst_flag in ('y','Y'))) and ((@fed_flag
in ('a','A')) or (@fed_flag in ('f','F') and cu.cust_name like 'US%'))
/* Populate alarm_template with dates*/
SELECT DISTINCT 'Day'=convert(char(8),gc_date,1),'Manually_worked'=0,
'Priority_200'=0, 'Auto_Abort'=0, 'Total'=0
INTO #alarm_template
FROM #alarm_mins (nolock) order by day
/* Populate counts by type */--set nocount off
SELECT 'Day'=convert(char(8),gc_date,1),'type'=case when priority=200 then 'Priority_200'
when priority<200 and full_clear_opactdisp_id='ABRT' and
event_id in ('DNR','TOR','MCL','PFA','PFT') then 'Auto_Abort'
when priority<200 and full_clear_opactdisp_id='ABRT' and
event_id in ('ABD','ABP','ADT','AIA','AIS','AM1','AM2','AM3','AMC','AMDF1','BAC','VVF')
then 'AMD_AA'
else 'Manually_worked' END, 'alarms'=count(distinct alarminc_no)
INTO #counts
FROM #alarm_mins am (nolock)
GROUP BY convert(char(8),gc_date,1),
case when priority=200 then 'Priority_200'
when priority<200 and full_clear_opactdisp_id='ABRT' and
event_id in ('DNR','TOR','MCL','PFA','PFT') then 'Auto_Abort'
when priority<200 and full_clear_opactdisp_id='ABRT' and
event_id in ('ABD','ABP','ADT','AIA','AIS','AM1','AM2','AM3','AMC','AMDF1','BAC','VVF')
then 'AMD_AA'
else 'Manually_worked' END
UPDATE #alarm_template SET auto_abort=c.alarms
FROM #counts c
WHERE #alarm_template.day=c.day and type='Auto_Abort'
UPDATE #alarm_template SET Manually_worked=c.alarms
FROM #counts c
WHERE #alarm_template.day=c.day and type='Manually_worked'
UPDATE #alarm_template SET priority_200=c.alarms
FROM #counts c
WHERE #alarm_template.day=c.day and type='Priority_200'
update #alarm_template set total=manually_worked+priority_200+auto_abort
/* Final Data output */
SELECT at.Day,
at.manually_worked,
at.priority_200,
at.auto_abort,
at.total,
'mw_tot'=(SELECT sum(manually_worked) from #alarm_template),
'p200_tot'=(SELECT sum(priority_200) from #alarm_template),
'aa_tot'=(SELECT sum(auto_abort) from #alarm_template),
'Report_Total'=(SELECT sum(total) from #alarm_template),
'Start_date'=@start,
'Finish_date'=@finish,
'Feds'=case when @fed_flag in ('f','F') then 'Federal Only' else 'All Customers' END,
'DST'=case when @dst_flag in ('y','Y') then 'Daylight Savings' else 'Standard' END
FROM #alarm_template at
ORDER BY at.Day
DROP table #alarm_mins, #alarm_template, #counts
SET nocount OFF
go
GRANT EXECUTE ON rp_alarm_by_day TO public
go