Select distinct t1.日期, xm.bh,xm.mc,fy.编码,fy.名称,t1."数量",t1."金额" ,t2."cb数量1",t2.cb数量2,t2.cb金额1+t3.sq金额1 金额合计,t2.cb金额2+t3.sq金额2 累计金额,t4.sp金额1,t5.fk金额1,t4.sp金额2,t5.fk金额2,t1.计量产值,t1.施工产值,t1.合同金额,t1.目标利润率
from 
(select field0005 bh,field0007 mc from formmain_0190) xm 
 left join 
/*项目预算表*/
(select distinct a.field0008"合同金额", a.field0033 计量产值, a.field0034 施工产值, a.field0009 目标利润率, convert(varchar(7),a.field0004,120)"日期", a.field0005"项目编码",a.field0006"项目名称",b.field0028"费用编码",b.field0018"费用类型",sum(b.field0019)over(partition by a.field0005,a.field0006,b.field0028,b.field0018,convert(varchar(7),a.field0004,120) order by convert(varchar(7),a.field0004,120))"数量",sum(b.field0021)over(partition by a.field0005,a.field0006,b.field0028,b.field0018,convert(varchar(7),a.field0004,120))"金额"
 from formmain_0309 a left join formson_0312 b on  a.id=b.formmain_id 
where a.finishedflag=1
) "t1"  on xm.bh=t1.项目编码 
 full join
( select field0007 编码,field0004"名称" from formmain_0275) fy on fy.编码=t1.费用编码
full join 
 /*成本过程结算*/
 (select distinct convert(varchar(7),a.field0004,120)"日期1", a.field0015"项目编码1",a.field0016"项目名称1",b.field0035"费用编码1",b.field0036"费用名称1",sum(b.field0038)over(partition by convert(varchar(7),a.field0004,120), a.field0015,a.field0016,b.field0035,b.field0036) "cb数量1",
  sum(b.field0038)over(partition by a.field0015,a.field0016,b.field0035,b.field0036 order by convert(varchar(7),a.field0004,120) ) "cb数量2",
sum(b.field0040)over(partition by convert(varchar(7),a.field0004,120), a.field0015,a.field0016,b.field0035,b.field0036) "cb金额1",
  sum(b.field0040)over(partition by a.field0015,a.field0016,b.field0035,b.field0036 order by convert(varchar(7),a.field0004,120) ) "cb金额2"
from formmain_0316 a left join  formson_0318 b on a.id=b.formmain_id where a.finishedflag=1)"t2" 
on  xm.bh=t2."项目编码1" and fy.编码=t2.费用编码1
full join
/*项目付款申请*/
(select distinct convert(varchar(7),a.field0005,120)"日期2",a.field0006"项目编码2",a.field0007"项目名称2",b.field0093"费用编码2",b.field0062"费用类型2",sum(b.field0050)over(partition by convert(varchar(7),a.field0005,120),a.field0006,b.field0093)"sq金额1",
 sum(b.field0050)over(partition by a.field0006,a.field0007,b.field0093,b.field0062 order by convert(varchar(7),a.field0004,120) )"sq金额2"
from formmain_0360 a left join formson_0361 b on a.id=b.formmain_id where a.finishedflag=1)"t3" 
on   xm.bh=t3.项目编码2 and fy.编码=t3.费用编码2
full join 
/*收票登记*/
(select distinct convert(varchar(7),a.field0009,120)"日期3",a.field0010"项目编码3",a.field0011"项目名称3",b.field0019"费用编码3",b.field0018"费用类型3",sum(b.field0015) over (partition by convert(varchar(7),a.field0009,120),a.field0010,a.field0011,b.field0019,b.field0018)"sp金额1" ,
sum(b.field0015) over (partition by a.field0010,a.field0011,b.field0019,b.field0018  order by convert(varchar(7),a.field0009,120))"sp金额2" 
from formmain_0295 a left join formson_0296 b on a.id=b.formmain_id where a.finishedflag=1)"t4"
on   xm.bh=t4.项目编码3 and fy.编码=t4.费用编码3
full join 
/*项目付款*/
(select distinct convert(varchar(7),a.field0005,120)"日期4",a.field0006"项目编码4",a.field0007"项目名称4",b.field0093"费用编码4",b.field0062"费用名称4",sum(b.field0050)over(partition by convert(varchar(7),a.field0005,120),a.field0006,a.field0006,b.field0093,b.field0062)"fk金额1",sum(b.field0050)over(partition by convert(varchar(7),a.field0005,120),a.field0006,b.field0093 order by convert(varchar(7),a.field0009,120))"fk金额2"
 from formmain_0380 a left join formson_0381 b on a.id=b.formmain_id where a.finishedflag=1)"t5"
 on xm.bh=t5."项目编码4" and fy.编码=t5.费用编码4