August 4, 2013 at 7:53 am
/****** Object: StoredProcedure [dbo].[spQMS_FetchStrataSummary] Script Date: 8/4/2013 9:05:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
-- =============================================
/*
declare @pEmployeeIDs udtEmployeeIDs
insert into @pEmployeeIDs values(1836)
exec spQMS_FetchStrataSummary @pEmployeeIDs,'2013-08-02','2013-08-02',33,'BatchNo','E'
*/
ALTER PROCEDURE [dbo].[spQMS_FetchStrataSummary]
@pEmployeeIDs udtEmployeeIDs readonly,
@pWorkedDateFrom date,
@pWorkedDateTo date,
@pProjectProcessID smallint,
@pGroupBy varchar(500),
@pMode char(1)='I'
AS
BEGIN
SET NOCOUNT ON;
declare @StrataSummary nvarchar(4000),@ViewName varchar(500),@Columns varchar(8000),@GroupByCount tinyint,@GroupBy varchar(500)
declare @JoinCondition varchar(8000),@Condition varchar(8000),@ViewCondition varchar(100),@ClientSubClientID int
set @GroupBy=@pGroupBy
select
@ClientSubClientID=pqs.ClientSubClientID
from tblProcessQCStructure pqs
where pqs.ProcessStructureID=@pProjectProcessID -- used to filter client wise data in view
if((select IsSameAs from tblProcessQCStructure where ProcessStructureID=@pProjectProcessID) is not null)
begin
select
@pProjectProcessID=pqs.IsSameAs
from tblProcessQCStructure pqs
where pqs.ProcessStructureID=@pProjectProcessID -- used to get root resource
end
select
@ViewName=DatabaseName+'.'+SchemaName+'.'+ViewName,
@ViewCondition=case when Condition is null then '' else ' and ' + Condition end
from dbo.tblProcessDataSource
where ProcessID=@pProjectProcessID
select @GroupByCount= 2-(LEN(@pGroupBy) - LEN(REPLACE(@pGroupBy, ',', '')))
while(@GroupByCount>0)
begin
set @GroupBy=@GroupBy+',NULL as Column'+CONVERT(varchar, @GroupByCount)
set @GroupByCount=@GroupByCount-1
end
if(@ViewName is not null)
begin
if(@pMode='I')
begin
if not exists
(
select 1
from tblProcessQCColumns
where SourceColumn='FFMTransactionCount'
and ProcessID=@pProjectProcessID
)
begin
set @StrataSummary ='
select '+@GroupBy+',
COUNT(*) as [Count],
COUNT(*) as [TobeQcd]
from '+@ViewName+' trn
where WorkedBy in(select EmployeeID from @pEmployeeIDs)
and (trn.ProcessID='+convert(varchar(50),@ClientSubClientID) +' or trn.ProcessID is null)
and CONVERT(date,WorkedDate) between '''+CONVERT(varchar,@pWorkedDateFrom)+''' and '''+CONVERT(varchar,@pWorkedDateTo)+'''
'+@ViewCondition+'
group by '+@pGroupBy+'
order by '+@pGroupBy
exec sp_executesql @StrataSummary,N'@pEmployeeIDs udtEmployeeIDs readonly',@pEmployeeIDs
select @StrataSummary
end
else
begin
set @StrataSummary ='
select '+@GroupBy+',
SUM(ISNULL(FFMTransactionCount,0)) as [Count],
SUM(ISNULL(FFMTransactionCount,0)) as [TobeQcd]
from '+@ViewName+' trn
where WorkedBy in(select EmployeeID from @pEmployeeIDs)
and (trn.ProcessID='+convert(varchar(50),@ClientSubClientID) +' or trn.ProcessID is null)
and CONVERT(date,WorkedDate) between '''+CONVERT(varchar,@pWorkedDateFrom)+''' and '''+CONVERT(varchar,@pWorkedDateTo)+'''
'+@ViewCondition+'
group by '+@pGroupBy+'
order by '+@pGroupBy
exec sp_executesql @StrataSummary,N'@pEmployeeIDs udtEmployeeIDs readonly',@pEmployeeIDs
--print(@StrataSummary)
end
end
else
begin
if not exists
(
select 1
from tblProcessQCColumns
where SourceColumn='FFMTransactionCount'
and ProcessID=@pProjectProcessID
)
begin
select
@JoinCondition=COALESCE(@JoinCondition+' and ','')+'ISNULL(CONVERT(varchar(1000),trn.['+pqc.SourceColumn+']),'''')=ISNULL(CONVERT(varchar(1000),qms.['+qc.ColumnName+']),'''')'
from dbo.tblProcessQCColumns pqc
inner join dbo.tblQCColumns qc
on qc.ColumnID=pqc.ColumnID
where pqc.ProcessID=@pProjectProcessID
and pqc.IsMatchingColumn=1
select
@Condition=COALESCE(@Condition+' and ','')+'qms.['+qc.ColumnName+'] is null'
from dbo.tblProcessQCColumns pqc
inner join dbo.tblQCColumns qc
on qc.ColumnID=pqc.ColumnID
where pqc.ProcessID=@pProjectProcessID
and pqc.IsMatchingColumn=1
set @StrataSummary =
'
select '+@GroupBy+',
COUNT(*) as [Count],
COUNT(*) as [TobeQcd]
from '+@ViewName+' trn
left outer join tblEmployeeTransactions qms
on '+@JoinCondition+'
inner join @pEmployeeIDs emp
on emp.EmployeeID=trn.WorkedBy
where
(trn.ProcessID='+convert(varchar(50),@ClientSubClientID) +' or trn.ProcessID is null)
and CONVERT(date,WorkedDate) between '''+CONVERT(varchar,@pWorkedDateFrom)+''' and '''+CONVERT(varchar,@pWorkedDateTo)+'''
and ' +@Condition+ '
'+@ViewCondition+'
group by '+@pGroupBy+'
order by '+@pGroupBy
--print @StrataSummary
exec sp_executesql @StrataSummary,N'@pEmployeeIDs udtEmployeeIDs readonly',@pEmployeeIDs
select @StrataSummary
end
else
begin
select
@JoinCondition=COALESCE(@JoinCondition+' and ','')+'ISNULL(CONVERT(varchar(1000),trn.['+pqc.SourceColumn+']),'''')=ISNULL(CONVERT(varchar(1000),qms.['+qc.ColumnName+']),'''')'
from dbo.tblProcessQCColumns pqc
inner join dbo.tblQCColumns qc
on qc.ColumnID=pqc.ColumnID
where pqc.ProcessID=@pProjectProcessID
and pqc.IsMatchingColumn=1
select
@Condition=COALESCE(@Condition+' and ','')+'qms.['+qc.ColumnName+'] is null'
from dbo.tblProcessQCColumns pqc
inner join dbo.tblQCColumns qc
on qc.ColumnID=pqc.ColumnID
where pqc.ProcessID=@pProjectProcessID
and pqc.IsMatchingColumn=1
set @StrataSummary =
'
select '+@GroupBy+',
SUM(ISNULL(FFMTransactionCount,0)) as [Count],
SUM(ISNULL(FFMTransactionCount,0)) as [TobeQcd]
from '+@ViewName+' trn
left outer join tblEmployeeTransactions qms
on '+@JoinCondition+'
where WorkedBy in(select EmployeeID from @pEmployeeIDs)
and (trn.ProcessID='+convert(varchar(50),@ClientSubClientID) +' or trn.ProcessID is null)
and CONVERT(date,WorkedDate) between '''+CONVERT(varchar,@pWorkedDateFrom)+''' and '''+CONVERT(varchar,@pWorkedDateTo)+'''
and ' +@Condition+ '
'+@ViewCondition+'
group by '+@pGroupBy+'
order by '+@pGroupBy
exec sp_executesql @StrataSummary,N'@pEmployeeIDs udtEmployeeIDs readonly',@pEmployeeIDs
end
end
end
print(@StrataSummary)
END
August 4, 2013 at 4:11 pm
It's not clear what your question is, but if your procedure keeps looping and not terminating, add debug PRINT/SELECT to see how the code is (not) progressing.
Tip: rather than
['+pqc.SourceColumn+']
use
quotename(pqc.SourceColumn)
quotename adds the brackets, and doubles any right brackets in the name.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply