SQL Server：基于其他列的新列

I want to calculate a new value for the new column based on other columns in T-SQL.

我想根据T-SQL中的其他列计算新列的新值。

My data is look like this:

我的数据如下所示：

Each row represents one person in one day.

每行代表一天中的一个人。

The WorkHours is calculated based on the Portion column:

WorkHours基于“部分”列计算：

Round(FF.Portion * 7.4, 3) AS WorkHours

I want to calculate a percentage of hours which people not have been at work in relation to the TOTAL workhours for day for each school. For example if 10 people work full hour in one school for one day, it gives 74 working hours and if one person have been sick that day it will give (7,4 % 74 * 100) which is 10% (the WorkHours is calculated based on Portion column)

我想计算每个学校每天工作时间总计不在工作时间的百分比。例如，如果10个人在一所学校工作一小时一天，它就会有74个工作小时，如果一个人当天生病，它会给出（7,4％74 * 100）10％（工作时间计算）基于部分专栏）

1 个解决方案

#1

In your comment you state Peter had 6 hours on 1/1/2017 as "seek" but it was actually 7.4. With that in mind, we can calculate your results as follows:

在你的评论中你说彼得在2017年1月1日的6小时是“寻求”，但实际上是7.4。考虑到这一点，我们可以按如下方式计算您的结果：

declare @table table (Name varchar(16), Date date, School char(2), FreedayCode int, Freeday varchar(64), Portion decimal (6,4))
insert into @table
values
('Mike','20170101','AA',-1,'AtWork',1),
('Mike','20170201','AA',1,'Seek',1),
('Ali','20170101','BB',-1,'AtWork',0.94594),
('Ali','20170201','BB',-1,'AtWork',0.94594),
('Sara','20170101','CC',2,'holiday',1),
('Sara','20170201','CC',1,'Seek',1),
('Peter','20170101','AA',1,'Seek',1),
('Peter','20170201','AA',1,'Seek',1),
('Nina','20170101','AA',-1,'AtWork',0.81081),
('Nina','20170201','AA',-1,'AtWork',0.81081)

select
        Name
        ,Date
        ,School
        ,FreeDayCode
        ,Freeday,Portion
        ,NewColumn = sum(case when Freeday <> 'AtWork' then Round(Portion * 7.4,3) else 0 end) over (partition by Date, School) /  sum(Round(Portion * 7.4,3)) over (partition by Date, School)
from 
    @table
order by
    Date
    ,School

RETURNS

退货

+-------+------------+--------+-------------+---------+---------+-----------+
| Name  |    Date    | School | FreeDayCode | Freeday | Portion | NewColumn |
+-------+------------+--------+-------------+---------+---------+-----------+
| Mike  | 2017-01-01 | AA     |          -1 | AtWork  |  1.0000 |  0.355769 |
| Peter | 2017-01-01 | AA     |           1 | Seek    |  1.0000 |  0.355769 |
| Nina  | 2017-01-01 | AA     |          -1 | AtWork  |  0.8108 |  0.355769 |
| Ali   | 2017-01-01 | BB     |          -1 | AtWork  |  0.9459 |  0.000000 |
| Sara  | 2017-01-01 | CC     |           2 | holiday |  1.0000 |  1.000000 |
| Peter | 2017-02-01 | AA     |           1 | Seek    |  1.0000 |  0.711538 |
| Mike  | 2017-02-01 | AA     |           1 | Seek    |  1.0000 |  0.711538 |
| Nina  | 2017-02-01 | AA     |          -1 | AtWork  |  0.8108 |  0.711538 |
| Ali   | 2017-02-01 | BB     |          -1 | AtWork  |  0.9459 |  0.000000 |
| Sara  | 2017-02-01 | CC     |           1 | Seek    |  1.0000 |  1.000000 |
+-------+------------+--------+-------------+---------+---------+-----------+