账单日期的月平均日

Question

我们的计费系统有一个位置表。这些地方每个月都有账单。这些账单每个月可以变动几天。第六个月开出账单，第二个月是第八次)。不过，他们总是在一个月的同一时间逗留。我需要想出一个“停电”范围，基本上是每月的任何一天，地点已经在过去的X个月计费。它需要适当地计算可能在月末/月初之间反弹的地点。

为此，唯一相关的表是位置表&票据表。Loc有一个位置列表，其中LocationID是PK。其他领域与此无关，我相信。有一个账单表，它的文档号是PK，LocationID是FK。此外，与许多其他字段，如文档数量，到期日等。它也有一个计费日期，这是我想要计算我的‘断电’日期。

基本上，我们将改变每一米，而不想改变他们的一天，他们可能会被收费。

示例数据：

地点：

111111、Field1、Field2等。

222222、Field1、Field2等。

Bill (DocNum，LocationID，BillingDate):

BILL0001,111111,2018年6月1日

BILL0002,111111,2018年8月2日

BILL0003,111111,2018年3月5日

BILL0004,111111,2018年6月4日

BILL0005,111111,2018年5月11日

BILL0006,111111,2018年6月10日

BILL0007,111111,2018年9月7日

BILL0008,222222,2018年1月30日

BILL0009,222222,2018年3月1日

BILL0010,222222,2018年4月2日

BILL0011,222222,2018年5月3日

BILL0012,222222,2018年6月1日

BILL0013,222222,2018年7月1日

BILL0014,222222,2018年7月28日

示例输出：

地点: 111111 BlackOut: 6-11

地点: 222222 BlackOut: 28-3

我该怎么做呢？

Answer 1

据我所知，您想要计算出计费发生的“平均”日，然后基于此构造一个日期范围？

我能想到的最精确的方法是计算一个距离函数，并试图找出这个距离函数最小的月份的日期。一个例子是：

距离()= Min((Day(BillingDate) - TrialDayNumber) % 30，(TrialDayNumber - Day(BillingDate)) % 30)

这将产生一个介于0到15之间的数字，告诉您一个记帐日期离一个月中任意一天的TrialDayNumber有多远。

您想要尝试选择的一天，平均来说，有最低的距离值，因为这将是一个月中的一天，是最接近你的帐单日。如果可以在SQL之外执行计算(例如，将数据导出到Excel或SSRS)，这种优化函数将更容易计算。

我相信您完全可以使用SQL来计算它，但这是相当棘手的。我甚至为每个DayOfTheMonth / location ID组合生成了一个具有距离结果的表，但是当我试图将其缩小到每个位置的最佳距离时，我就陷入了困境。

WITH DaysOfTheMonth as(
SELECT 1 as DayNumber
UNION ALL
SELECT DayNumber + 1 as DayNumber
FROM DaysOfTheMonth
WHERE AutoNumber < 31
)
SELECT Bills.LocationID, DaysOfTheMonth.DayNumber,
Avg((Day(Bills.BillingDate)-DaysOfTheMonth.DayNumber)%30) as Distance
FROM Bills, DaysOfTheMonth
GROUP BY Bills.LocationID, DaysOfTheMonth.DayNumber

这种方法的一个很大的缺点是计算量很大。