How to calculate call cost with an SQL query for Billing

Has anyone or does anyone know how to calculate call charges from a call log table and tariff table in sql and put the output in a new table???

tariff rates for seconds or minutes.

Could you provide some sample queries?

Here is the code that I used to create the table with field names:

CREATE TABLE cdr (
accountcode VARCHAR (30),
src VARCHAR(64),
dst VARCHAR(64),
dcontext VARCHAR(32),
clid VARCHAR(32),
channel VARCHAR(32),
dstchannel VARCHAR(32),
lastapp VARCHAR(32),
lastdata VARCHAR(64),
calldate TIMESTAMP NOT NULL,
answerdate TIMESTAMP NOT NULL,
hangupdate TIMESTAMP NOT NULL,
duration INT(8) UNSIGNED DEFAULT NULL,
billsec INT(8) UNSIGNED DEFAULT NULL,
disposition VARCHAR(32),
amaflags VARCHAR(128),
PRIMARY KEY (clid,channel,calldate)
);

and for the tariff rates, peak and off peak I have:

CREATE TABLE tariffs (ratestart TIME, rateend TIME, ratecharged DECIMAL(10,8));

INSERT INTO tariffs(ratestart, rateend, ratecharged)
    VALUES("08:00:00", "19:00:00", 3.3333);
INSERT INTO tariffs(ratestart,rateend,ratecharged)
    VALUES("19:00:00", "08:00:00", 1.5555);

I would like to have the output of the cost of the call开发者_JS百科 made into a new table with callerid, Source, Destination, Call duration, tariff and cost of the call.

Also should I create another table with a list of users? And also how would anyone be charged off peak during weekends?

You should simplify your life by inserting more data in the tariff table:

INSERT INTO tariffs(ratestart, rateend, ratecharged)
    VALUES("00:00:00", "08:00:00", 1.5555);
INSERT INTO tariffs(ratestart, rateend, ratecharged)
    VALUES("08:00:00", "19:00:00", 3.3333);
INSERT INTO tariffs(ratestart, rateend, ratecharged)
    VALUES("19:00:00", "24:00:00", 1.5555);

This makes it a bit simpler to handle calls by separating the early morning and late night rates. (You might need to use '23:59:59' in place of '24:00:00' if you can't store midnight like that, but then you need to adjust the other end times to 1 second before the transition, and you modify queries to use '<= rateend' instead of '< rateend'.)

Do you have to worry about calls that overlap two tariff periods? What about calls that last more than a whole tariff period? More than 24 hours?

You should also translate terms you use. For example, you request:

with callerid, Source, Destination, Call duration, tariff and cost of the call

• CallerID ⟶ clid
• Source ⟶ src
• Destination ⟶ dst
• Call duration ⟶ duration
• Tariff ⟶ ratecharged

What units are used to measure call duration? Is the charging regime simply the rate that applied when the call started, so someone calling at 07:59:59 for 12 hours gets to talk at the night rate? How does call duration differ from billing seconds? Should the calculation use billing seconds?

You might also like to look at a previous question that was very similar to this one:

• Calculating different tariff-periods for a call in SQL Server