URTracker

--  取得某个长字符串中所含项的个数

create function [dbo].[Get_StrArrayLength]
(
  @str nvarchar(4000),  --要分割的字符串
  @split nvarchar(10)  --分隔符号
)
returns int
as
begin
  declare @location int
  declare @start int
  declare @length int

  set @str=ltrim(rtrim(@str))
  set @location=charindex(@split,@str)
  set @length=1
  while @location<>0
  begin
    set @start=@location+1
    set @location=charindex(@split,@str,@start)
    set @length=@length+1
  end
  return @length
end 

--取得某个长字符串中的第n项

create function [dbo].[Get_StrArrayStrOfIndex]
(
  @str nvarchar(4000),  --要分割的字符串
  @split nvarchar(10),  --分隔符号
  @index int --取第几个元素
)
returns nvarchar(4000)
as
begin
  declare @location int
  declare @start int
  declare @next int
  declare @seed int

  set @str=ltrim(rtrim(@str))
  set @start=1
  set @next=1
  set @seed=len(@split)
 
  set @location=charindex(@split,@str)
  while @location<>0 and @index>@next
  begin
    set @start=@location+@seed
    set @location=charindex(@split,@str,@start)
    set @next=@next+1
  end
  if @location =0 select @location =len(@str)+1
 --这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。
 
  return substring(@str,@start,@location-@start)
end

-- 声明临时表，用于存储查询出来的数据
declare @tb table(project nvarchar(50), total int, completed int, notcompleted int, overtime int)

declare @str varchar(5000)
-- 定义所有可能的值
set @str='ACRM系统;CC系统;ERP系统;ETF套利系统;OA系统;OCRM系统'
-- 对每个可能的可选值循环统计，并插入表变量中
declare @next int 
set @next=1
while @next<=dbo.Get_StrArrayLength(@str,';')
begin
 declare @project nvarchar(10)
 set @project=  dbo.Get_StrArrayStrOfIndex(@str,';',@next)
 declare @total int
 declare @completed int
 declare @notcompleted int
 declare @overtime int
 
 select @total=sum(1),
  @completed = sum(case when IsClosed=1 then 1 else 0 end),
  @notcompleted = sum(case when IsClosed=0 then 1 else 0 end),
  @overtime = sum(case when IsClosed=0 and Deadline<GetDate() then 1 else 0 end)
 FROM v_Pts_Problems where ProjectID=项目ID and MultiSelect1 like '%' + @project + '%'
  
 insert into @tb values(@project, @total, @completed, @notcompleted, @overtime)

  set @next=@next+1
end

-- 从表变量中查询
select project as [项目名称],
total as [总数],
completed as [跟踪中],
notcompleted as [已关闭],
overtime as [超期未关闭] from @tb