17,377
社区成员
发帖
与我相关
我的任务
分享
select c.编码||b.名称 as 药品名称,
b.规格,
decode (/*B3*/1/*E3*/,1,c.售价单位,2,c.门诊单位,3,c.住院单位,4,c.药库单位,c.售价单位) as 单位,
g.实际数量/decode(/*B3*/1/*E3*/,2,c.门诊包装,3,c.住院包装,4,c.药库包装,1) as 实际数量,
to_char(g.零售价,'9999990.00') as 零售价 ,
to_char(g.零售金额,'9999990.00') as 零售金额
from (select 药品id,sum(实际数量) as 实际数量,avg(零售价) as 零售价,sum(零售金额) as 零售金额 from 药品收发记录 group by 药品id) g ,
药品收发记录 a,收费项目目录 b, 药品目录 c
where b.id = a.药品id
and g.药品id = a.药品id
and c.药品id = a.药品id
and a.审核日期 between /*B0*/TO_DATE('2015-12-01 00:00:00','YYYY-MM-DD HH24:MI:SS')/*E0*/ and /*B1*/TO_DATE('2016-01-14','YYYY-MM-DD')/*E1*/
and a.单据 in (8,9,10) and a.发药方式 in (1,2,3)
and a.库房id /*B2*/is not null/*E2*/
select
a.编码||a.名称 as 药品名称,a.规格,
gg.实际数量/decode (/*B3*/1/*E3*/,2,a.门诊包装,3,a.住院包装,4,a.药库包装,1) as 实际数量,
decode(/*B3*/1/*E3*/,1,a.售价单位,2,a.门诊单位,3,a.住院单位,4,a.药库单位,a.售价单位) as 单位,
gg.零售价,gg.零售金额
from
(select
药品id ,库房id,
sum(实际数量) as 实际数量,
avg(零售价) as 零售价,
sum(零售金额) as 零售金额
from
药品收发记录
where
单据 in (8,9,10) and 发药方式 in (1,2,3)
and 审核日期 between /*B1*/TO_DATE('2015-12-01 00:00:00','YYYY-MM-DD HH24:MI:SS')/*E1*/ and /*B2*/TO_DATE('2016-01-14 23:59:59','YYYY-MM-DD HH24:MI:SS')/*E2*/
group by 药品id,库房id) GG,药品目录 a , 部门表 b
where
b.id /*B0*/is not null/*E0*/ and a.药品id = gg.药品id and b.id=gg.库房id