declare @yr int,@mth int,@logistics_code varchar(30),@fee_type varchar(50) select @yr=2014,@mth=1,@logistics_code='All',@fee_type='All' declare @index int,@from_date datetime,@to_date datetime,@strDateList varchar(180),@strSql varchar(1000)
select @index=1,@strDateList='',@from_date=cast((ltrim(@yr)+'-'+ltrim(@mth)+'-1') as datetime)
set @to_date=@from_date
while @index<=12
begin
set @strDateList=@strDateList+'['+ltrim(year(@to_date))+'/'+ltrim(month(@to_date))+'],'
set @to_date=dateadd(mm,1,@to_date)
set @index=@index+1
end create table #tbl
(
ymth varchar(20),
logistics_code varchar(30),
fee money
) insert into #tbl(ymth,logistics_code,fee)
select ltrim(yr)+'/'+ltrim(mth),logistics_code,sum(fee)
from tbl_lis_FeeList where status='A'
and
(
(yr=year(@from_date) and mth>=month(@from_date))
or (yr=year(@to_date) and mth<=month(@to_date))
)
and logistics_code=case when @logistics_code<>'All' then @logistics_code else logistics_code end
and fee_type=case when @fee_type<>'All' then @fee_type else fee_type end
group by ltrim(yr)+'/'+ltrim(mth),logistics_code set @strDateList=substring(@strDateList,1,len(@strDateList)-1)
set @strSql='select b.customer_name,'+@strDateList+' from
(
select logistics_code,'+@strDateList+' from #tbl pivot(sum(fee) for ymth in('+@strDateList+')) as t
) as temp
left join tbl_lis_LogisticsProvider b on temp.logistics_code=b.logistics_code' exec (@strSql) /*
select customer_name,temp.* from (
select logistics_code,[2014/1],[2014/2],[2014/3],[2014/4],[2014/5],[2014/6],[2014/7],[2014/8],[2014/9],[2014/10],[2014/11],[2014/12]
from #tbl
pivot(sum(fee) for ymth in([2014/1],[2014/2],[2014/3],[2014/4],[2014/5],[2014/6],[2014/7],[2014/8],[2014/9],[2014/10],[2014/11],[2014/12])) as t
) as temp
left join tbl_lis_LogisticsProvider b on temp.logistics_code=b.logistics_code
*/ drop table #tbl
GO
