How to modify reports to exclude holiday days.

Problem:

How to modify reports to exclude holiday days.

Solution:

You need to modify reports, so that stored procedure calculates the Holidays and does not include such days in the report.

1. Set up the table to hold the date of the closed days (table "days_closed" with "column closed_date" datatype = datetime)

   Modify SP rpt_computer_hourly_stats

2. Add an additional column to the #hourly_detail temporary table :

   --temp table for hourly totals for each day
   
   create table #hourly_detail
   
   ( day_of_year datetime not null,
   
   computer_type_id int not null,
   
   day_of_week int not null,
   
   hour_of_day int not null,
   
   minutes_used int not null,
   
   was_closed int not null )

3. Query to populate #hourly_detail becomes:

   --populate table
   
   insert into #hourly_detail
   
   select
   
   ct.day_of_year,
   
   ct.computer_type_id,
   
   w.day_of_week,
   
   w.hour_of_day,
   
   sum(case
   
   when ct.start_min >= w.start_min and ct.end_min < w.end_min then ct.end_min - ct.start_min
   
   when ct.start_min >= w.start_min and ct.end_min >= w.end_min then w.end_min - ct.start_min
   
   when ct.start_min < w.start_min and ct.end_min < w.end_min then ct.end_min - w.start_min
   
   else w.end_min - w.start_min
   
   end),
   
   case
   
   when max(isnull(closed_date,0)) else 0
   
   end
   
   from #week w
   
   join #cg_transactions ct on ct.day_of_week = w.day_of_week
   
   and ( (ct.start_min >= w.start_min and ct.start_min < w.end_min)
   
   or (ct.end_min >= w.start_min and ct.end_min < w.end_min)
   
   or (w.start_min >= ct.start_min and w.start_min < ct.end_min))
   
   left join days_closed dc on ct.day_of_year = convert(datetime, convert(varchar, dc.closed_date, 112))
   
   group by
   
   ct.computer_type_id,
   
   ct.day_of_year,
   
   w.day_of_week,
   
   w.hour_of_day

4. Query that calculates the average utilization and populates hourly_summary becomes:

   --populate table
   
   insert into #hourly_summary
   
   select
   
   hd.day_of_week,
   
   hd.computer_type_id,
   
   hd.hour_of_day,
   
   case
   
   when sum(hd.was_closed) >= @num_weeks then 0
   
   else convert(real, sum(hd.minutes_used))  100 / ((@num_weeks -sum(hd.was_closed)) ct.num_computers * 60)
   
   end,
   
   convert(real, max(hd.minutes_used))  100 / (ct.num_computers  60),
   
   case
   
   when count(*) < @num_weeks then 0
   
   else convert(real, min(hd.minutes_used))  100 / (ct.num_computers  60)
   
   end
   
   from #hourly_detail hd
   
   join #computer_types ct on ct.computer_type_id = hd.computer_type_id
   
   group by
   
   hd.day_of_week,
   
   hd.computer_type_id,
   
   hd.hour_of_day,
   
   ct.num_computers