拟上传明细

select a.YiLiaoDH, b.YiBaoBM,c.HiYaoming,b.YiYuanBM,b.YaoPinMC, b.ShouFeiLBYB, c.YaoPinLB, c.ZhenLiaoDJ,
   '' as JiXing, '' as GuiGe,c.ChanDi,b.DanWei,c.YongFa,b.DanJia, b.ShuLiang, b.ZiFuBL, b.ZongFY, b.ZiFeiFY,
    b.ZiLiFY,b.YiBaoFY, a.JiaoYiSJ
  from Z_Reg_Charge a, Z_Reg_ChargeDetail b, vZ_Dict_AllItem c
  where a.DanJuID=b.DanJuID and a.YiLiaoDH=b.YiLiaoDH and b.YiYuanBM=c.YaoPinID
  and convert(varchar(10),JiaoYiSJ,120) between '2008-04-17' and '2008-04-17'
  and TransFlag=1
  union all
  select a.YiLiaoDH, b.YiBaoBM,c.HiYaoming,
  YiYuanBM=(case when c.yaopinjx='复方（甲类）' and FuFangBZ=0 then c.YaoPinID+'1'
             when c.yaopinjx='复方（甲类）' and FuFangBZ=1 then c.YaoPinID+'2' else c.YaoPinID end),
  b.YaoPinMC, b.ShouFeiLBYB,c.YaoPinLB, c.ZhenLiaoDJ,
  b.JiXing, b.GuiGe,c.ChanDi,b.DanWei,
  YongFa=(case when (d.PinCi is not null and d.YongLiang is not null) then (e.PinCiMC+',每次'+d.YongLiang) else '' end),
  b.DanJia, b.ShuLiang, b.ZiFuBL, b.ZongFY, b.ZiFeiFY,
  b.ZiLiFY,b.YiBaoFY, a.JiaoYiSJ
  from Z_Out_Clinic a inner join ( select sum(ShuLiang) as ShuLiang,sum(ZongFY) as ZongFY,sum(ZiFeiFY) as ZiFeiFY,
  sum(ZiLiFY) as ZiLiFY,sum(YiBaoFY) as YiBaoFY,SerialNo,YiLiaoDH,DanJuID,YiYuanBM,YiBaoBM,
  YaoPinMC,ShouFeiLBYB,JiXing,GuiGe,DanWei,DanJia,ZiFuBL,FuFangBZ from Z_Out_ClinicDetail
  where TransFlag=1
  group by SerialNo,YiLiaoDH,DanJuID,YiYuanBM,YiBaoBM,
  YaoPinMC,ShouFeiLBYB,JiXing,GuiGe,DanWei,DanJia,ZiFuBL,FuFangBZ) b on a.DanJuID=b.DanJuID and a.YiLiaoDH=b.YiLiaoDH
  inner join vZ_Dict_AllItem c on b.YiYuanBM=c.YaoPinID
  inner join Z_Out_ClinicDetailExt d on b.SerialNo=d.SerialNo and b.YiYuanBM=d.YaoPinID
  left outer join Dict_Sys_Channel e on d.PinCi=e.PinCiBM
  where convert(varchar(10),JiaoYiSJ,120) between '2008-04-17' and '2008-04-17'