将查询中的空值替换为以前的值

Question

我正在通过SSMS创建一个查询，使用SQL Server 2012。

我的问题的一部分是找到合伙人的主管。我的问题是，对于包含主管姓名的表，它不会创建新的每日记录。因为那天没有记录，所以我对主管的查询显示为NULL。

如何使用表中向其报告的最后一位主管填充这些空值？

主管表

MeasureDate    Associate            Supervisor
2019-09-02     Goodman, Michelle    Moody, Robin
2019-09-01     Goodman, Michelle    Moody, Robin
2019-08-20     Goodman, Michelle    Henry, Jack
2019-08-19     Goodman, Michelle    Henry, Jack

当前查询输出

Date        Supervisor     Associate
2019-09-01  Moody, Robin   Goodman, Michelle
2019-08-21  NULL           Goodman, Michelle
2019-08-20  Henry, Jack    Goodman, Michelle

改进的查询输出

Date        Supervisor     Associate
2019-09-01  Moody, Robin   Goodman, Michelle
2019-08-21  Henry, Jack    Goodman, Michelle
2019-08-20  Henry, Jack    Goodman, Michelle

当前查询

SELECT 
z.Date,
z.Supervisor,
z.Associate,
CAST('XXX' AS char) AS [Group],
z.Task,
Sum(z.Minutes) as Minutes,
Sum(z.Count) as Cases

FROM
(
SELECT
a.DS_LOAD_DT AS Date,
c.CASE_TASK_TYPE_NM AS Task,
b.AssociateName AS Associate,
d.Supervisor,
(CONVERT(decimal,a.DURATIONSEC)/60) AS Minutes,
COUNT(*) AS Count

FROM DW_RawData_XXXDetail a
LEFT JOIN DW_Dim_AssociateMapping b ON a.XXX_EMP_ID = b.XXX_ID AND a.DS_LOAD_DT = b.ScheduleDate
LEFT JOIN DW_Dim_XXX_Case_Types c ON left(a.MSG_TYPE_CD, patindex('%[0-9][^0-9]%', a.MSG_TYPE_CD)) = 
c.CASE_TASK_TYPE_ID
LEFT JOIN OperationScoreCard_Daily_Associates d ON b.AssociateName = d.Associate AND b.ScheduleDate = 
d.MeasureDate

WHERE b.MUID = '400'
AND a.DS_LOAD_DT >= '2019-08-01'

GROUP BY
a.DS_LOAD_DT,
c.CASE_TASK_TYPE_NM,
b.AssociateName,
d.Supervisor,
(CONVERT(decimal,a.DURATIONSEC)/60)
) z

GROUP BY
z.Date,
z.Task,
z.Associate,
z.Supervisor

Answer 1

select top 1 '2019-08-01' as Date, Supervisor, Associate
from DW_RawData_XXXDetail --plus any joins you need
where Date <= '2019-08-01'
group by MAX(Date), Supervisor, Associate

group by为您提供每个主管/助理组合的最新日期，因此您可以将该日期与您需要的任何目标日期进行比较。理想情况下，用变量替换硬编码的日期。

Answer 2

您可以使用lag函数通过使用partitioning over associate来获取supervisor的前值。有关更多信息，请参阅：https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15

下面是一个示例：

    CREATE TABLE LogicalConnection (
  `cpId` VARCHAR(11),
  `sequenceNumber` VARCHAR(11)
);

INSERT INTO LogicalConnection
  (`cpId`, `sequenceNumber`)
VALUES
  ('1', '10'),
  ('1', NULL),
  ('2', '20');

    SELECT t.cpId, 
    case when t.sequenceNumber is null then lag(t.sequenceNumber) over (partition by t.cpId) else t.sequenceNumber end
from LogicalConnection t