有条件的得分

我有一个只显示“捕获”数据的表格 . 例如,在下面的展览中,emp_no 17有2条记录 - 11月和2月(指定的6个月期间,从2017年7月起) . 它没有其他4个月的数据(在6个月的日期范围内,从之前的6个月到当前日期) .

如何在缺失的月份中使用缺省值num_differences为0填充这些缺失的月份(9月,10月,12月)? (例如,在这种情况下,我希望emp_no 17具有以下内容(我可以忽略2018数据 - 仅需要截至2017年12月的数据):

我有以下脚本:

declare @YMN date;
set @YMN = '20171201';

DECLARE @Emp TABLE (

[date] date,

[emp_no] int,
[num_differences] int
);

INSERT INTO @Emp VALUES
('2017-09-14', 17, 1), ('2017-12-01', 17, 1),('2017-12-18', 17, 1),('2017-12-21', 17, 1),

('2017-09-27', 17, 1), ('2017-12-04', 17, 1);

-------------------------------------------------------------------------------------------get missing dates---------------------------------------------------------------------------

    ;WITH cte_Emp_No AS (
    SELECT DISTINCT [emp_no]
    FROM @Emp
    ),
    cte_dates AS (
    SELECT [emp_no], DATEADD(month, -6, DATEADD(dd, -(DAY(dateadd(month, 1, @YMN)) - 1), dateadd(month, 1, @YMN))) AS [date]
    FROM cte_Emp_No
    UNION ALL
    SELECT [emp_no], DATEADD(month, 1, [date]) AS [date]
    FROM cte_dates
    WHERE [date] < dateadd(month, 0, @YMN)
    )

    SELECT DISTINCT ISNULL(e.emp_no, c.emp_no) emp_no, ISNULL(e.date, c.date) date, ISNULL(e.num_differences, 0) num_differences 
    into ##new_table 
    FROM @Emp AS e
    RIGHT JOIN cte_dates AS c ON YEAR(c.date) = YEAR(e.date) AND MONTH(c.date) = MONTH(e.date)

    -----------------------------------------------------------------------------------------------MAIN CTE------------------------------------------------------------------------------
    ;with cte_RawScore as
    (
    select emp_no
       , date YMN
       ,sum(case when datediff(month, convert(datetime, @YMN, 112), date) = 0 then num_differences else 0 end) as thismonth
       ,sum(case when datediff(month, convert(datetime, @YMN, 112), date) between -2 and 0 then num_differences else 0 end) as last3month
       ,sum(case when datediff(month, convert(datetime, @YMN, 112), date) between -5 and 0 then num_differences else 0 end) as last6month
    from ##new_table d
    group by emp_no, date
    )

    select 
       emp_no
       ,YMN
       ,case when last6month  = 0 then 5
     when last3month  = 0 then 4
     when thismonth  = 0 then 3
     when thismonth  <= 3 then 2
    else 1 end RawScore
    from cte_RawScore 
    ORDER BY day(YMN) desc

    drop table ##new_table

我希望这个得分仅适用于2017年7月和之后的6个月 . 即@YMN是存储年月数的变量;根据上述规则,分数适用于201707年的6个月 .

所以201707是1个月,

201708年为2个月等,截至2017年12月

我希望根据下面提到的规则,列出员工及其相关分数 .

那是, :

如果连续6个月(从7月到12月)0差异,则得分为5分;

如果连续3个月(从7月到12月)0差异,则得分为4分;

如果0个差异为1个月(从7月到12月),则得分为3;

如果1个月至3个差异为1个月(从7月到12月),则得分为2;

如果在1个月(从7月到12月)有4个或更多差异,则得分为1 .

我从表中得到了差异的数量,但有些员工在某些月份没有出现;因此,如果他们没有出现在那个特定的月份,我想给他们一个0的差异 .

请协助 .

回答(1)

2 years ago

我想我明白你的目标是什么 . 让我举个简单的例子 . 你需要一张满是日期的 table 才能加入 . 在数据仓库中,我们使用Date维度,该维度具有关于每个日期的属性 .

对于您的示例,您的日期维度表可能只有月份名称或数字:

1 2 ... 12

我们把这个表叫做几个月 .

然后你会做这样的事情,在没有数据的情况下计算零几个月 . 在这里,我使用所谓的公用表表达式或CTE(带有WITH的部分)代替表,因为我现在不关心创建永久表 .

WITH Months AS (
    SELECT 1 AS MonthNumber UNION
    SELECT 2 UNION
    SELECT 3 UNION
    SELECT 4 UNION
    SELECT 5 UNION
    SELECT 6 UNION
    SELECT 7 UNION
    SELECT 8 UNION
    SELECT 9 UNION
    SELECT 10 UNION
    SELECT 11 UNION
    SELECT 12         
)
SELECT M.MonthNumber, COUNT(*)
FROM Months as M 
    LEFT JOIN MyData as D 
        ON MONTH(D.SomeDateValue) = M.MonthNumber
GROUP BY M.MonthNumber

这将保证每个月出现一个计数,也许为零 .