时间戳

首页 > 解决方案 > Fill values in SQL Server

sql - Fill values in SQL Server

问题描述

I have a table projects, and a log of changes to those project status columns: OldStatus and NewStatus.

I would like to calculate the status of a project for the periods below:

 Project_ID Period  OldStatus   NewStatus
    123     201705  NULL        NULL
    123     201706  NULL        NULL
    123     201707  A           D
    123     201708  NULL        NULL
    123     201709  NULL        NULL
    123     201710  NULL        NULL
    123     201711  D           A
    123     201712  NULL        NULL
    123     201713  NULL        NULL

And my ideal output:

Project_ID  Period  Status
    123     201705  A
    123     201706  A
    123     201707  D
    123     201708  D
    123     201709  D
    123     201710  D
    123     201711  A
    123     201712  A
    123     201713  A

(If no prior status found, it is A)

Test data:

SELECT '123' AS [Project_ID], 201603 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201604 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201605 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201606 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201607 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201608 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201609 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201610 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201611 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201612 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201613 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201701 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201702 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201703 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201704 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201705 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201706 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201707 AS [Period], 'A' AS [OldStatus], 'D' AS [NewStatus]
UNION ALL 
SELECT '123' AS [Project_ID], 201708 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201709 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201710 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201711 AS [Period], 'D' AS [OldStatus], 'A' AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201712 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201713 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201801 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201802 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201803 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201804 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201805 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201806 AS [Period], 'A' AS [OldStatus], 'D' AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201807 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201808 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201809 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201810 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201811 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201812 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201813 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201814 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201815 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201816 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201901 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201902 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201903 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '123' AS [Project_ID], 201904 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL
SELECT '321' AS [Project_ID], 201705 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus] 
UNION ALL 
SELECT '321' AS [Project_ID], 201706 AS [Period], 'A' AS [OldStatus], 'I' AS [NewStatus] 
UNION ALL 
SELECT '321' AS [Project_ID], 201707 AS [Period], NULL AS [OldStatus], NULL AS [NewStatus]

Edit

My final query based on answer by @Gordon Linoff below.

I introduced a second table: project_main, which includes the current project status. This is required in cases where no history is recorded.

SELECT
    p.project_id,
    p.period,
    COALESCE(p.new_status, pprev.new_status, pnext.old_status, crrnt.status) AS status
FROM projects p OUTER APPLY
     (SELECT TOP (1) old_status
      FROM projects p2
      WHERE p2.project_id = p.project_id AND p2.period >= p.period
      AND old_status IS NOT NULL
      ORDER BY p2.period DESC
     ) pnext OUTER APPLY
     (SELECT TOP (1) new_status
      FROM projects p2
      WHERE p2.project_id = p.project_id AND p2.period <= p.period
      AND new_status IS NOT NULL
      ORDER BY p2.period DESC
     ) pprev OUTER APPLY
     (SELECT TOP (1) Status
      FROM project_main p3 -- The current project status is stored here
      WHERE PR.project_id = p3.project_id) crrnt
ORDER BY Period ASC;

标签： sqlsql-serversql-server-2016

解决方案

嗯，您真正想要的是 and 上的选项IGNORE NULLS，但 SQL Server 不支持该选项。LAG()LEAD()

select p.project_id, p.period,
       coalesce(p.new_status, pnext.old_status, pprev.new_status, 'A') as status
from projects p outer apply
     (select top (1) old_status
      from projects p2
      where p2.project_id = p.project_id and
            p2.period >= p.period and
            p2.old_status is not null
      order by p2.period asc
     ) pnext outer apply
     (select top (1) new_status
      from projects p2
      where p2.project_id = p.project_id and
            p2.period <= p.period and
            p2.new_status is not null
      order by p2.period desc
     ) pprev;

推荐阅读