经济指标表公式

最新推荐文章于 2022-10-07 14:48:22 发布

tzjly

最新推荐文章于 2022-10-07 14:48:22 发布

阅读量553

点赞数

分类专栏：数巨分析文章标签： join c

本文链接：https://blog.csdn.net/tzjly/article/details/7444583

版权

数巨分析专栏收录该内容

6 篇文章 0 订阅

订阅专栏

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.ChunHuoLastMonthTruValue



from  (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 a
inner join dbo.IndicatorsInputItme b
       on  a.MainID =b.MainID
inner join JHOA_Approve c
on b.MainID=c.AppO_Values and c.App_Over=1  and
c.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) a
right 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 a
inner join dbo.IndicatorsInputItme b
  on  a.MainID =b.MainID
inner join JHOA_Approve c
on b.MainID=c.AppO_Values and c.App_Over=1  and
c.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 ) b
on a.Department=b.Department
right 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 a
inner join dbo.IndicatorsInputItme b
   on  a.MainID =b.MainID
inner join JHOA_Approve c
on b.MainID=c.AppO_Values and c.App_Over=1  and
c.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 c
on b.MainID=c.AppO_Values  and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
--[@ and a.year=:year @]
and a.year=2012 
group by a.Department
) d
on c.Department=d.Department