sql视图语句之项目完成进度

今天帮小伙伴写个语句，需求很简单，利用泛微数据中心的饼图展示项目完成、未完成比例。感觉挺有意思的，记录一下。

需求

Microsoft SQL Server表名为uf_projectinfo，字段有id、项目编码proCode、计划结束日期planenddate、实际结束日期trueenddate。

创建了一个视图，根据项目的计划结束日期和实际结束日期的情况，统计了总项目数量、按期完成数量和超期完成数量。

使用了CASE语句进行逻辑判断。根据要求，对每个项目的计划结束日期和实际结束日期进行比较，并根据不同情况进行分类统计：

• 如果计划日期大于今天，并且实际日期为空，则不算在计数逻辑之中（不做统计）。
• 如果计划日期大于今天，并且实际日期小于等于计划日期，则算按期完成。
• 如果计划日期小于等于今天，并且实际日期小于等于计划日期，则算正常完成。
• 如果计划日期小于等于今天，并且实际日期大于计划日期，则算超期完成。

通过对不同情况进行分类统计，最终得到了总项目数量、按期完成数量和超期完成数量的结果。

结果集1

结果集是有效项目总数量、已完成数量、未完成数量

SELECT  
    COUNT(*) AS total_projects,
    SUM(CASE 
            WHEN planenddate > GETDATE() AND trueenddate IS NULL THEN 0
            WHEN planenddate > GETDATE() AND trueenddate <= planenddate THEN 1
            WHEN planenddate <= GETDATE() AND trueenddate <= planenddate THEN 1
            WHEN planenddate <= GETDATE() AND trueenddate > planenddate THEN 1
            ELSE 0
        END) AS on_time_projects,
    SUM(CASE 
            WHEN planenddate > GETDATE() AND trueenddate IS NULL THEN 0
            WHEN planenddate > GETDATE() AND trueenddate > planenddate THEN 1
            WHEN planenddate <= GETDATE() AND trueenddate > planenddate THEN 1
            ELSE 0
        END) AS late_projects
FROM uf_projectinfo;

结果集2

结果集会显示每一个项目的执行状态

SELECT
    id,
    proCode,
    planenddate,
    trueenddate,
    CASE
        WHEN planenddate > GETDATE() AND trueenddate IS NULL THEN '未完成'
        WHEN planenddate > GETDATE() AND trueenddate <= planenddate THEN '按期完成'
        WHEN planenddate <= GETDATE() AND trueenddate <= planenddate THEN '正常完成'
        WHEN planenddate <= GETDATE() AND trueenddate > planenddate THEN '超期完成'
    END AS completion_status
FROM uf_projectinfo;

结果集3

结果集只会显示有效的项目完成进度

SELECT
    id,
    proCode,
    planenddate,
    trueenddate,
    CASE
        WHEN planenddate > GETDATE() AND trueenddate IS NULL THEN '未完成'
        WHEN planenddate > GETDATE() AND trueenddate <= planenddate THEN '按期完成'
        WHEN planenddate <= GETDATE() AND trueenddate <= planenddate THEN '正常完成'
        WHEN planenddate <= GETDATE() AND trueenddate > planenddate THEN '超期完成'
    END AS completion_status
FROM uf_projectinfo
WHERE CASE
        WHEN planenddate > GETDATE() AND trueenddate IS NULL THEN '未完成'
        WHEN planenddate > GETDATE() AND trueenddate <= planenddate THEN '按期完成'
        WHEN planenddate <= GETDATE() AND trueenddate <= planenddate THEN '正常完成'
        WHEN planenddate <= GETDATE() AND trueenddate > planenddate THEN '超期完成'
    END IS NOT NULL;

总结

很多时候，底层数据很简单，有趣的是不同的视角展示不同的报表需求。