<!-- https://learnsql.cpetoday.com/analyze/maps-and-fiscal -->
# Maps and fiscal calendars

Seven of the sample databases share the same two building blocks: **addresses with coordinates** for mapping, and **a fiscal calendar** for charting by fiscal period. They behave the same way in `cpafirm`, `nonprofit`, `pqlab` (its tables are prefixed `core_`), `smarthome`, `bigbear`, `retailwh` and `mfgorders`, so a map or a fiscal-year chart you build for one works for the others.

## Addresses and coordinates

Every table that holds an address has the same columns:

| Column | Meaning |
|---|---|
| `address_line1`, `city`, `state`, `postal_code` | The address as entered |
| `county`, `country` | Added from the city (county names, `US`) |
| `latitude`, `longitude` | WGS84 decimal degrees, six decimals |
| `geocode_quality` | How the point was found: `rooftop`, `street`, `postal_centroid` or `city_centroid` (`bigbear` adds `gps` for mobile-app reports, `address_match`, `neighborhood_centroid` and `failed`) |
| `full_address` | The address in one line, for geocoding tools and map labels |

> **Watch out.** Where the address is missing the coordinates are null. Where only the city is known you get the city centroid.

| Database | Tables with coordinates |
|---|---|
| `cpafirm` | `clients`, `offices`, `client_visits` (origin and destination of each trip) |
| `nonprofit` | `constituents` (about 18,500 donors, volunteers and prospects), `funders`, `vendors`, `program_sites`, `mobile_market_stops`, event venues |
| `pqlab` | `core_customers`, `core_stores`, `core_suppliers` (HQ), `core_shipments` (destination and distance from the warehouse), plus the `lab_geo_*` practice tables |
| `smarthome` | `homes`, `points_of_interest`, `geofences`, `trips`, `trip_points`, and an indoor floor plan in feet |
| `bigbear` | `parcels`, `facilities`, `assets`, `streets` (segment endpoints), `service_requests`, `cad_calls`, `permits`, `inspections`, `code_cases`, `work_orders`, `capital_projects`, `special_events`, `businesses`, `vendors`, `property_owners` |
| `retailwh` | `dim_store`, `dim_customer` (latitude and longitude), `dim_region` (a rough boundary polygon as GeoJSON) |
| `mfgorders` | `dim_customer`, `dim_plant` |

Postal codes in these databases are fictitious (a real city prefix with random digits). The point for a postal code is a synthetic centroid placed near the city, so use `postal_centroid` rows for rough maps only.

## Reference geography

Each database has the same three reference tables (in `pqlab` they are `core_geo_states`, `core_geo_places` and `core_geo_postal_codes`):

| Table | Holds |
|---|---|
| `geo_states` | The 50 states and DC: name, FIPS code, Census region and division, centroid, capital |
| `geo_places` | Each city in use with county, centroid, population, time zone and metro area |
| `geo_postal_codes` | A centroid for every postal code in the data |

Join to them on `state_code`, on city and state, or on `postal_code`. Use `geo_states` for filled (shaded-area) maps and `geo_places` for bubble maps.

## Distance on a map

The same great-circle (haversine) formula works on SQL Server and MariaDB. This finds the five clients farthest from the office that serves them in `cpafirm`:

```sql
SELECT c.client_name, c.city, o.office_name,
       ROUND(3958.8 * 2 * ASIN(SQRT(POWER(SIN(RADIANS(c.latitude - o.latitude) / 2), 2)
             + COS(RADIANS(o.latitude)) * COS(RADIANS(c.latitude)) * POWER(SIN(RADIANS(c.longitude - o.longitude) / 2), 2))), 1) AS miles_from_office
FROM clients c
JOIN offices o ON o.office_id = c.office_id
WHERE c.latitude IS NOT NULL
ORDER BY miles_from_office DESC
OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;
```

Out-of-state clients (Arizona, for example) are served remotely, which is why they top the list. To build a catchment for the nearest store, use `lab_geo_distance_pairs` in `pqlab`, which already holds the miles and a flag for each customer's nearest store.

## Mapping in Excel and Power BI

1. **Set the data category.** In Power BI select `latitude` and `longitude` and set their data category to Latitude and Longitude. For a state map use `state` with the category State or Province.
2. **Bubble map.** Put `longitude` and `latitude` on the map visual, and size the bubbles by a measure such as donors, billed amount or miles.
3. **Filled map.** Join to `geo_states` and shade by state; use `census_region` for a region rollup.
4. **Routes.** `client_visits`, `trips` and `lab_geo_route_points` have an origin and a destination, or ordered points, which a line layer or a Shape map can draw.
5. **Big maps.** For thousands of points, round the coordinates (for example to one decimal) and group, so the map draws bins rather than every row.
6. **Excel.** Use 3D Maps (Insert, 3D Map) or a Filled Map chart; for a Geography data type, build it from `full_address` or `city` and `state`.

## Fiscal calendars

Every database has a fiscal-calendar table (`fiscal_calendar`, or `core_fiscal_calendar` in `pqlab`) with one row per day, and its main fact tables carry three ready-made columns so you do not need a join to filter by fiscal period:

| Column | Meaning |
|---|---|
| `fiscal_year` | The fiscal year (the naming rule is below) |
| `fiscal_quarter` | 1 to 4 |
| `fiscal_period` | 1 to 12, with 1 the first month of the fiscal year |

The calendar tables add `fiscal_year_label` (such as FY2026), `fiscal_period_name` (such as FY2026-P03), the period and year start and end dates, `fiscal_week`, `fiscal_day_of_year`, and flags for the last day of a period and of a year. Each also has a few extras for its domain.

| Database | Fiscal year | Named by | Extras in the calendar |
|---|---|---|---|
| `cpafirm` | July to June | year it ends | `tax_season` (Busy, Extension, Off) and `tax_deadline` |
| `nonprofit` | July to June | year it ends | `giving_season`, holidays, Giving Tuesday |
| `pqlab` | February to January | year it **starts** | a true 4-5-4 retail calendar (`retail_year`, `retail_period`, `retail_week_of_period`) and shopping seasons |
| `smarthome` | October to September (water year) | year it ends | `utility_season` and the rate plan in force |
| `bigbear` | July to June | year it ends | `budget_phase`, property tax installment dates, `tourism_season`, snow season and holidays |
| `retailwh` | April to March | year it ends | `dim_date` with fiscal year, quarter, period and period name |
| `mfgorders` | November to October | year it ends | `dim_date` with fiscal year, quarter, period and period name |

"Named by the year it ends" means FY2026 is the year ending in 2026 (July 2025 to June 2026 for a July start). `pqlab` names its year by the year it starts, to match its older `core_calendar` table; its `retail_*` columns are the 4-5-4 weeks, which differ from the calendar-month fiscal periods by a few days at the edges.

**Fiscal quarters of billing, with no join:**

```sql
SELECT fiscal_year, fiscal_quarter, COUNT(*) AS invoices, SUM(total_amount) AS billed
FROM invoices
WHERE fiscal_year BETWEEN 2024 AND 2026
GROUP BY fiscal_year, fiscal_quarter
ORDER BY fiscal_year, fiscal_quarter;
```

**Using the extras with a join** (revenue by tax season in `cpafirm`):

```sql
SELECT f.tax_season, COUNT(*) AS invoices, SUM(i.total_amount) AS billed
FROM invoices i
JOIN fiscal_calendar f ON f.calendar_date = i.issue_date
WHERE i.fiscal_year = 2025
GROUP BY f.tax_season
ORDER BY billed DESC;
```

**Giving by season in `nonprofit`:**

```sql
SELECT f.giving_season, COUNT(*) AS gifts, SUM(g.amount) AS raised
FROM gifts g
JOIN fiscal_calendar f ON f.calendar_date = g.gift_date
WHERE g.fiscal_year = 2025
GROUP BY f.giving_season
ORDER BY raised DESC;
```

**The retail calendar in `pqlab`:**

```sql
SELECT f.retail_period, COUNT(*) AS orders, SUM(o.total) AS sales
FROM core_orders o
JOIN core_fiscal_calendar f ON f.calendar_date = o.order_date
WHERE f.retail_year = 2025
GROUP BY f.retail_period
ORDER BY f.retail_period;
```

## Fiscal time in Power BI and Excel

- **Use the calendar as your date table.** Load the fiscal calendar, mark `calendar_date` as the date table, relate it to the fact table's date, and put `fiscal_year_label` and `fiscal_period_name` on slicers and axes.
- **Sort correctly.** Sort `fiscal_period_name` by itself (it sorts as text, FY2026-P01 to P12) or sort a month name by `fiscal_period`, so July comes first for a July year.
- **Year to date.** Pass the year-end date to time intelligence, for example `TOTALYTD([Billed], 'fiscal_calendar'[calendar_date], "6/30")` for a June year end, or `"9/30"` for `smarthome`. See [time intelligence](/analyze/time-intelligence).
- **Map by fiscal period.** Put `fiscal_year` or `fiscal_quarter` on a slicer next to a map, and the bubbles show that period's activity.
- **Compare years.** `fiscal_period` lines up the same month of different fiscal years on one axis.
