UDF Function
-- =============================================
-- Author: Albert He
-- Create date: 2018-05-18
-- Description: make up columns for Automation Report
-- example: dbo.ufn_AR_makeupColumns('2017-08','2018-03','cast(sum([%1]) as varchar) [%1],',0)
-- =============================================
ALTER function [dbo].[ufn_AR_makeupColumnsTemp](@startMon varchar(20),@endMon varchar(20),@template varchar(max), @monthName bit=1) returns varchar(max) as
begin
declare @i int, @sql varchar(max)='';
declare @startYr int,@endYr int,@yr char(2),@mon int;
declare @fld varchar(max)
select @startYr=left(@startMon,4),@endYr=left(@endMon,4), @i = @startYr*12+right(@startMon,2),@yr=right(@startYr,2)
while @i <= @endYr*12+right(@endMon,2) --@endYr=2018,@endMon=18-06
begin
set @mon = (@i-@startYr*12-1)%12+1
set @fld = case when @monthName=1 then @yr+'-'+left(DateName(month, DateAdd(month, @mon, -1)),3) else @yr+'-'+ case when @mon<10 then '0' else '' end+cast(@mon as varchar) end
set @sql += cast(replace(@template,'%1',@fld) as varchar(max))
set @i+=1;
if @mon%12=0 set @yr=cast(cast(@yr as int)+1 as varchar)
end
return @sql
end
Usage - definition
@sql+='create table #t0 (LOB varchar(50),PROCESS_NAME varchar(255),seq varchar(255)'+dbo.ufn_AR_makeupColumns('2017-01','2018-12',',[%1] float',0)+',Total float)
Result - definition
create table #t0 (LOB varchar(50),PROCESS_NAME varchar(255),seq varchar(255),[17-01] float,[17-02] float,[17-03] float,[17-04] float,[17-05] float,[17-06] float,[17-07] float,[17-08] float,[17-09] float,[17-10] float,[17-11] float,[17-12] float,[18-01] float,[18-02] float,[18-03] float,[18-04] float,[18-05] float,[18-06] float,[18-07] float,[18-08] float,[18-09] float,[18-10] float,[18-11] float,[18-12] float,Total float)
Usage - column list
select @strColumns = dbo.ufn_AR_makeupColumns('2017-01','2018-12',',[%1]',0)
Result - column list
,[17-01],[17-02],[17-03],[17-04],[17-05],[17-06],[17-07],[17-08],[17-09],[17-10],[17-11],[17-12],[18-01],[18-02],[18-03],[18-04],[18-05],[18-06],[18-07],[18-08],[18-09],[18-10],[18-11],[18-12]
Comments