如何用SQL Server查询转换Primavera P6日历数据为可读格式？

Primavera P6的日历数据通常存储在CALENDAR表的CALENDAR_DATA字段中，采用嵌套字符串格式。以下是通用的SQL查询方案，可将其拆分并生成你需要的两个可读表。

1. 生成DaysOfWeek表（每周工作日/非工作日及工时） #

该表从日历的工作日模板块提取数据，拆分出每周各天的工作状态和工时：

-- 创建并填充DaysOfWeek表
WITH CalendarCTE AS (
    SELECT
        CALENDAR_ID,
        -- 提取工作日模板子串：定位Weekday:到Exception:之间的内容
        SUBSTRING(CALENDAR_DATA, 
                  CHARINDEX('Weekday:', CALENDAR_DATA) + 8,
                  CHARINDEX('Exception:', CALENDAR_DATA) - (CHARINDEX('Weekday:', CALENDAR_DATA) + 8)
                 ) AS WeekdayTemplate
    FROM CALENDAR
    WHERE CALENDAR_DATA LIKE '%Weekday:%' -- 过滤包含工作日模板的日历
),
SplitWeekdays AS (
    SELECT
        CALENDAR_ID,
        value AS WeekdayEntry
    FROM CalendarCTE
    CROSS APPLY STRING_SPLIT(WeekdayTemplate, ';')
    WHERE value <> '' -- 剔除空条目
)
SELECT
    CALENDAR_ID,
    -- 转换工作日编号为名称（注意：P6中1=周日、2=周一...7=周六，可根据实际版本调整）
    CASE CAST(SUBSTRING(WeekdayEntry, 1, CHARINDEX(',', WeekdayEntry)-1) AS INT)
        WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday'
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
    END AS DayOfWeek,
    -- 标记工作日/非工作日
    CASE CAST(SUBSTRING(WeekdayEntry, CHARINDEX(',', WeekdayEntry)+1, CHARINDEX(',', WeekdayEntry, CHARINDEX(',', WeekdayEntry)+1)-CHARINDEX(',', WeekdayEntry)-1) AS INT)
        WHEN 0 THEN 'Non-Working'
        WHEN 1 THEN 'Working'
    END AS DayType,
    -- 计算当日工时（结束时间-开始时间）
    CAST(
        CAST(SUBSTRING(WeekdayEntry, CHARINDEX(',', WeekdayEntry, CHARINDEX(',', WeekdayEntry)+1)+1, CHARINDEX(',', WeekdayEntry, CHARINDEX(',', WeekdayEntry, CHARINDEX(',', WeekdayEntry)+1)+1)-CHARINDEX(',', WeekdayEntry, CHARINDEX(',', WeekdayEntry)+1)-1) AS INT)
        - CAST(SUBSTRING(WeekdayEntry, CHARINDEX(',', WeekdayEntry)+1, CHARINDEX(',', WeekdayEntry, CHARINDEX(',', WeekdayEntry)+1)-CHARINDEX(',', WeekdayEntry)-1) AS INT)
    AS DECIMAL(4,2)) AS WorkHours
INTO DaysOfWeek
FROM SplitWeekdays;

2. 生成Exceptions_dates表（例外日期及状态） #

该表从日历的例外日期块提取数据，拆分出节假日、特殊工作日等信息：

-- 创建并填充Exceptions_dates表
WITH CalendarCTE AS (
    SELECT
        CALENDAR_ID,
        -- 提取例外日期子串：定位Exception:到字符串末尾的内容
        SUBSTRING(CALENDAR_DATA, 
                  CHARINDEX('Exception:', CALENDAR_DATA) + 10,
                  LEN(CALENDAR_DATA) - (CHARINDEX('Exception:', CALENDAR_DATA) + 10)
                 ) AS ExceptionTemplate
    FROM CALENDAR
    WHERE CALENDAR_DATA LIKE '%Exception:%' -- 过滤包含例外日期的日历
),
SplitExceptions AS (
    SELECT
        CALENDAR_ID,
        value AS ExceptionEntry
    FROM CalendarCTE
    CROSS APPLY STRING_SPLIT(ExceptionTemplate, ';')
    WHERE value <> '' -- 剔除空条目
)
SELECT
    CALENDAR_ID,
    -- 转换为标准日期格式
    DATEFROMPARTS(
        CAST(SUBSTRING(ExceptionEntry, 1, CHARINDEX(',', ExceptionEntry)-1) AS INT),
        CAST(SUBSTRING(ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1)-CHARINDEX(',', ExceptionEntry)-1) AS INT),
        CAST(SUBSTRING(ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1)+1, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1)+1)-CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1)-1) AS INT)
    ) AS ExceptionDate,
    -- 标记例外类型
    CASE CAST(SUBSTRING(ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1)+1)+1, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1)+1)+1)-CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1)+1)-1) AS INT)
        WHEN 0 THEN 'Holiday/Non-Working'
        WHEN 1 THEN 'Special Working Day'
    END AS ExceptionType,
    -- 计算例外日工时（仅特殊工作日有效）
    CASE CAST(SUBSTRING(ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1)+1)+1, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1)+1)+1)-CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1)+1)-1) AS INT)
        WHEN 1 THEN 
            CAST(
                CAST(SUBSTRING(ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1)+1)+1)+1, LEN(ExceptionEntry)-CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1)+1)+1)) AS INT)
                - CAST(SUBSTRING(ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1)+1)+1, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1)+1)+1)-CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry, CHARINDEX(',', ExceptionEntry)+1)+1)-1) AS INT)
            AS DECIMAL(4,2))
        ELSE 0
    END AS WorkHours
INTO Exceptions_dates
FROM SplitExceptions;

注意事项 #

• P6不同版本的CALENDAR_DATA格式可能略有差异，若字段顺序或分隔符不同，需调整CHARINDEX的定位逻辑。
• 若日历无例外日期，Exception:块不存在，需通过WHERE CALENDAR_DATA LIKE '%Exception:%'过滤避免报错。
• 工作日编号的映射需根据实际P6配置调整（部分版本可能1=周一、7=周日）。
• 上述代码针对单班次基础日历，若需处理多班次日历，需扩展拆分逻辑以提取班次时段数据。

内容的提问来源于stack exchange，提问作者Sebin Thomas