在每个时间点计算 [英] Calculation at each point in time

问题描述

我正在尝试编写一个计算总计的存储过程每个时间点。

这是我的数据集

类型	用户	费率（％）	开始日期	结束日期
1	User1	30	01-Jan	31-Dec
1	User2	30	01-Jan	31-Dec
1	User3	20	01-Jan	31-Dec
1	User4	20	01-Jan	30-Jun
1	User5	20	01-Jul	31-Dec

在每个时间点，总费率为100％。

如何编写运行该检查的代码？有人可以帮忙吗？

如果您需要更多说明，请告诉我。

Hi,

I am trying to write a stored procedure that calculates a total at each point in time.
This is my dataset below

Type	User	Rate(%)	Start Date	End Date
1	User1	30	01-Jan	31-Dec
1	User2	30	01-Jan	31-Dec
1	User3	20	01-Jan	31-Dec
1	User4	20	01-Jan	30-Jun
1	User5	20	01-Jul	31-Dec

At each point in time, the total rate is 100%.
How can I write a code that runs that check? Can anyone help?
Let me know if you need more clarification.

推荐答案

以下内容将查找日期之间的每个日期你转到总数不等于100的程序。（为了演示目的，我更改了样本数据，因此有些日期里，Rate列的总和不是100％。

The following will find every date between the dates you pass to the procedure where the total does not equal 100.  (For demo purposes I changed the sample data so there would be some dates where the sum of the Rate column was not 100%.

Create Table #Sample(Type int, [User] varchar(10), Rate int, StartDate date, EndDate date);
Insert #Sample (Type, [User], Rate, StartDate, EndDate) Values
(1, 'User1', 30, '20190101', '20191231'),
(1, 'User2', 30, '20190101', '20191231'),
(1, 'User3', 20, '20190101', '20191231'),
(1, 'User4', 20, '20190101', '20190702'),
(1, 'User5', 20, '20190701', '20191031'),
(1, 'User5', 20, '20191102', '20191231');

go
Create Procedure FooCheckDates(@StartDateToCheck date, @EndDateToCheck date ) As
;With cteCalendar As
(Select @StartDateToCheck As dt
Union All
Select DateAdd(day, 1, dt) As dt
From cteCalendar
Where dt < @EndDateToCheck)
Select c.dt, IsNull(Sum(s.Rate), 0) As TotalRate
From cteCalendar c
Left Outer Join #Sample s On c.dt Between s.StartDate And s.EndDate
Group By c.dt
Having IsNull(Sum(s.Rate), 0) <> 100
Order By dt
Option (MaxRecursion 10000);
go
Exec FooCheckDates '20190101', '20191231'
go
-- Cleanup
Drop Table #Sample;
go
Drop Procedure FooCheckDates;

Tom

Tom

这篇关于在每个时间点计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！