blocks|key|808918|text|在T中有效吗？根据目前描述的模式，我怀疑没有。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|808919|但是，如果您的费率表存储每个日期的三个关税，则可能是可能的。除了手头的问题之外，至少有一个原因可以解释为什么你会这么做:在某个时候，一个时期的利率可能会发生变化，而你可能需要有历史上的利率。|808920|假设我们有这些桌子：|808921|CREATE+TABLE+rates+(
++++from_date_time+DATETIME
,+++to_date_time+DATETIME
,+++rate+MONEY
)

CREATE+TABLE+calls+(
++++id+INT
,+++started+DATETIME
,+++ended+DATETIME
)|code-block|syntax|javascript|808922|我认为有三种情况需要考虑(可能更多，我正在编造)：|808923|呼叫完全在一个速率期间内发生。|ordered-list-item|808924|呼叫从一个速率周期(a)开始，在下一个(b)期间结束。|808925|呼叫至少跨越一个完整的速率周期。|808926|假设速率为每秒，我认为您可能会生成如下(完全未经测试的)查询|808927|SELECT+id,+DATEDIFF(ss,+started,+ended)+*+rate+/*+case+1+*/
FROM+rates+JOIN+calls+ON+started+>+from_date_time+AND+ended+<+to_date_time
UNION
SELECT+id,+DATEDIFF(ss,+started,+to_date_time)+*+rate+/*+case+2a+and+the+start+of+case+3+*/
FROM+rates+JOIN+calls+ON+started+>+from_date_time+AND+ended+>+to_date_time
UNION
SELECT+id,+DATEDIFF(ss,+from_date_time,+ended)+*+rate+/*+case+2b+and+the+last+part+of+case+3+*/
FROM+rates+JOIN+calls+ON+started+<+from_date_time+AND+ended+<+to_date_time
UNION
SELECT+id,+DATEDIFF(ss,+from_date_time,+to_date_time)+*+rate+/*+case+3+for+entire+rate+periods,+should+pick+up+all+complete+periods+*/
FROM+rates+JOIN+calls+ON+started+<+from_date_time+AND+ended+>+to_date_time|808928|您可以在SQL中应用SUM..GROUP或在代码中处理它。或者，使用精心构造的逻辑，您可能可以将UNIONed部件合并成一个包含大量and和OR的WHERE子句。我认为工会更清楚地表明了这一意图。|808929|HTH+&+HIW+(希望它能起作用)|808930|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|13|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|14|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|15|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|16|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|17|8|@]|9|@]|A|$]]|$1|M|3|N|5|O|7|18|8|@]|9|@]|A|$]]|$1|P|3|Q|5|O|7|19|8|@]|9|@]|A|$]]|$1|R|3|S|5|O|7|1A|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|1B|8|@]|9|@]|A|$]]|$1|V|3|W|5|H|7|1C|8|@]|9|@]|A|$I|J]]|$1|X|3|Y|5|6|7|1D|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1E|8|@]|9|@]|A|$]]|$1|11|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|12|$]]

Effectively in T-SQL? I suspect not, with the schema as described at present.

It might be possible, however, if your rate table stores the three tariffs for each date. There is at least one reason why you might do this, apart from the problem at hand: it's likely at some point that rates for one period or another might change and you may need to have the historic rates available.

So say we have these tables:

<pre><code>CREATE TABLE rates (
 from_date_time DATETIME
, to_date_time DATETIME
, rate MONEY
)

CREATE TABLE calls (
 id INT
, started DATETIME
, ended DATETIME
)
</code></pre>

I think there are three cases to consider (may be more, I'm making this up as I go):

<ol>
<li>a call occurs entirely within one
rate period</li>
<li>a call starts in one
rate period (a) and ends in the next (b)</li>
<li>a call spans at least one complete
rate period</li>
</ol>

Assuming rate is per second, I think you might produce something like the following (completely untested) query

<pre><code>SELECT id, DATEDIFF(ss, started, ended) * rate /* case 1 */
FROM rates JOIN calls ON started &gt; from_date_time AND ended &lt; to_date_time
UNION
SELECT id, DATEDIFF(ss, started, to_date_time) * rate /* case 2a and the start of case 3 */
FROM rates JOIN calls ON started &gt; from_date_time AND ended &gt; to_date_time
UNION
SELECT id, DATEDIFF(ss, from_date_time, ended) * rate /* case 2b and the last part of case 3 */
FROM rates JOIN calls ON started &lt; from_date_time AND ended &lt; to_date_time
UNION
SELECT id, DATEDIFF(ss, from_date_time, to_date_time) * rate /* case 3 for entire rate periods, should pick up all complete periods */
FROM rates JOIN calls ON started &lt; from_date_time AND ended &gt; to_date_time
</code></pre>

You could apply a SUM..GROUP BY over that in SQL or handle it in your code. Alternatively, with carefully-constructed logic, you could probably merge the UNIONed parts into a single WHERE clause with lots of ANDs and ORs. I thought the UNION showed the intent rather more clearly.

HTH &amp; HIW (Hope It Works...)

blocks|key|808943|text|这是一个线关于你在sqlteam.com遇到的问题。请看一看，因为它包含了一些非常巧妙的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|808944|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110247^0|0|5|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

<a href="http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110247" rel="nofollow noreferrer">This is a thread</a> about your problem we had over at sqlteam.com. take a look because it includes some pretty slick solutions.

blocks|key|550597|text|根据迈克·伍德豪斯的回答，这可能对你有用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|550598|SELECT+id,+SUM(DATEDIFF(ss,+started,+ended)+*+rate)
FROM+rates+
JOIN+calls+ON+
+++++CASE+WHEN+started+<+from_date_time+
++++++++++THEN+DATEADD(ss,+1,+from_date_time)+
++++++++++ELSE+started+>+from_date_time
+++AND+
+++++CASE+WHEN+ended+>+to_date_time+
++++++++++THEN+DATEADD(ss,+-1,+to_date_time)+
++++++++++ELSE+ended+END+
+++++<+ended
GROUP+BY+id|code-block|syntax|javascript|550599|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Following on from Mike Woodhouse's answer, this may work for you:

<pre><code>SELECT id, SUM(DATEDIFF(ss, started, ended) * rate)
FROM rates 
JOIN calls ON 
 CASE WHEN started &lt; from_date_time 
 THEN DATEADD(ss, 1, from_date_time) 
 ELSE started &gt; from_date_time
 AND 
 CASE WHEN ended &gt; to_date_time 
 THEN DATEADD(ss, -1, to_date_time) 
 ELSE ended END 
 &lt; ended
GROUP BY id
</code></pre>

blocks|key|809802|text|数据库中相关表的实际模式将是非常有用的。我会做出最好的猜测。我已经假设，费率表有start_time和end_time作为午夜过后的分钟数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|809803|使用日历表(在大多数数据库中有一个非常有用的表)：|809804|SELECT
+++++C.id,
+++++R.rate,
+++++SUM(DATEDIFF(ss,
++++++++++CASE
+++++++++++++++WHEN+C.start_time+<+R.rate_start_time+THEN+R.rate_start_time
+++++++++++++++ELSE+C.start_time
++++++++++END,
++++++++++CASE
+++++++++++++++WHEN+C.end_time+>+R.rate_end_time+THEN+R.rate_end_time
+++++++++++++++ELSE+C.end_time
++++++++++END))+AS+
FROM
+++++Calls+C
INNER+JOIN
+++++(
+++++SELECT
++++++++++DATEADD(mi,+Rates.start_time,+CAL.calendar_date)+AS+rate_start_time,
++++++++++DATEADD(mi,+Rates.end_time,+CAL.calendar_date)+AS+rate_end_time,
++++++++++Rates.rate
+++++FROM
++++++++++Calendar+CAL
+++++INNER+JOIN+Rates+ON
++++++++++1+=+1
+++++WHERE
++++++++++CAL.calendar_date+>=+DATEADD(dy,+-1,+C.start_time)+AND
++++++++++CAL.calendar_date+<=+C.start_time
+++++)+AS+R+ON
++++++++++R.rate_start_time+<+C.end_time+AND
++++++++++R.rate_end_time+>+C.start_time
GROUP+BY
+++++C.id,
+++++R.rate|code-block|syntax|javascript|809805|我只是在打字的时候想出了这个，所以它是未经测试的，你很可能需要调整它，但希望你能看到总体的想法。|809806|我还意识到，您使用的是start_time和通话的持续时间。只要您看到C.end_time，就可以使用DATEADD(ss、C.start_time、C.duration)替换它，前提是持续时间是秒。|809807|这应该在任何适当的RDBMS中执行得非常快，假设有适当的索引，等等。|809808|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

An actual schema for the relevant tables in your database would have been very helpful. I'll take my best guesses. I've assumed that the Rates table has start_time and end_time as the number of minutes past midnight.

Using a calendar table (a VERY useful table to have in most databases):

<pre><code>SELECT
 C.id,
 R.rate,
 SUM(DATEDIFF(ss,
 CASE
 WHEN C.start_time &lt; R.rate_start_time THEN R.rate_start_time
 ELSE C.start_time
 END,
 CASE
 WHEN C.end_time &gt; R.rate_end_time THEN R.rate_end_time
 ELSE C.end_time
 END)) AS 
FROM
 Calls C
INNER JOIN
 (
 SELECT
 DATEADD(mi, Rates.start_time, CAL.calendar_date) AS rate_start_time,
 DATEADD(mi, Rates.end_time, CAL.calendar_date) AS rate_end_time,
 Rates.rate
 FROM
 Calendar CAL
 INNER JOIN Rates ON
 1 = 1
 WHERE
 CAL.calendar_date &gt;= DATEADD(dy, -1, C.start_time) AND
 CAL.calendar_date &lt;= C.start_time
 ) AS R ON
 R.rate_start_time &lt; C.end_time AND
 R.rate_end_time &gt; C.start_time
GROUP BY
 C.id,
 R.rate
</code></pre>

I just came up with this as I was typing, so it's untested and you will very likely need to tweak it, but hopefully you can see the general idea.

I also just realized that you use a start_time and a duration for your calls. You can just replace C.end_time wherever you see it with DATEADD(ss, C.start_time, C.duration) assuming that the duration is in seconds.

This should perform pretty quickly in any decent RDBMS assuming proper indexes, etc.

blocks|key|550688|text|如果您调用的时间少于100天：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|550689|WITH+generate_range(item)+AS
(
++++SELECT++0
++++UNION+ALL
++++SELECT++item+%2B+1
++++FROM++++generate_range
++++WHERE+++item+<+100
)
SELECT+tday,+id,+span
FROM+++(
+++++++SELECT+++tday,+id,
++++++++++++++++DATEDIFF(minute,
++++++++++++++++++++CASE+WHEN+tbegin+<+clbegin+THEN+clbegin+ELSE+tbegin+END,
++++++++++++++++++++CASE+WHEN+tend+<+clend+THEN+tend+ELSE+clend+END
++++++++++++++++)+AS+span
++++++++FROM++++(
++++++++++++++++SELECT++DATEADD(day,+item,+DATEDIFF(day,+0,+clbegin))+AS+tday,
++++++++++++++++++++++++ti.id,
++++++++++++++++++++++++DATEADD(minute,+rangestart,+DATEADD(day,+item,+DATEDIFF(day,+0,+clbegin)))+AS+tbegin,
++++++++++++++++++++++++DATEADD(minute,+rangeend,+DATEADD(day,+item,+DATEDIFF(day,+0,+clbegin)))+AS+tend
++++++++++++++++FROM++++calls,+generate_range,+tariff+ti
++++++++++++++++WHERE+++DATEADD(day,+1,+DATEDIFF(day,+0,+clend))+>+DATEADD(day,+item,+DATEDIFF(day,+0,+clbegin))
++++++++++++++++)+t1
++++++++)+t2
WHERE+++span+>+0|code-block|syntax|javascript|550690|我假设你把你的关税范围保持在几分钟内从午夜开始，并计算长度在分钟内。|550691|entityMap^0|A|3|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

Provided that you calls last less than <code>100</code> days:

<pre><code>WITH generate_range(item) AS
(
 SELECT 0
 UNION ALL
 SELECT item + 1
 FROM generate_range
 WHERE item &lt; 100
)
SELECT tday, id, span
FROM (
 SELECT tday, id,
 DATEDIFF(minute,
 CASE WHEN tbegin &lt; clbegin THEN clbegin ELSE tbegin END,
 CASE WHEN tend &lt; clend THEN tend ELSE clend END
 ) AS span
 FROM (
 SELECT DATEADD(day, item, DATEDIFF(day, 0, clbegin)) AS tday,
 ti.id,
 DATEADD(minute, rangestart, DATEADD(day, item, DATEDIFF(day, 0, clbegin))) AS tbegin,
 DATEADD(minute, rangeend, DATEADD(day, item, DATEDIFF(day, 0, clbegin))) AS tend
 FROM calls, generate_range, tariff ti
 WHERE DATEADD(day, 1, DATEDIFF(day, 0, clend)) &gt; DATEADD(day, item, DATEDIFF(day, 0, clbegin))
 ) t1
 ) t2
WHERE span &gt; 0
</code></pre>

I'm assuming you keep your tariffs ranges in minutes from midnight and count lengths in minutes too.

blocks|key|2079729|text|在我看来，这是一个分两个阶段的行动。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2079730|确定电话的哪个部分使用哪个速率在什么时间。|ordered-list-item|2079731|每个费率中的时间之和。|2079732|第一阶段比第二阶段更棘手，我在(IDS)中使用过这个示例，因为我没有。这些想法应该很容易翻译。INTO+TEMP子句创建一个具有适当架构的临时表；该表对于会话是私有的，并且在会话结束时消失(或者显式地删除它)。在IDS中，您还可以使用一个显式的创建临时表语句，然后插入到临时表选择.作为一种更冗长的方式来完成和临时工作相同的工作。|2079733|在SQL问题中，关于SO的问题经常发生，您没有提供模式，因此每个人都必须发明一个模式，该模式可能与您所描述的匹配，也可能与您所描述的不匹配。|2079734|假设您的数据位于两个表中。第一个表包含呼叫日志记录、有关所打电话的基本信息，如打电话的电话、被呼叫的号码、呼叫开始的时间和通话的持续时间：|2079735|CREATE+TABLE+clr++--+call+log+record
(
++++phone_id++++++VARCHAR(24)+NOT+NULL,+++--+billing+plan
++++called_number+VARCHAR(24)+NOT+NULL,+++--+needed+to+validate+call
++++start_time++++TIMESTAMP+++NOT+NULL,+++--+date+and+time+when+call+started
++++duration++++++INTEGER+++++NOT+NULL++++--+duration+of+call+in+seconds
++++++++++++++++++CHECK(duration+>+0),
++++PRIMARY+KEY(phone_id,+start_time)
++++--+other+complicated+range-based+constraints+omitted!
++++--+foreign+keys+omitted
++++--+there+would+probably+be+an+auto-generated+number+here+too.
);
INSERT+INTO+clr(phone_id,+called_number,+start_time,+duration)
++++VALUES('650-656-3180',+'650-794-3714',+'2009-02-26+15:17:19',+186234);|code-block|syntax|javascript|2079736|为了方便(主要是为了节省多次写入加法)，我想要一个clr表的副本，其中包含实际的结束时间：|2079737|SELECT++phone_id,+called_number,+start_time+AS+call_start,+duration,
++++++++start_time+%2B+duration+UNITS+SECOND+AS+call_end
++++FROM+clr
++++INTO+TEMP+clr_end;|2079738|关税数据存储在一个简单的表格中：|2079739|CREATE+TABLE+tariff
(
++++tariff_code+++CHAR(1)++++++NOT+NULL+++--+code+for+the+tariff
++++++++++++++++++CHECK(tariff_code+IN+('P','N','O'))
++++++++++++++++++PRIMARY+KEY,
++++rate_start++++TIME+++++++++NOT+NULL,++--+time+when+rate+starts
++++rate_end++++++TIME+++++++++NOT+NULL,++--+time+when+rate+ends
++++rate_charged++DECIMAL(7,4)+NOT+NULL+++--+rate+charged+(cents+per+second)
);
INSERT+INTO+tariff(tariff_code,+rate_start,+rate_end,+rate_charged)
++++VALUES('N',+'00:00:00',+'08:00:00',+0.9876);
INSERT+INTO+tariff(tariff_code,+rate_start,+rate_end,+rate_charged)
++++VALUES('P',+'08:00:00',+'19:00:00',+2.3456);
INSERT+INTO+tariff(tariff_code,+rate_start,+rate_end,+rate_charged)
++++VALUES('O',+'19:00:00',+'23:59:59',+1.2345);|2079740|我讨论了关税表是否应该使用时间或间隔值；在这种情况下，时间非常类似于相对于午夜的时间间隔，但时间间隔可以添加到时间戳中，而时间不能。我坚持时间，但这让事情变得很混乱。|2079741|这个查询的棘手部分是为每个没有循环的关税生成相关的日期和时间范围。事实上，我最终使用了一个嵌入在存储过程中的循环来生成一个整数列表。(我还使用了一种特定于，IDS的技术，它使用来自系统目录的表ID号作为范围1.N中连续整数的来源，它适用于11.50版中的1到60之间的数字。)|2079742|CREATE+PROCEDURE+integers(lo+INTEGER+DEFAULT+0,+hi+INTEGER+DEFAULT+0)
++++RETURNING+INT+AS+number;
++++DEFINE+i+INTEGER;
++++FOR+i+=+lo+TO+hi+STEP+1
++++++++RETURN+i+WITH+RESUME;
++++END+FOR;
END+PROCEDURE;|2079743|在简单情况下(也是最常见的情况)，呼叫属于单一关税期；多期呼叫增加了兴奋。|2079744|让我们假设我们可以创建一个与此模式匹配的表表达式，并涵盖我们可能需要的所有时间戳值：|2079745|CREATE+TEMP+TABLE+tariff_date_time
(
+++++tariff_code+++CHAR(1)++++++NOT+NULL,
+++++rate_start++++TIMESTAMP++++NOT+NULL,
+++++rate_end++++++TIMESTAMP++++NOT+NULL,
+++++rate_charged++DECIMAL(7,4)+NOT+NULL
);|2079746|幸运的是，你没有提到周末的价格，所以你对顾客的收费是一样的|2079747|周末和一周的费率。然而，答案应该适应这样的情况。|2079748|如果有可能的话。如果你要像给周末的价格一样复杂|2079749|公众假期，除了在圣诞节或新年，你收取最高的费用，而不是|2079750|周末费率由于需求高，那么最好将利率存储在一个永久的tariff_date_time表中。|2079751|填充tariff_date_time的第一步是生成与调用相关的日期列表：|2079752|SELECT+DISTINCT+EXTEND(DATE(call_start)+%2B+number,+YEAR+TO+SECOND)+AS+call_date
++++FROM+clr_end,
+++++++++TABLE(integers(0,+(SELECT+DATE(call_end)+-+DATE(call_start)+FROM+clr_end)))
+++++++++AS+date_list(number)
++++INTO+TEMP+call_dates;|2079753|这两个日期值之间的差异是一个整数的天数(在IDS中)。过程整数生成从0到调用所涵盖的天数的值，并将结果存储在临时表中。对于多条记录的更一般情况，最好是计算最小和最大日期，并在两者之间生成日期，而不是多次生成日期，然后使用DISTINCT子句删除它们。|2079754|现在，使用关税表的笛卡儿乘积和call_dates表生成每天的汇率信息。这是每隔一段时间关税时间将更加整洁的地方。|2079755|SELECT++r.tariff_code,
++++++++d.call_date+%2B+(r.rate_start+-+TIME+'00:00:00')+AS+rate_start,
++++++++d.call_date+%2B+(r.rate_end+++-+TIME+'00:00:00')+AS+rate_end,
++++++++r.rate_charged
++++FROM+call_dates+AS+d,+tariff+AS+r
++++INTO+TEMP+tariff_date_time;|2079756|现在，我们需要将呼叫日志记录与适用的费率相匹配。该条件是处理重叠问题的一种标准方法--如果第一个时间段的结束时间晚于第二个时间段的开始，而第一个时间段的开始时间在第二个时间段结束之前，则两个时间段重叠：|2079757|SELECT+tdt.*,+clr_end.*
FROM+tariff_date_time+tdt,+clr_end
WHERE+tdt.rate_end+>+clr_end.call_start
++AND+tdt.rate_start+<+clr_end.call_end
INTO+TEMP+call_time_tariff;|2079758|然后我们需要确定费率的开始和结束时间。费率的起始时间是费率开始时间的后一段时间和呼叫的开始时间。费率的结束时间是关税结束时间和通话结束时间的较早时间：|2079759|SELECT++phone_id,+called_number,+tariff_code,+rate_charged,
++++++++call_start,+duration,
++++++++CASE+WHEN+rate_start+<+call_start+THEN+call_start
++++++++ELSE+rate_start+END+AS+rate_start,
++++++++CASE+WHEN+rate_end+>=+call_end+THEN+call_end
++++++++ELSE+rate_end+END+AS+rate_end
++++FROM+call_time_tariff
++++INTO+TEMP+call_time_tariff_times;|2079760|最后，我们需要将每一税率所花费的时间相加，并将时间(以秒为单位)乘以收费率。由于SUM的结果(rate_end-+rate_start)是一个间隔，而不是一个数字，所以我不得不调用一个转换函数来将这个区间转换成一个十进制秒数，而这个(非标准的)函数是iv_seconds：|2079761|SELECT+phone_id,+called_number,+tariff_code,+rate_charged,
+++++++call_start,+duration,
+++++++SUM(rate_end+-+rate_start)+AS+tariff_time,
+++++++rate_charged+*+iv_seconds(SUM(rate_end+-+rate_start))+AS+tariff_cost
+++FROM+call_time_tariff_times
+++GROUP+BY+phone_id,+called_number,+tariff_code,+rate_charged,
++++++++++++call_start,+duration;|2079762|对于示例数据，这产生了数据(在这里，我没有打印电话号码和电话号码以保证紧凑性)：|2079763|N+++0.9876+++2009-02-26+15:17:19+++186234+++0+16:00:00+++56885.760000000
O+++1.2345+++2009-02-26+15:17:19+++186234+++0+10:01:11+++44529.649500000
P+++2.3456+++2009-02-26+15:17:19+++186234+++1+01:42:41++217111.081600000|2079764|这是一个非常昂贵的电话，但电信公司会对此感到满意的。您可以查看任何中间结果，以了解如何得出答案。您可以使用更少的临时表，而代价是更清晰。|2079765|对于单个调用，这与在客户端运行VB中的代码没有太大不同。对于很多电话来说，这可能会更有效率。我远不相信递归在VB中是必要的--直接迭代就足够了。|2079766|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|2H|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|2I|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|2J|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|2K|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|2L|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|2M|8|@]|9|@]|A|$]]|$1|M|3|N|5|O|7|2N|8|@]|9|@]|A|$P|Q]]|$1|R|3|S|5|6|7|2O|8|@]|9|@]|A|$]]|$1|T|3|U|5|O|7|2P|8|@]|9|@]|A|$P|Q]]|$1|V|3|W|5|6|7|2Q|8|@]|9|@]|A|$]]|$1|X|3|Y|5|O|7|2R|8|@]|9|@]|A|$P|Q]]|$1|Z|3|10|5|6|7|2S|8|@]|9|@]|A|$]]|$1|11|3|12|5|6|7|2T|8|@]|9|@]|A|$]]|$1|13|3|14|5|O|7|2U|8|@]|9|@]|A|$P|Q]]|$1|15|3|16|5|6|7|2V|8|@]|9|@]|A|$]]|$1|17|3|18|5|6|7|2W|8|@]|9|@]|A|$]]|$1|19|3|1A|5|O|7|2X|8|@]|9|@]|A|$P|Q]]|$1|1B|3|1C|5|6|7|2Y|8|@]|9|@]|A|$]]|$1|1D|3|1E|5|6|7|2Z|8|@]|9|@]|A|$]]|$1|1F|3|1G|5|6|7|30|8|@]|9|@]|A|$]]|$1|1H|3|1I|5|6|7|31|8|@]|9|@]|A|$]]|$1|1J|3|1K|5|6|7|32|8|@]|9|@]|A|$]]|$1|1L|3|1M|5|6|7|33|8|@]|9|@]|A|$]]|$1|1N|3|1O|5|O|7|34|8|@]|9|@]|A|$P|Q]]|$1|1P|3|1Q|5|6|7|35|8|@]|9|@]|A|$]]|$1|1R|3|1S|5|6|7|36|8|@]|9|@]|A|$]]|$1|1T|3|1U|5|O|7|37|8|@]|9|@]|A|$P|Q]]|$1|1V|3|1W|5|6|7|38|8|@]|9|@]|A|$]]|$1|1X|3|1Y|5|O|7|39|8|@]|9|@]|A|$P|Q]]|$1|1Z|3|20|5|6|7|3A|8|@]|9|@]|A|$]]|$1|21|3|22|5|O|7|3B|8|@]|9|@]|A|$P|Q]]|$1|23|3|24|5|6|7|3C|8|@]|9|@]|A|$]]|$1|25|3|26|5|O|7|3D|8|@]|9|@]|A|$P|Q]]|$1|27|3|28|5|6|7|3E|8|@]|9|@]|A|$]]|$1|29|3|2A|5|O|7|3F|8|@]|9|@]|A|$P|Q]]|$1|2B|3|2C|5|6|7|3G|8|@]|9|@]|A|$]]|$1|2D|3|2E|5|6|7|3H|8|@]|9|@]|A|$]]|$1|2F|3|-4|5|6|7|3I|8|@]|9|@]|A|$]]]|2G|$]]

It seems to me that this is an operation with two phases.

<ol>
<li>Determine which parts of the phone call use which rates at which time.</li>
<li>Sum the times in each of the rates.</li>
</ol>

Phase 1 is trickier than Phase 2. I've worked the example in IBM Informix Dynamic Server (IDS) because I don't have MS SQL Server. The ideas should translate easily enough. The INTO TEMP clause creates a temporary table with an appropriate schema; the table is private to the session and vanishes when the session ends (or you explicitly drop it). In IDS, you can also use an explicit CREATE TEMP TABLE statement and then INSERT INTO temp-table SELECT ... as a more verbose way of doing the same job as INTO TEMP.

As so often in SQL questions on SO, you've not provided us with a schema, so everyone has to invent a schema that might, or might not, match what you describe.

Let's assume your data is in two tables. The first table has the call log records, the basic information about the calls made, such as the phone making the call, the number called, the time when the call started and the duration of the call:

<pre><code>CREATE TABLE clr -- call log record
(
 phone_id VARCHAR(24) NOT NULL, -- billing plan
 called_number VARCHAR(24) NOT NULL, -- needed to validate call
 start_time TIMESTAMP NOT NULL, -- date and time when call started
 duration INTEGER NOT NULL -- duration of call in seconds
 CHECK(duration &gt; 0),
 PRIMARY KEY(phone_id, start_time)
 -- other complicated range-based constraints omitted!
 -- foreign keys omitted
 -- there would probably be an auto-generated number here too.
);
INSERT INTO clr(phone_id, called_number, start_time, duration)
 VALUES('650-656-3180', '650-794-3714', '2009-02-26 15:17:19', 186234);
</code></pre>

For convenience (mainly to save writing the addition multiple times), I want a copy of the clr table with the actual end time:

<pre><code>SELECT phone_id, called_number, start_time AS call_start, duration,
 start_time + duration UNITS SECOND AS call_end
 FROM clr
 INTO TEMP clr_end;
</code></pre>

The tariff data is stored in a simple table:

<pre><code>CREATE TABLE tariff
(
 tariff_code CHAR(1) NOT NULL -- code for the tariff
 CHECK(tariff_code IN ('P','N','O'))
 PRIMARY KEY,
 rate_start TIME NOT NULL, -- time when rate starts
 rate_end TIME NOT NULL, -- time when rate ends
 rate_charged DECIMAL(7,4) NOT NULL -- rate charged (cents per second)
);
INSERT INTO tariff(tariff_code, rate_start, rate_end, rate_charged)
 VALUES('N', '00:00:00', '08:00:00', 0.9876);
INSERT INTO tariff(tariff_code, rate_start, rate_end, rate_charged)
 VALUES('P', '08:00:00', '19:00:00', 2.3456);
INSERT INTO tariff(tariff_code, rate_start, rate_end, rate_charged)
 VALUES('O', '19:00:00', '23:59:59', 1.2345);
</code></pre>

I debated whether the tariff table should use TIME or INTERVAL values; in this context, the times are very similar to intervals relative to midnight, but intervals can be added to timestamps where times cannot. I stuck with TIME, but it made things messy.

The tricky part of this query is generating the relevant date and time ranges for each tariff without loops. In fact, I ended up using a loop embedded in a stored procedure to generate a list of integers. (I also used a technique that is specific to IBM Informix Dynamic Server, IDS, using the table ID numbers from the system catalog as a source of contiguous integers in the range 1..N, which works for numbers from 1 to 60 in version 11.50.)

<pre><code>CREATE PROCEDURE integers(lo INTEGER DEFAULT 0, hi INTEGER DEFAULT 0)
 RETURNING INT AS number;
 DEFINE i INTEGER;
 FOR i = lo TO hi STEP 1
 RETURN i WITH RESUME;
 END FOR;
END PROCEDURE;
</code></pre>

In the simple case (and the most common case), the call falls in a single-tariff period; the multi-period calls add the excitement. 

Let's assume we can create a table expression that matches this schema and covers all the timestamp values we might need:

<pre><code>CREATE TEMP TABLE tariff_date_time
(
 tariff_code CHAR(1) NOT NULL,
 rate_start TIMESTAMP NOT NULL,
 rate_end TIMESTAMP NOT NULL,
 rate_charged DECIMAL(7,4) NOT NULL
);
</code></pre>

Fortunately, you haven't mentioned weekend rates, so you charge the customers the same 

rates at the weekend as during the week. However, the answer should adapt to such 

situations if at all possible. If you were to get as complex as giving weekend rates on 

public holidays, except that at Christmas or New Year, you charge peak rate instead of 

weekend rate because of the high demand, then you would be best off storing the rates in a permanent tariff_date_time table.

The first step in populating tariff_date_time is to generate a list of dates which are relevant to the calls:

<pre><code>SELECT DISTINCT EXTEND(DATE(call_start) + number, YEAR TO SECOND) AS call_date
 FROM clr_end,
 TABLE(integers(0, (SELECT DATE(call_end) - DATE(call_start) FROM clr_end)))
 AS date_list(number)
 INTO TEMP call_dates;
</code></pre>

The difference between the two date values is an integer number of days (in IDS).
The procedure integers generates values from 0 to the number of days covered by the call and stores the result in a temp table. For the more general case of multiple records, it might be better to calculate the minimum and maximum dates and generate the dates in between rather than generate dates multiple times and then eliminate them with the DISTINCT clause.

Now use a cartesian product of the tariff table with the call_dates table to generate the rate information for each day. This is where the tariff times would be neater as intervals.

<pre><code>SELECT r.tariff_code,
 d.call_date + (r.rate_start - TIME '00:00:00') AS rate_start,
 d.call_date + (r.rate_end - TIME '00:00:00') AS rate_end,
 r.rate_charged
 FROM call_dates AS d, tariff AS r
 INTO TEMP tariff_date_time;
</code></pre>

Now we need to match the call log record with the tariffs that apply. The condition is a standard way of dealing with overlaps - two time periods overlap if the end of the first is later than the start of the second and if the start of the first is before the end of the second:

<pre><code>SELECT tdt.*, clr_end.*
FROM tariff_date_time tdt, clr_end
WHERE tdt.rate_end &gt; clr_end.call_start
 AND tdt.rate_start &lt; clr_end.call_end
INTO TEMP call_time_tariff;
</code></pre>

Then we need to establish the start and end times for the rate. The start time for the rate is the later of the start time for the tariff and the start time of the call. The end time for the rate is the earlier of the end time for the tariff and the end time of the call:

<pre><code>SELECT phone_id, called_number, tariff_code, rate_charged,
 call_start, duration,
 CASE WHEN rate_start &lt; call_start THEN call_start
 ELSE rate_start END AS rate_start,
 CASE WHEN rate_end &gt;= call_end THEN call_end
 ELSE rate_end END AS rate_end
 FROM call_time_tariff
 INTO TEMP call_time_tariff_times;
</code></pre>

Finally, we need to sum the times spent at each tariff rate, and take that time (in seconds) and multiply by the rate charged. Since the result of SUM(rate_end - rate_start) is an INTERVAL, not a number, I had to invoke a conversion function to convert the INTERVAL into a DECIMAL number of seconds, and that (non-standard) function is iv_seconds:

<pre><code>SELECT phone_id, called_number, tariff_code, rate_charged,
 call_start, duration,
 SUM(rate_end - rate_start) AS tariff_time,
 rate_charged * iv_seconds(SUM(rate_end - rate_start)) AS tariff_cost
 FROM call_time_tariff_times
 GROUP BY phone_id, called_number, tariff_code, rate_charged,
 call_start, duration;
</code></pre>

For the sample data, this yielded the data (where I'm not printing the phone number and called number for compactness):

<pre><code>N 0.9876 2009-02-26 15:17:19 186234 0 16:00:00 56885.760000000
O 1.2345 2009-02-26 15:17:19 186234 0 10:01:11 44529.649500000
P 2.3456 2009-02-26 15:17:19 186234 1 01:42:41 217111.081600000
</code></pre>

That's a very expensive call, but the telco will be happy with that. You can poke at any of the intermediate results to see how the answer is derived. You can use fewer temporary tables at the cost of some clarity.

For a single call, this will not be much different than running the code in VB in the client. For a lot of calls, this has the potential to be more efficient. I'm far from convinced that recursion is necessary in VB - straight iteration should be sufficient.

blocks|key|550852|text|在数据库级别执行这种计算的最大问题是，在数据库运行时，它会将资源从数据库中带走，无论是CPU方面，还是通过锁定方式获得行和表的可用性。如果您是作为批处理操作的一部分计算1,000,000关税，那么这可能会在数据库上运行很长一段时间，在此期间，您将无法将数据库用于其他任何事情。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|550853|如果您拥有该资源，则使用一个事务检索所需的所有数据，并以您选择的语言在数据库之外执行所有逻辑计算。然后插入所有结果。数据库用于存储和检索数据，它们执行的任何业务逻辑在任何时候都应保持在最低限度。虽然在某些方面非常出色，但SQL并不是处理日期或字符串的最佳语言。|550854|我怀疑您对VBA工作的看法是正确的，如果不了解更多信息，我肯定会觉得这是一个递归的问题，或者至少是一个迭代的问题。如果正确执行，递归可以成为解决问题的强大而优雅的解决方案。将数据库的资源捆绑在一起是非常罕见的。|550855|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

The big problem with performing this kind of calculation at the database level is that it takes resource away from your database while it's going on, both in terms of CPU and availability of rows and tables via locking. If you were calculating 1,000,000 tariffs as part of a batch operation, then that might run on the database for a long time and during that time you'd be unable to use the database for anything else.

If you have the resource, retrieve all the data you need with one transaction and do all the logic calculations outside the database, in a language of your choice. Then insert all the results. Databases are for storing and retrieving data, and any business logic they perform should be kept to an absolute bare minimum at all times. Whilst brilliant at some things, SQL isn't the best language for date or string manipulation work.

I suspect you're already on the right lines with your VBA work, and without knowing more it certainly feels like a recursive, or at least an iterative, problem to me. When done correctly recursion can be a powerful and elegant solution to a problem. Tying up the resources of your database very rarely is.

blocks|key|2079806|text|kar_vasile(id,vid,datein,timein,timeout,bikari,tozihat)
{
---+the+bikari+field+is+unemployment+time++you+can+delete+any+where
select
++++++++++++id,
++++++++++++vid,
++++++++++++datein,
++++++++++++timein,
++++++++++++timeout,
++++++++++++bikari,
++++++++++++hourwork+=
++++++++++++case+when+
++++++++++++timein+<=+timeout
++++++++++++then
++++++++++++++++SUM+
++++++++++++(abs(DATEDIFF(mi,+timein,+timeout))+-+bikari)/60+--
++++++++++++calculate+Hour+
++++++++else
++++++++++++SUM(abs(DATEDIFF(mi,+timein,+'23:59:00:00')+%2B+DATEDIFF(mi,+'00:00:00',+timeout)+%2B+1)+-+bikari)/60+--
++++++++++++calculate
++++++++++++minute
++++++++++++++++end
++++++++++++++++,
++++++++++++++++minwork+=
++++++++++++case+when+
++++++++++++timein+<=+timeout
++++++++++++then
++++++++++++++++SUM+
++++++++++++(abs(DATEDIFF(MI,+timein,+timeout))+-+bikari)%2560++--
++++++++++++calclate+Hour+
++++++++++++starttime+is+later
++++++++++++than+endtime+
++++++++else
++++++++++++SUM(abs(DATEDIFF(mi,+timein,+'23:59:00:00')+%2B+DATEDIFF(mi,+'00:00:00',+timeout)+%2B+1)+-+bikari)%2560--
++++++++++++calculate+minute+
++++++++++++starttime+is+later
++++++++++++than
++++++++++++endtime
++++++++++++++++end,+tozihat+

++++++++++++from+kar_vasile+
++++++++++++group
++++++++++++by+id,+vid,+datein,+timein,+timeout,+tozihat,+bikari
}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2079807|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>kar_vasile(id,vid,datein,timein,timeout,bikari,tozihat)
{
--- the bikari field is unemployment time you can delete any where
select
 id,
 vid,
 datein,
 timein,
 timeout,
 bikari,
 hourwork =
 case when 
 timein &lt;= timeout
 then
 SUM 
 (abs(DATEDIFF(mi, timein, timeout)) - bikari)/60 --
 calculate Hour 
 else
 SUM(abs(DATEDIFF(mi, timein, '23:59:00:00') + DATEDIFF(mi, '00:00:00', timeout) + 1) - bikari)/60 --
 calculate
 minute
 end
 ,
 minwork =
 case when 
 timein &lt;= timeout
 then
 SUM 
 (abs(DATEDIFF(MI, timein, timeout)) - bikari)%60 --
 calclate Hour 
 starttime is later
 than endtime 
 else
 SUM(abs(DATEDIFF(mi, timein, '23:59:00:00') + DATEDIFF(mi, '00:00:00', timeout) + 1) - bikari)%60--
 calculate minute 
 starttime is later
 than
 endtime
 end, tozihat 

 from kar_vasile 
 group
 by id, vid, datein, timein, timeout, tozihat, bikari
}
</code></pre>

For a call-rating system, I'm trying to split a telephone call duration into sub-durations for different tariff-periods. The calls are stored in a SQL Server database and have a starttime and total duration. Rates are different for night (0000 - 0800), peak (0800 - 1900) and offpeak (1900-235959) periods. 

For example:
A call starts at 18:50:00 and has a duration of 1000 seconds. This would make the call end at 19:06:40, making it 10 minutes / 600 seconds in the peak-tariff and 400 seconds in the off-peak tariff. 

Obviously, a call can wrap over an unlimited number of periods (we do not enforce a maximum call duration). A call lasting > 24 h can wrap all 3 periods, starting in peak, going through off-peak, night and back into peak tariff. 

Currently, we are calculating the different tariff-periods using recursion in VB. We calculate how much of the call goes in the same tariff-period the call starts in, change the starttime and duration of the call accordingly and repeat this process till the full duration of the call has been reach (peakDuration + offpeakDuration + nightDuration == callDuration). 

Regarding this issue, I have 2 questions:

<ul>
<li>Is it possible to do this effectively in a SQL Server statement? (I can think of subqueries or lots of coding in stored procedures, but that would not generate any performance improvement)</li>
<li>Will SQL Server be able to do such calculations in a way more resource-effective than the current VB scripts are doing it?</li>
</ul>

Calculating different tariff-periods for a call in SQL Server

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

对于呼叫评级系统，我试图将电话通话的持续时间划分为不同收费期的次持续时间。调用存储在Server数据库中，并具有启动时间和总持续时间。夜间(0000 - 0800)、高峰(0800-1900)和非高峰(1900-235959)期间的费率不同。例如:呼叫从18:50:00开始，持续时间为1000秒。这样的话，通话就会在1...

问计算Server中调用的不同关税期
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问计算Server中调用的不同关税期EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问计算Server中调用的不同关税期
EN