经济指标表公式

select c.*,d.OrderThisYearAcc,d.CapReturnThisYearCum,d.CapSpentThisYearCum,d.OutputThisYearCum,d.SalesThisYearCum,a.OrderTruAcc,a.CapReturnTruCum,a.OutputTruCum,a.SalesTruCum,a.AccTruCum,a.CapSpentTruCum,b.OrderPlanValue,b.CapReturnPlanValue,b.CapSpentPlanValue,b.OutputPlanValue,b.SalesPlanValue,b.AccPlanValue,b.AccOverPlanValue,b.AccLastMonthPlanValue,b.AccLastMonthtruValue,b.ChunHuoLastMonthPlanValue,b.ChunHuoLastMonthTruValuefrom  (select a.Department, sum(isnull(OrderTruValue,0)) OrderTruAcc,       SUM(ISNULL(CapReturnTruValue,0)) CapReturnTruCum,       SUM(ISNULL(OutputTruValue,0)) OutputTruCum,       SUM(ISNULL(SalesTruValue,0)) SalesTruCum,       sum(isnull(AccTruValue,0)) AccTruCum,       sum(isnull(CapSpentTruValue,0)) CapSpentTruCum            from dbo.IndicatorsInput ainner join dbo.IndicatorsInputItme b       on  a.MainID =b.MainIDinner join JHOA_Approve con b.MainID=c.AppO_Values and c.App_Over=1  andc.App_IdeaFlag=9 and c.Del_flag=0--[@  and  a.year=:year @] [@ and a.Month<=:month @]and a.year=2012 and a.month=2       group by a.Department) aright join(select a.Department,  sum(ISNULL(OrderPlanValue,0)) OrderPlanValue,  SUM(ISNULL(CapReturnPlanValue,0)) CapReturnPlanValue,  SUM(ISNULL(CapSpentPlanValue,0)) CapSpentPlanValue,  SUM(ISNULL(OutputPlanValue,0)) OutputPlanValue,  SUM(ISNULL(SalesPlanValue,0)) SalesPlanValue,  SUM(ISNULL(AccPlanValue,0)) AccPlanValue,  sum(isnull(AccOverPlanValue,0)) AccOverPlanValue,  sum(isnull(AccLastMonthPlanValue,0)) AccLastMonthPlanValue,sum(isnull(AccLastMonthTruValue,0)) AccLastMonthTruValue,sum(isnull(ChunHuoLastMonthPlanValue,0)) ChunHuoLastMonthPlanValue,sum(isnull(ChunHuoLastMonthTruValue,0)) ChunHuoLastMonthTruValue  from dbo.IndicatorsInput ainner join dbo.IndicatorsInputItme b  on  a.MainID =b.MainIDinner join JHOA_Approve con b.MainID=c.AppO_Values and c.App_Over=1  andc.App_IdeaFlag=9 and c.Del_flag=0--[@ and a.year=:year @] [@ and case :month of 1 : a.month=1 else a.month=:month-1  @]and a.year=2012 and a.month=2  group by a.Department ) bon a.Department=b.Departmentright join(select a.Department,   SUM(isnull(OrderTruValue,0)) OrderTruValue,   sum(isnull(OrderNextMonPlan,0)) OrderNextMonPlan,   sum(isnull(CapReturnNextMonPlan,0)) CapReturnNextMonPlan,   sum(isnull(CapReturnTruValue,0)) CapReturnTruValue,   sum(isnull(CapSpentNextMonPlan,0)) CapSpentNextMonPlan,   sum(isnull(CapSpentTruValue,0)) CapSpentTruValue,   sum(isnull(OutputTruValue,0)) OutputTruValue,   sum(isnull(OutputNextMonPlan,0)) OutputNextMonPlan,   sum(isnull(SalesNextMonPlan,0)) SalesNextMonPlan,   sum(isnull(SalesTruValue,0)) SalesTruValue,   sum(isnull(AccNextMonPlan,0)) AccNextMonPlan,   sum(isnull(AccTruValue,0)) AccTruValue,   sum(isnull(AccOverTruValue,0)) AccOverTruValue,   sum(isnull(AccZZTSPlanValue,0)) AccZZTSPlanValue,   sum(isnull(AccZZTSTruValue,0)) AccZZTSTruValue,sum(isnull(ChunHuoPlanValue,0)) ChunHuoPlanValue,sum(isnull(ChunHuoTruValue,0)) ChunHuoTruValue,sum(isnull(ChunHuoljxscbPlanValue,0)) ChunHuoljxscbPlanValue,sum(isnull(ChunHuoljxscbTruValue,0)) ChunHuoljxscbTruValue,sum(isnull(ChunHuoljzztsPlanValue,0)) ChunHuoljzztsPlanValue,sum(isnull(ChunHuoljzztsTruValue,0)) ChunHuoljzztsTruValue   from dbo.IndicatorsInput ainner join dbo.IndicatorsInputItme b   on  a.MainID =b.MainIDinner join JHOA_Approve con b.MainID=c.AppO_Values and c.App_Over=1  andc.App_IdeaFlag=9 and c.Del_flag=0-- [@ and a.year=:year @] [@  and a.month=:month @]and a.year=2012 and a.month=2  group by a.Department) c   on b.Department=c.Department   left join   (select a.Department,   sum(isnull(OrderThisYearAcc,0)) OrderThisYearAcc,   sum(isnull(CapReturnThisYearCum,0)) CapReturnThisYearCum,   sum(ISNULL(CapSpentThisYearCum,0)) CapSpentThisYearCum,   sum(ISNULL(OutputThisYearCum,0)) OutputThisYearCum,   SUM(ISNULL(SalesThisYearCum,0)) SalesThisYearCum   from  IndicatorsInputPlanInfoByYear a   inner join IndicatorsInputPlanInfoByYearItem b   on  a.MainID =b.MainID   inner join JHOA_Approve con b.MainID=c.AppO_Values  and c.App_Over=1  andc.App_IdeaFlag=9 and c.Del_flag=0--[@ and a.year=:year @]and a.year=2012 group by a.Department) don c.Department=d.Department