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

EN

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);

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;

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);

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;

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
);

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;

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;

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;

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;

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;

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

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') + DATEDIFF(mi, '00:00:00', timeout) + 1) - bikari)/60 --
            calculate
            minute
                end
                ,
                minwork =
            case when 
            timein <= 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
}

CREATE TABLE rates (
    from_date_time DATETIME
,   to_date_time DATETIME
,   rate MONEY
)

CREATE TABLE calls (
    id INT
,   started DATETIME
,   ended DATETIME
)

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