怎么计算停车收费

Question

停车类型 s1   驶入时间 srsj  开出时间 kcsj  计费charge    
 
7点--21点     2元/每15分钟                     不够15分钟的按15分钟计算           
21点--次日7点   1.5元/每2小时                  不够2小时的按2小时计算     
有些停好几天的，这个charge=多少啊，怎么计算啊   
                                                 

Answer 1

charge：=就是把2个时间段停的时间算出来*价格 加起来，不会时间运算啊

Answer 2

--写个函数，传入SRSJ和KCSJ，返回应结算金额
--以下代码可参考
SELECT SRSJ,KCSJ,
       TO_NUMBER(TO_CHAR(SRSJ,'HH24')) HOUR,
       CASE WHEN TO_NUMBER(TO_CHAR(SRSJ,'HH24')) BETWEEN 7 AND 20 THEN 'DAY' ELSE 'NIGTH' END DATE_TYPE,
       (KCSJ - SRSJ)*24*60*60 SECOND_DIFF,
       CASE WHEN TO_NUMBER(TO_CHAR(SRSJ,'HH24')) BETWEEN 7 AND 20 
                THEN TO_DATE(TO_CHAR(TRUNC(SRSJ,'DD'),'YYYY-MM-DD')||'21:00:00','YYYY-MM-DD HH24:MI:SS')
            WHEN TO_NUMBER(TO_CHAR(SRSJ,'HH24')) <= 6
                THEN TO_DATE(TO_CHAR(TRUNC(SRSJ,'DD'),'YYYY-MM-DD')||'07:00:00','YYYY-MM-DD HH24:MI:SS')
            ELSE TO_DATE(TO_CHAR(TRUNC(SRSJ,'DD')+1,'YYYY-MM-DD')||'07:00:00','YYYY-MM-DD HH24:MI:SS') 
       END NEXT_DATE_TYPE
FROM A_TABLE;

Answer 3

自己寫的 可能會有錯誤 參考著看吧 
create or replace procedure tb_proce_TingCheFei
(
    date_1 date,
    date_5 date
)
as
hour_1 number;
hour_2 number;
date_3 date;
date_4 date;
date_2 date;
money  number;
begin
        money:=0;
        date_2:=date_5;
        if date_2-date_1>=1 then
            money:=round(date_2-date_1-0.499)*119.5;
            date_2:=date_2-round(date_2-date_1-0.499);
        end if;
        hour_1:=to_number(to_char(date_1,'HH24'));
        hour_2:=to_number(to_char(date_2,'HH24'));
        date_3:=TO_DATE(TO_CHAR(TRUNC(date_1,'DD'),'YYYY-MM-DD')||'21:00:00','YYYY-MM-DD HH24:MI:SS');
        date_4:=TO_DATE(TO_CHAR(TRUNC(date_2,'DD'),'YYYY-MM-DD')||'07:00:00','YYYY-MM-DD HH24:MI:SS');
        
        if hour_1>=7 and hour_2<=20 and hour_1<=20 then
            money:=Round((date_2-date_1)*24*60/15+0.499)*2;
        elsif hour_1>20 and hour_2<=7 then
            money:=Round((date_2-date_1)*24/2+0.499)*1.5;
        elsif hour_1<=20 and hour_1>=7 then
            money:=money+Round((date_3-date_1)*24*60/15+0.499)*2;
            money:=money+Round((date_2-date_3)*24/2+0.499)*1.5;
        elsif hour_1<7 or hour_1>20 then
            money:=money+Round((date_4-date_1)*24/2+0.499)*1.5;
            money:=money+Round((date_2-date_4)*24*60/15+0.499)*2;
        end if;
    dbms_output.put_line(money);
end;