Oracle 日期时间的转换获取//存储过程执行插入数据
< 返回列表时间: 2020-05-13来源:OSCHINA
【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里?>>>
Oracle时间转换 select DeptCode,IHCount from view_tobis_zycount where 1=1 and 执行时间 between to_date(:DateBegin,'yyyy-MM-dd hh24:mi:ss') and to_date(:DateEnd,'yyyy-MM-dd hh24:mi:ss') and rownum <100
select t.column_name from user_col_comments t where t.table_name = 'BIZ_DICT_XB'; select * from v_pt_收入分析 where 日期 between to_date(sysdate-30,'yyyy-mm-dd') and to_date(sysdate,'yyyy-mm-dd'); select 类别,收入分类 ,sum(金额) from v_pt_收入分析 where 日期 between sysdate-30 and sysdate group by 类别,收入分类 //注:分类列一定要记全,除了统计列的其他 select extract(year from sysdate) as 今年 from dual --今年 select ( to_char(sysdate-30,'YYYY/MM')) as 年月 from dual --今年上个月 select to_char(sysdate,'MM')-1 as 月份 from dual --本月 ADD_MONTHS(sysdate, -12) --去年今天 v_pt_门诊人次(每一天的每个科室每种号别的门诊人次) v_pt_住院人次(每一天的每个科室入院数/出院数/现人数等) v_pt_收入分析(每一天的门诊/住院 每个科室每种收入分类的金额)


一、最近一个月的“在院人次”、“床位使用率”、“药品比例” select sysdate-30 as 起始日期,sysdate as 截止日期,'在院人次' as 统计项目,count( 在院人数) as 值 from v_pt_住院人次 where 日期 between sysdate-30 and sysdate union all select sysdate-30 as 起始日期,sysdate as 截止日期,'床位使用率' as 统计项目,(count( 实际占用床位数)/count(实际开放床位数)) as 值 from v_pt_住院人次 where 日期 between sysdate-30 and sysdate union all select distinct sysdate-30 as 起始日期,sysdate as 截止日期,'药品比例' as 统计项目,round((select count(收入分类) from v_pt_收入分析 where 收入分类='西药费' or 收入分类='中成药' ) / (select count(收入分类) from v_pt_收入分析),3) as 值 from v_pt_收入分析 where 日期 between sysdate-30 and sysdate

二、最近3个月的“门诊人次”和前一年的相同月份的“门诊人次” select extract(year from sysdate) as 年份,(to_char(sysdate,'MM')-1) as 月份,'门诊人次' as 统计项目,count(门诊人次) from v_pt_门诊人次 where to_char(日期,'YYYY/MM') = to_char(sysdate-30,'YYYY/MM') union all select extract(year from sysdate) as 年份,(to_char(sysdate,'MM')-2) as 月份,'门诊人次' as 统计项目,count(门诊人次) from v_pt_门诊人次 where to_char(日期,'YYYY/MM') = to_char(sysdate-60,'YYYY/MM') union all select extract(year from sysdate) as 年份,(to_char(sysdate,'MM')-3) as 月份,'门诊人次' as 统计项目,count(门诊人次) from v_pt_门诊人次 where to_char(日期,'YYYY/MM') = to_char(sysdate-90,'YYYY/MM') union all select extract(year from sysdate)-1as 年份,(to_char(ADD_MONTHS(sysdate, -12),'MM')-1) as 月份,'门诊人次' as 统计项目,count(门诊人次) from v_pt_门诊人次 where to_char(日期,'YYYY/MM') = to_char(ADD_MONTHS(sysdate, -12),'YYYY/MM') union all select extract(year from sysdate)-1 as 年份,(to_char(ADD_MONTHS(sysdate, -12),'MM')-2) as 月份,'门诊人次' as 统计项目,count(门诊人次) from v_pt_门诊人次 where to_char(日期,'YYYY/MM') = to_char(ADD_MONTHS(sysdate, -13),'YYYY/MM') union all select extract(year from sysdate)-1 as 年份,(to_char(ADD_MONTHS(sysdate, -12),'MM')-3) as 月份,'门诊人次' as 统计项目,count(门诊人次) from v_pt_门诊人次 where to_char(日期,'YYYY/MM') = to_char(ADD_MONTHS(sysdate, -14),'YYYY/MM')

三、最近一个月的“门诊收入” select sysdate-30 as 起始日期,sysdate as 截止日期,收入分类 as 统计项目,count(金额) from v_pt_收入分析 where 类别='门诊' and 日期 between sysdate-30 and sysdate group by 收入分类
五、最近一个月的“住院收入” select sysdate-30 as 起始日期,sysdate as 截止日期,收入分类 as 统计项目,count(金额) from v_pt_收入分析 where 类别='住院' and 日期 between sysdate-30 and sysdate group by 收入分类


四、最近3个月的“出院人数”和前一年的相同月份的“出院人数” select extract(year from sysdate) as 年份,(to_char(sysdate,'MM')-1) as 月份,'出院人数' as 统计项目,count(出院人数) from v_pt_住院人次 where to_char(日期,'YYYY/MM') = to_char(sysdate-30,'YYYY/MM') union all select extract(year from sysdate) as 年份,(to_char(sysdate,'MM')-2) as 月份,'出院人数' as 统计项目,count(出院人数) from v_pt_住院人次 where to_char(日期,'YYYY/MM') = to_char(sysdate-60,'YYYY/MM') union all select extract(year from sysdate) as 年份,(to_char(sysdate,'MM')-3) as 月份,'出院人数' as 统计项目,count(出院人数) from v_pt_住院人次 where to_char(日期,'YYYY/MM') = to_char(sysdate-90,'YYYY/MM') union all select extract(year from sysdate)-1as 年份,(to_char(ADD_MONTHS(sysdate, -12),'MM')-1) as 月份,'出院人数' as 统计项目,count(出院人数) from v_pt_住院人次 where to_char(日期,'YYYY/MM') = to_char(ADD_MONTHS(sysdate, -12),'YYYY/MM') union all select extract(year from sysdate)-1 as 年份,(to_char(ADD_MONTHS(sysdate, -12),'MM')-2) as 月份,'出院人数' as 统计项目,count(出院人数) from v_pt_住院人次 where to_char(日期,'YYYY/MM') = to_char(ADD_MONTHS(sysdate, -13),'YYYY/MM') union all select extract(year from sysdate)-1 as 年份,(to_char(ADD_MONTHS(sysdate, -12),'MM')-3) as 月份,'出院人数' as 统计项目,count(出院人数) from v_pt_住院人次 where to_char(日期,'YYYY/MM') = to_char(ADD_MONTHS(sysdate, -14),'YYYY/MM')


存储过程执行插入数据 begin execute zl_人员表_新增( 人员表_ID.nextval ,:编号 , :姓名 , '', :身份证号 , To_Date( :出生日期,'yyyy-MM-dd') , :性别 , '' , null , :联系电话 , '' , '' , '' , '' , '' , '' , '' , '' , 0, '', '' , '' , '' , '554', '' , '' , '' , '' , '' , '' , '' , '' , '' , To_Number( :联系电话 ) , 0, Sysdate ); end;
热门排行