Have you ever deleted outdated data from your PivotTable source, only to find that the old values still appear in the filter dropdowns? This happens because Excel caches old items by default — even if they’re no longer present in the data source.
Sub ClearPivotCaches()
Dim pc As PivotCache
For Each pc In ThisWorkbook.PivotCaches
pc.MissingItemsLimit = xlMissingItemsNone
Next pc
ThisWorkbook.RefreshAll
End Sub
💡 To run this code:
💡 執行這段程式碼的方法如下：
Press Alt + F11 to open the VBA editor.
Click Insert → Module.
Paste the code above.
Press F5 to run it.
Close the VBA editor.
按下 Alt + F11 開啟 VBA 編輯器。
點選 插入 → 模組。
貼上上述程式碼。
按 F5 執行。
關閉 VBA 編輯器。
This will clear all outdated items from your PivotTable filters and refresh them automatically.
這樣會自動清除所有樞紐分析表中的舊篩選項目，並重新整理。
🛡️ Tip: For future PivotTables, remember to set the “Number of items to retain per field” to “None” when you first create them to avoid this issue.
Creating a permanent calendar table (also known as a date dimension) is a best practice for production systems that involve reporting, scheduling, or time-based analytics. It simplifies logic, improves performance, and makes queries more readable.
🧱 What Is a Calendar Table?
A calendar table is a user-defined table with one row per date and columns for various date-related attributes.
Example Schema
CREATE TABLE dbo.Calendar (
CalendarDate DATE PRIMARY KEY,
Year INT,
Month INT,
Day INT,
Weekday INT, -- 1=Sunday, 7=Saturday
DayOfWeekName VARCHAR(10),
IsWeekend BIT,
IsHoliday BIT,
YearMonth CHAR(6), -- e.g. '202504'
Quarter INT,
DayOfYear INT,
WeekOfYear INT,
FiscalYear INT,
FiscalPeriod INT
-- Add more fields as needed
);
✅ Benefits
Feature
Benefit
🧠 Readability
Easier to understand and maintain date-related logic
🕵️♂️ Simplicity
Avoids complex DATEPART or CASE expressions
🚀 Performance
Precomputed attributes save CPU and memory during query time
📅 Flexibility
Add columns like fiscal period, holiday flag, ISO week, etc.
📊 BI Integration
Works seamlessly with Power BI, SSAS, Tableau, and other tools
🧠 Example Use Case
SELECT
c.YearMonth,
COUNT(*) AS WorkdayCount
FROM dbo.Calendar c
WHERE c.CalendarDate BETWEEN '2024-07-01' AND '2025-06-30'
AND c.IsWeekend = 0
AND c.IsHoliday = 0
GROUP BY c.YearMonth
ORDER BY c.YearMonth;
🔧 How to Populate It
Here’s how to generate dates and populate the calendar:
DECLARE @StartDate DATE = '2020-01-01';
DECLARE @EndDate DATE = '2030-12-31';
WITH DateList AS (
SELECT @StartDate AS CalendarDate
UNION ALL
SELECT DATEADD(DAY, 1, CalendarDate)
FROM DateList
WHERE CalendarDate < @EndDate
)
INSERT INTO dbo.Calendar (
CalendarDate, Year, Month, Day, Weekday, DayOfWeekName,
IsWeekend, YearMonth, Quarter, DayOfYear, WeekOfYear
)
SELECT
CalendarDate,
YEAR(CalendarDate),
MONTH(CalendarDate),
DAY(CalendarDate),
DATEPART(WEEKDAY, CalendarDate),
DATENAME(WEEKDAY, CalendarDate),
CASE WHEN DATENAME(WEEKDAY, CalendarDate) IN ('Saturday', 'Sunday') THEN 1 ELSE 0 END,
FORMAT(CalendarDate, 'yyyyMM'),
DATEPART(QUARTER, CalendarDate),
DATEPART(DAYOFYEAR, CalendarDate),
DATEPART(WEEK, CalendarDate)
FROM DateList
OPTION (MAXRECURSION 0);
🧩 Add Holiday Info
Assuming you maintain a dbo.Holidays table:
UPDATE c
SET c.IsHoliday = 1
FROM dbo.Calendar c
JOIN dbo.Holidays h ON c.CalendarDate = h.HolidayDate;
🏁 Final Thoughts
Creating and using a permanent calendar table ensures:
🗓️ Two Approaches to Generating Calendar Dates in SQL Server
When working with time-based reporting or scheduling systems, it’s often necessary to generate a list of calendar dates. Whether you’re counting workdays, building timelines, or joining with other datasets, having a calendar table—or generating dates on the fly—is crucial. Let’s explore two popular methods of generating calendar dates in SQL Server.
✅ 1. Using a Recursive CTE (Common Table Expression)
A recursive CTE can dynamically generate a list of dates without relying on any external table:
WITH Calendar AS (
SELECT CAST('2024-07-01' AS DATE) AS DateValue
UNION ALL
SELECT DATEADD(DAY, 1, DateValue)
FROM Calendar
WHERE DateValue < '2025-06-30'
)
SELECT * FROM Calendar
OPTION (MAXRECURSION 0);
🔍 Pros:
No dependency on system tables.
Simple and self-contained.
⚠️ Cons:
Slightly less performant for very large ranges.
MAXRECURSION default is 100; you must override it for larger date ranges.
✅ 2. Using master.dbo.spt_values (or a numbers/tally table)
This system table contains thousands of rows and can be repurposed to generate sequential numbers (and thus, dates):
SELECT DATEADD(DAY, number, '2024-07-01') AS DateValue
FROM master.dbo.spt_values
WHERE type = 'P'
AND number BETWEEN 0 AND DATEDIFF(DAY, '2024-07-01', '2025-06-30');
🔍 Pros:
Faster performance for large sets.
No recursion required.
⚠️ Cons:
Relies on an undocumented system table (spt_values), which may be unsupported or unavailable in some SQL Server environments (especially Azure SQL DB).
Less self-explanatory for beginners.
🧠 When to Use Which?
Use Case
Recommended Method
Small date ranges (e.g. 1 year)
Recursive CTE
Large date ranges (many years)
Tally table or spt_values
Need portability and readability
Recursive CTE
Need performance
Tally/spt_values
🏁 Conclusion
Both approaches have their place. The recursive CTE is elegant and portable, great for small to medium needs. If you’re working with large datasets or care about performance, using spt_values (or better, your own numbers table) is a strong alternative.
-- Suggested: Create your own Numbers table for safety
CREATE TABLE dbo.Numbers (n INT PRIMARY KEY);
WITH E1(n) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(n) AS (
SELECT 1 FROM E1 a CROSS JOIN E1 b -- 16 rows
),
E4(n) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n FROM E2 a CROSS JOIN E2 b
)
INSERT INTO dbo.Numbers(n)
SELECT n FROM E4 WHERE n < 10000;
👉 Tip: For production systems, consider creating a permanent Calendar table with pre-generated dates and useful attributes (week number, holiday flag, etc.) to simplify your queries.
更換VGA連接線=>更換螢幕=>更換轉接頭=>更新BIOS與intel顯卡驅動=>安裝AVAST防毒軟體=>移除最近安裝應用軟體(Microsoft Power Automate)=>進入intel顯卡設定程式任意調整設定(非相關設定)=>恢復正常
問題推斷，使用者反應自從另一位同事幫忙安裝Microsoft Power Automate後，桌面背景不定時會自行替換，檢查發現該人員安裝MPA時有微軟帳號登入，並有開啟桌面同步功能，關閉桌面同步後，桌面背景就不會自行變換，推斷此同步設定可能會影響開機時螢幕顯示相關設定，進入顯卡設定任意調整後可覆蓋此問題，因此可解決此異常，後續持續觀察。
SELECT
t1.TransactionType,
t1.TransactionDate,
YEAR(t1.TransactionDate) AS TransactionYear,
MONTH(t1.TransactionDate) AS TransactionMonth,
LEAD(t1.TransactionDate, 1, DATEADD(day, 1, EOMONTH(t1.TransactionDate)))
OVER (PARTITION BY t1.TransactionType,YEAR(t1.TransactionDate),MONTH(t1.TransactionDate) ORDER BY t1.TransactionDate) AS NextTransactionDate,
DATEDIFF(day, t1.TransactionDate,
LEAD(t1.TransactionDate, 1, DATEADD(day, 1, EOMONTH(t1.TransactionDate)))
OVER (PARTITION BY t1.TransactionType,YEAR(t1.TransactionDate),MONTH(t1.TransactionDate) ORDER BY t1.TransactionDate)) AS DifferenceInDays
FROM Transactions t1
GROUP BY
t1.TransactionType,
t1.TransactionDate,
YEAR(t1.TransactionDate),
MONTH(t1.TransactionDate)
ORDER BY
t1.TransactionType,
t1.TransactionDate;
LEAD (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause ) provides access to a row at a specified physical offset which follows the current row. It allows you to query more than one row in a SQL statement without having to join the table to itself
The EOMONTH function in SQL is used to get the last day of the month for a specified date
