SQL Server How can I create a T-SQL query that does a running total with a maximum?

wyyhbhjk 于 9个月前发布在其他

关注(0)|答案(1)|浏览(111)

We have an unusual sick time policy where we can't accrue more than 56 hours. I need to write a query to reflect that. This is what I have

SELECT 
    t.employeecode, t.LeaveAccrued, PPE, RecordDate,
    (CASE 
         WHEN (SUM(t.LeaveAccrued) OVER (PARTITION BY t.Employeecode ORDER BY RecordDate)) > 56 
             THEN 56
         WHEN (SUM(t.LeaveAccrued) OVER (PARTITION BY t.Employeecode ORDER BY RecordDate)) <= 56 
             THEN SUM(t.LeaveAccrued) OVER (PARTITION BY t.Employeecode ORDER BY RecordDate)
     END) AS accrued_time
FROM 
    time t

That looks like it works except it fails when somebody has maxed out at 56 hours and then takes a bunch of time. Here is an example of the results where it fails

employeecode	LeaveAccrued	PPE	RecordDate	accrued_time
****	0.9000	PPE 8 12 22	2022-08-16 00:00:00.000	54.8169
****	-29.0000	PPE 8 12 22	2022-08-16 00:00:00.000	54.8169
****	2.2667	PPE 8 26 22	2022-08-30 00:00:00.000	52.0836
****	-5.0000	PPE 8 26 22	2022-08-30 00:00:00.000	52.0836

I took 29 hours of sick time when my daughter was born. It should have dropped down to about 27 hours of sick time.

Any suggestions on how to deal with that? I have been calculating based on an annual basis and putting rollover data in a different table but I would really like to fix this once and for all.

Thanks

sql-server

来源：https://stackoverflow.com/questions/77574150/how-can-i-create-a-t-sql-query-that-does-a-running-total-with-a-maximum

1条答案

按热度按时间

yrdbyhpb1#

You can do it the other way, calculate the running total first, then limit the value.

SELECT *
    , CASE WHEN running_accrued_time > 56 THEN 56 ELSE running_accrued_time END AS running_accrued_time
FROM (
    SELECT t.employeecode, t.LeaveAccrued, PPE, RecordDate
        , SUM(t.LeaveAccrued) OVER(PARTITION BY t.EmployeeCode ORDER BY RecordDate) AS accrued_time
        , SUM(t.LeaveAccrued) OVER(PARTITION BY t.EmployeeCode ORDER BY RecordDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_accrued_time
    FROM #time t
) d

赞(0）回复(0）举报 9个月前

我来回答

SQL Server How can I create a T-SQL query that does a running total with a maximum?

1条答案

相关问题

热门标签

最新问答