通过计数周值创建值字段

Question

我有一个查询，它返回以下信息

ID  Employees  wk1   wk2   wk3   wk4 
1      10       2     7     4     5
2      15       5     5     5     5

我想在这个查询中添加另一个字段，它是每周员工的计算结果。它可以通过查看员工栏并计算出这个数字能满足多少wk。示例如下所示；

ID  Employees WeeksEmployees  wk1   wk2   wk3   wk4 
1      10            2          2     7     4     5
2      15            3          5     5     5     5

如果我能做到这一点，那将是非常有帮助的。

Answer 1

这将计算出你所要求的数字。

create table #demo (id int, employees int, wk1 int, wk2 int, wk3 int, wk4 int)
insert #demo values
(1,10,2,7,4,5),
(2,15,5,5,5,5)

select *, case
when wk1 + wk2 + wk3 + wk4 <= employees then 4
when wk1 + wk2 + wk3 <= employees then 3
when wk1 + wk2 <= employees then 2
when wk1 <= employees then 1 else 0 end as WeeksEmployees
from #demo

我可以想象你可能想有更多的星期，因为你在IT，我想你不想把它全部打印出来；如果是的话，请让我知道需求，我会看看是否有一个支点或动态解决方案适合。

Answer 2

您可以这样做，但如果您并不总是希望从第一周开始，那么您将需要准确地重复您的标准。

DECLARE @EmployeeWeekData TABLE
   (
     ID INT
   , Employees INT
   , wk1 INT
   , wk2 INT
   , wk3 INT
   , wk4 INT
   )
INSERT   INTO @EmployeeWeekData
VALUES   ( 1, 10, 2, 7, 4, 5 )
,        ( 2, 15, 5, 5, 5, 5 )

SELECT   *
       , CASE WHEN Employees - ( wk1 + wk2 + wk3 + wk4 ) >= 0 THEN 4
              WHEN Employees - ( wk1 + wk2 + wk3 ) >= 0 THEN 3
              WHEN Employees - ( wk1 + wk2 ) >= 0 THEN 2
              WHEN Employees - ( wk1 ) >= 0 THEN 1
              ELSE 0
         END AS WeeksFulfilled
FROM     @EmployeeWeekData ewd

好样的，CarrieAnne

Answer 3

            CREATE TABLE SAMPLE1
            (
             ID         INT IDENTITY
            ,EMPLOYEES  INT
            ,WK1        INT
            ,WK2        INT
            ,WK3        INT
            ,WK4        INT
            )

            INSERT INTO SAMPLE1 VALUES(10,2,7,4,5)
            INSERT INTO SAMPLE1 VALUES(15,5,5,5,5)
            INSERT INTO SAMPLE1 VALUES(12,15,5,5,5)
            INSERT INTO SAMPLE1 VALUES(14,3,4,7,5)
            INSERT INTO SAMPLE1 VALUES(20,5,5,5,5)


            SELECT ID,EMPLOYEES,WK1,WK2,WK3,WK4
                   ,CASE
                       WHEN WK1>EMPLOYEES THEN 0 
                       WHEN WK1+WK2>EMPLOYEES THEN 1
                       WHEN WK1+WK2+WK3>EMPLOYEES THEN 2
                       WHEN WK1+WK2+WK3+WK4>EMPLOYEES THEN 3
                       ELSE 4
                    END AS WEEKSEMPLOYEES
            FROM SAMPLE1