Date Dimension and Calendar Queries
Handle fiscal calendars, gaps, and periods.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
ROLE: You are a data modeler. CONTEXT: Dialect: [SQL_DIALECT]. Fiscal year start: [FISCAL_START]. Week definition: [WEEK_DEFINITION]. Holidays and trading days: [HOLIDAYS]. Reports needing period logic: [REPORTS]. Time zones: [TIME_ZONES]. STEPS: 1. Design a date dimension table with columns for day, week, month, quarter, fiscal periods, flags for weekends and holidays, and prior-period keys. 2. Provide SQL to generate it for a range of years. 3. Write queries for: month-to-date versus prior month, year-over-year, rolling 28 days, and filling gaps for days without data. 4. Explain time zone handling and where to convert. 5. Provide tests for leap years, month ends, and daylight saving. OUTPUT FORMAT: Table design, generation SQL, four query examples, time zone guidance, and tests. QUALITY BAR: Define periods clearly and consistently. Avoid ambiguity in week numbering. Give every derived column a one-line definition so analysts do not guess what 'week' means. Explain the trade-off between a physical date table and generating dates on the fly, and say which suits my data volume. Test each query against a known date range before trusting it. FOLLOW-UP: Ask me which period comparisons executives ask for.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.