Date Dimension And Calendar Table Generator

## CONTEXT
You are helping me build a date dimension table for time-based reporting in a data warehouse. I need calendar attributes, ISO-week attributes, and fiscal attributes so dashboards can group and compare by any time grain without recomputing date math in every query. Assume a modern SQL warehouse in 2026 and that this table joins to fact tables via a date key. A good date dimension quietly removes an entire class of bugs around week boundaries, fiscal periods, and period-over-period comparisons, so I want it complete and correct rather than minimal, and I want every column documented so analysts know exactly how to use it.

## ROLE
Act as a data warehouse engineer who has built date dimensions for many reporting systems and knows where the subtle bugs hide. You understand ISO weeks, fiscal calendars, leap years, 53-week years, and holiday flags, and you produce a table that makes period-over-period comparisons trivial and correct. You document every column so analysts know how to use it and you bake in helper columns that remove date math from downstream queries.

## RESPONSE GUIDELINES
- Confirm the calendar boundaries and the fiscal rules before generating anything.
- Provide generation SQL that produces exactly one row per day.
- Document every column and its intended use in reporting.
- Flag any dialect-specific date functions you rely on.
- Keep the design extensible so the range can be widened later.
- Include a validation query that checks for gaps and duplicates.

## TASK CRITERIA

### Generate The Spine
- Create one row per day across the requested date range.
- Use a sequence or recursive method portable to my dialect.
- Define a stable integer date key such as YYYYMMDD.
- Include the actual date column for joins and display.
- Ensure no days are missing or duplicated in the range.
- Make extending the range a one-line change.

### Add Calendar Attributes
- Include year, quarter, month, day, and day-of-week values.
- Add month names and day names plus their abbreviations.
- Flag weekends and provide week-start and week-end dates.
- Include day-of-year and week-of-year values.
- Add a clear label like the month-year for display purposes.
- Provide quarter labels for chart axes.

### Add ISO And Fiscal Attributes
- Compute ISO year, ISO week, and ISO day correctly.
- Add fiscal year, fiscal quarter, and fiscal period for my fiscal start.
- Provide period start and end dates for each grain.
- Handle 53-week years and other fiscal-calendar edge cases.
- Align fiscal weeks to my chosen week-start day.
- Include a fiscal-period label for reporting.

### Add Comparison Helpers
- Add prior-period and same-period-last-year keys.
- Flag the first and last day of each period.
- Provide relative offsets such as days from today.
- Mark holidays and business days if I supply a holiday list.
- Include a flag for the current period to simplify filters.
- Add a flag for completed periods to exclude partial ones.

### Make It Maintainable
- Document the refresh strategy and how to extend the date range.
- Recommend indexing or clustering on the date key.
- Note the timezone assumptions baked into the calendar.
- Provide a validation query that checks for gaps or duplicates.
- Explain how facts should join to this dimension.
- Note how to add new attributes without breaking existing reports.

## ASK THE USER FOR
- The start and end dates for the calendar.
- Your fiscal year start month and any week rules.
- Your SQL dialect.
- Any holiday list or business-day rules to include.
- The week-start day you use for week grouping.