---
title: "How to modify reports to exclude holiday days."
slug: "how-to-modify-reports-to-exclude-holiday-days"
updated: 2024-04-18T13:54:06Z
published: 2024-04-18T13:54:06Z
canonical: "kb.pharos.com/how-to-modify-reports-to-exclude-holiday-days"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://kb.pharos.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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)

```plaintext
Modify SP rpt_computer_hourly_stats
```
2. Add an additional column to the #hourly_detail temporary table :

```plaintext
--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:

```plaintext
--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:

```plaintext
--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
```

## Related

- [HOW TO: Diagnose 401.x HTTP errors on IIS](/how-to-diagnose-401x-http-errors-on-iis.md)
- [How to create custom reports.](/how-to-create-custom-reports.md)
- [Pharos Support Hours and Contact Information](/pharos-support-hours-and-contact-information.md)
- [How to modify the license key name and site code in the Pharos database](/how-to-modify-the-license-key-name-and-site-code-in-the-pharos-database.md)