这个问题有基于集合的解决方案吗?

下面应该做。

(但正如其他人所提到的,最好的解决办法是在假期结束时调整剩余的假期。)

select 
    id, employeeid, date, category, 
    case 
    when  earned_so_far + hours - total_spent > hours then 
        hours 
    else 
        earned_so_far + hours - total_spent
    end as hours
from 
    (
                select 
                    id, employeeid, date, category, hours,
                    (
                        select 
                            isnull(sum(hours),0)
                        from 
                            vacations 
                        WHERE 
                            category = 'Vacation Earned' 
                            and 
                            date < v.date
                            and
                            employeeid = v.employeeid
                    ) as earned_so_far,
                    (
                        select
                            isnull(sum(hours),0)
                        from
                            vacations
                        where 
                            category = 'Vacation Used'
                            and 
                            employeeid = v.employeeid
                    ) as total_spent
                from 
                    vacations V
                where category = 'Vacation Earned'
    ) earned
where
    earned_so_far + hours > total_spent

逻辑是

1. 为每个计算 earned 划船,赢得的时间 到目前为止
2. 计算此用户使用的总小时数
3. 如果此记录的总工作小时数+总工作小时数-总工作小时数>0,请选择该记录

在思考这个问题的时候,我想到你唯一需要关心的 什么时候 假期到期即获得。如果是这样的话,最简单的解决方案是将“假期过期”记录添加到表中,这样一个员工的剩余假期总是 sum(vacation earned) - (sum(vacation expired) + sum(vacatation used)) . 您甚至可以使用上次假期过期记录作为查询的起点来显示所需的准确记录。

但我想这不是一个选择。要按要求解决这个问题,请记住,每当您发现自己使用临时表时,请尝试将该数据放入cte(common table expression)中。不幸的是,我现在有一个会议,所以我没有时间写查询(也许稍后,听起来很有趣),但这应该让你开始。

我发现你的整个结果集混乱和不准确,我可以看到员工说,“不,我在1月25日挣了2个小时,而不是1个小时。”这不是真的,他们在那一天挣了1个小时,这只是部分抵消,你将没有结束的问题,如果你选择这样显示。我会用另一种方式来表达这些信息。通常,您要么显示所有休假操作(已获得、已过期和已使用)的列表,在底部显示总计,要么显示可用和已使用的摘要。

在超过30年的劳动生涯中,在许多不同的计时系统下(以及在我还是一名管理分析员时的更多研究),我从未见过有人想用这种方式显示计时信息。我想这是有原因的。如果这是一个要求,我建议你把它推回去,解释一下读取数据会有多混乱,这是因为很难得到一个性能良好的解决方案。如果不让客户相信这是个糟糕的主意,我是不会接受这个要求的。

随着时间的推移和记录的增加,性能会越来越差,除非您采取措施,例如:

• 一旦旧行被“取消”(例如,已获得的假期已添加并计入同等的已使用假期行;已使用的假期已设置为“过期”已获得的假期为“已消耗”),则清除这些旧行。
• 添加一个列,该列标记某行是否已被“取消”,并将该列合并到索引中

以这种方式跟踪数据的变化似乎是修改表结构的一个参数(有几个,而不仅仅是一个),但这超出了当前问题的范围。

至于查询本身,我将构建两个聚合,做一些减法,使其成为一个子查询,然后巧妙地使用一个排名函数将其加入。在某个地方也有关联子查询的味道。我以后可能会想办法解决这个问题(我时间不够),但我敢打赌一定有人比我强。

我建议修改这个表,以便在它自己的列中保持平衡。这样,您只需要获取最新的记录就可以知道员工的立场。

这样,你就可以满足简单的情况(“我有多少假期”),同时仍然可以做你在“哪些假期时间与其他时间不符”报告中寻找的尴尬汇总,我希望这是你不经常需要的。