Hi
The full queries are below:
declare @yr int;
declare @mdate date;
declare @pid varchar(100);
declare @t1 nvarchar(max);
declare @t2 nvarchar(max);
select @yr=year(getdate());
select @mdate=max(act_date) from dbo.tblpr_prodact;
exec sp_DBR_DOH_Prepare @yr,@inLogin;
if @inPID = 1 begin
set @pid = '64060128'
end
else
begin
set @pid = '''72420210'',''72420210'''
end;
set @t1=concat('
select iif(pid=''72420105'',''0.05MG'',iif(pid=''72420210'',''0.10MG'','''')) as "Strength[str]",
case
when cust=''XXX- Parent'' then ''ABC''
when cust=''Big 3 Totals'' then ''BIG 3''
when cust=''YYY- Parent'' then ''CH''
else ''ZZZ''
end as "[who]",
val as "DoH[doh]",
case
when cust=''XXX- Parent'' then 1
when cust=''Big 3 Totals'' then 4
when cust=''YYY- Parent'' then 2
else 3
end as "[ord]",
format(b.bdate,''MM-dd'') as "as of[adate]"
from dbo.tmp_doh_daily
left join (select act_cust as bcust,max(act_date) as bdate from dbo.tblpr_prodact group by act_cust) b on cust=b.bcust
where cust in (''XXX - Parent'',''YYY - Parent'',''ZZZ - Parent'') and iuser=''',@inLogin,''' and stype=''5Days on Hand''
and pid in (', @pid,')
order by 1,4;');
set @t2=concat('
select iif(pid=''72420105'',''0.05MG'',iif(pid=''72420210'',''0.10MG'','''')) as "Strength[str]",
case
when cust=''XXX- Parent'' then ''ABC''
when cust=''Big 3 Totals'' then ''BIG 3''
when cust=''YYY- Parent'' then ''CH''
else ''ZZZ''
end as "[who]",
val as "DoH[doh]",
case
when cust=''XXX- Parent'' then 1
when cust=''Big 3 Totals'' then 4
when cust=''YYY- Parent'' then 2
else 3
end as "[ord]"
from dbo.tmp_doh_daily
left join (select act_cust as bcust,max(act_date) as bdate from dbo.tblpr_prodact group by act_cust) b on cust=b.bcust
where cust in (''Big 3 Totals'') and iuser=''',@inLogin,''' and stype=''5Days on Hand''
and pid in (', @pid,')
order by 1,4;');
select 'dbr.title','Big 3 Days on Hand';
select 'dbr.crosstab','who';
select 'dbr.resultclass','b3 kwn';
select 'dbr.colstyle','str','[min-width:75px;text-align:right;font-weight:bold;]';
select 'dbr.colstyle','doh','[text-align:center]%0.1f';
select 'dbr.colstyle','adate','[text-align:center]';
select 'dbr.css','.b3 {font-size:15px;}';
select 'dbr.css','.b3 th {text-align:center;font-weight:bold;min-width:75px;}';
select 'dbr.css','table.b3 {margin:0;}';
select 'dbr.search',0;
select 'dbr.hidecolumn','ord';
select 'dbr.keepwithnext',0;
exec sp_executesql @t1;
select 'dbr.crosstab','who';
select 'dbr.resultclass','b4 kwn';
select 'dbr.colstyle','str','[min-width:75px;text-align:right;font-weight:bold;]';
select 'dbr.colstyle','doh','[text-align:center]%0.1f';
select 'dbr.css','.b4 {font-size:15px;}';
select 'dbr.css','.b4 td:nth-last-child(1) {font-weight:bold;}';
select 'dbr.css','.b4 th {text-align:center;font-weight:bold;min-width:75px;}';
select 'dbr.css','table.b4 {margin:0;}';
select 'dbr.search',0;
select 'dbr.hidecolumn','ord','str';
exec sp_executesql @t2;
I've used your suggestion and got rid of the the dynamic sql and it works great.
Sorry I'm just so used to years of using MySQL and now just starting T-SQL.
Thanks
Jake
