日期:2014-05-17 浏览次数:20536 次
create proc SP_testMAOSB
(
@where nvarchar(max)='-1'
)
as
create table #a
(
machine nvarchar(500) null,
DURATION nvarchar(500) null
)
declare @sql nvarchar(max)
set @sql='insert into #a(machine,DURATION) '
set @sql=@sql+' SELECT
(dbo.T_SFC_DOWNTIME_CODE.DOWNTIME_CODE_NUMBER + ''—'' + ISNULL(dbo.T_SFC_DOWNTIME_CODE.DOWNTIME_CODE_NAME, '''')) AS ''machine'',
cast (SUM(dbo.T_SFC_DT_PER_MACHINE_DATA.DURATION) AS varchar(100)) AS ''DURATION''
FROM dbo.T_SFC_DOWNTIME_CODE INNER JOIN dbo.T_SFC_DT_PER_MACHINE_DATA ON
dbo.T_SFC_DOWNTIME_CODE.DOWNTIME_CODE_ID = dbo.T_SFC_DT_PER_MACHINE_DATA.DOWNTIME_CODE_ID INNER JOIN
dbo.T_SFC_MACHINE_DATA ON
dbo.T_SFC_DT_PER_MACHINE_DATA.MACHINE_DATA_ID = dbo.T_SFC_MACHINE_DATA.MACHINE_DATA_ID INNER JOIN
dbo.T_SFC_MACHINE ON dbo.T_SFC_MACHINE_DATA.MACHINE_ID = dbo.T_SFC_MACHINE.MACHINE_ID '
if(@where<>'-1')
begin
set @sql=@sql+' '+@where;
end
set @sql=@sql+' group by (dbo.T_SFC_DOWNTIME_CODE.DOWNTIME_CODE_NUMBER + ''—'' + ISNULL(dbo.T_SFC_DOWNTIME_CODE.DOWNTIME_CODE_NAME, ''''))';
print (@sql)
exec(@sql)