报表设计解决方法

报表设计

SQL code

GSDM    KJND    JHYF    DM    JE        001    2010    1    110    50        001    2010    2    110    30        001    2011    1    110    60        001    2011    2    110    35        …                        以2011年1月查询                        GSDM    KJND    DM    本年累计    本月发生    上年本月    同比001    2011    110    60    60    50    1.2                        以2011年2月查询                        GSDM    KJND    DM    本年累计    本月发生    上年本月    同比001    2011    110    95    35    30    1.166666667

SQL code

select  gsdm,kjnd,dm,  (select sum(je) from tb where jhyf<=t.jhyf)from  tb twhere  KJND=2011 and JHYF=1
[解决办法]
SQL code
--GSDM    KJND    JHYF    DM    JE;with ach as(    select GSDM,KJND,JHYF,DM,sum(JE) as JE    from tb    group by GSDM,KJND,JHYF,DM)select t.GSDM,t.KJND,t.DM,    (select sum(JE) from ach where GSDM=t.GSDM and KJND=t.KJND and DM=t.DM) YJE,    sum(t.JE) as MJE,e.JE as LMJE,sum(t.JE)*1./e.JE as TPfrom tb t left join ach e     on t.GSDM = e.GSDM and t.KJND = e.KJND+1 and t.JHYF = e.JHYF and t.DM = e.DMgroup by t.GSDM,t.KJND,t.DM,e.JE
[解决办法]
SQL code
--sql2005declare @KJND intdeclare @JHYF intset @KJND=2011set @JHYF=2select GSDM,KJND,DM,本年累计,本月发生,上月发生,cast(本月发生 as float)/cast(上月发生 as float) 同比from(select*,sum(je) over(partition by KJND) 本年累计,sum(je) over (partition by KJND,JHYF) 本月发生,sum(case when KJND=@KJND-1 then je else 0 end ) over (partition by JHYF) 上月发生from t1) as awhere KJND=@KJND and JHYF=@JHYF
[解决办法]
SQL code
if object_id('[TB]') is not null drop table [TB]gocreate table [TB] (GSDM nvarchar(6),KJND int,JHYF int,DM int,JE int)insert into [TB]select '001',2010,1,110,50 union allselect '001',2010,2,110,30 union allselect '001',2011,1,110,60 union allselect '001',2011,2,110,35select * from [TB]DECLARE @i INT = 2010SELECT  GSDM ,        KJND ,        DM ,        [本年累计] = SUM(JE) ,        [本月发生] = SUM(CASE WHEN JHYF = DATEPART(mm, GETDATE()) THEN JE                          ELSE 0                     END) ,        [上月发生] = SUM(CASE WHEN DATEDIFF(mm, JHYF, GETDATE()) = 1                               AND KJND = @i THEN JE                          ELSE 0                     END) ,        [同比] = CONVERT(FLOAT, SUM(CASE WHEN JHYF = DATEPART(mm, GETDATE())                                       THEN JE                                       ELSE 0                                  END) * 1.0        / CASE WHEN SUM(CASE WHEN DATEDIFF(mm, JHYF, GETDATE()) = 1                                  AND KJND = @i THEN JE                             ELSE 0                        END) = 0 THEN 1               ELSE SUM(CASE WHEN DATEDIFF(mm, JHYF, GETDATE()) = 1                                  AND KJND = @i THEN JE                             ELSE 0                        END)          END)FROM    TBWHERE   KJND = @iGROUP BY GSDM ,        KJND ,        DM