можноли оптимизировать запрос?

vehfl
Дата: 30.01.2009 11:31:48
сам запрос:
select 
  GR051,
  case 
      when count(distinct isnull(GR0301, '10')) =1-- c301,
       and count(distinct isnull(GR052, '1')) =1--c52,
       and count(distinct case isnull(GR053, '')
                     when '2' then '1'
                     else isnull(GR053, '')
                   end) =1 -- c53,  
       and count(distinct isnull(GR054, '')) =1--c54,  
       and count(distinct isnull(GR055, '')) =1 --c55,
      then min(isnull(GR0301, ''))
      else 'G'
   end GR0301,
   
  case 
      when count(distinct isnull(GR0301, '10')) =1-- c301,
       and count(distinct isnull(GR052, '1')) =1--c52,
       and count(distinct case isnull(GR053, '')
                     when '2' then '1'
                     else isnull(GR053, '')
                   end) =1 -- c53,  
       and count(distinct isnull(GR054, '')) =1--c54,  
       and count(distinct isnull(GR055, '')) =1 --c55,
      then min(isnull(GR052, '1'))
      else 'G'
    end GR052, 
   
  case 
    when count(distinct isnull(GR025_RISKM, ''))>1 then 'Различно по товарам'
    else min(isnull(GR025_RISKM,''))
  end GR025,
  case 
    when count(distinct isnull(GR022_RISKM, ''))>1 then 'Различно по товарам'
    else convert(varchar(10), min(isnull(GR022_RISKM,'')), 104)
  end GR022,
  case 
    when count(distinct isnull(GR022A, ''))>1 then 'Различно по товарам'
    else min(isnull(GR022A,''))
  end GR022A,
  case 
    when count(distinct isnull(GR023_RISKM, ''))>1 then 'Различно по товарам'
    else min(isnull(GR023_RISKM,''))
  end GR023,
  case 
    when count(distinct isnull(GR024_RISKM, ''))>1 then 'Различно по товарам'
    else min(isnull(GR024_RISKM,''))
  end GR024,
  case 
    when count(distinct isnull(GR0302, ''))>1 then 'Различно по товарам'
    else min(isnull(GR0302,''))
  end GR0302,
  case 
    when count(distinct isnull(GR031_RISKM, ''))>1 then 'Различно по товарам'
    else min(isnull(GR031_RISKM,''))
  end GR031,
  1
from gtd..DCL_RISKM m
where ND_Key = @ND_Key
  and (m.GR052 is null
       or 
       m.GR052+isnull(m.GR053,'0')+isnull(m.GR054,'00') not in ('1002', '2000', '3000','0005')
       and 
       exists(select *
              from gtd..DCL_RISKV r
              where r.ND_Key = @ND_Key and r.SerialRiskv = m.SerialRiskv and r.POINT_CODE = @POINT_CODE
             )
      )                
group by GR051
план выполнения:
  |--Compute Scalar(DEFINE:([Expr1024]=If (((([Expr1004]=1 AND [Expr1005]=1) AND [Expr1006]=1) AND [Expr1007]=1) AND [Expr1008]=1) then [Expr1003] else 'G', [Expr1025]=If (((([Expr1004]=1 AND [Expr1005]=1) AND [Expr1006]=1) AND [Expr1007]=1) AND [Expr1008]=1) then [Expr1009] else 'G', [Expr1026]=If ([Expr1010]>1) then 'Различно по товарам' else Convert([Expr1011]), [Expr1027]=If ([Expr1012]>1) then 'Различно по товарам' else Convert(Convert([Expr1013])), [Expr1028]=If ([Expr1014]>1) then 'Различно по товарам' else Convert([Expr1015]), [Expr1029]=If ([Expr1016]>1) then 'Различно по товарам' else Convert([Expr1017]), [Expr1030]=If ([Expr1018]>1) then 'Различно по товарам' else [Expr1019], [Expr1031]=If ([Expr1020]>1) then 'Различно по товарам' else Convert([Expr1021]), [Expr1032]=If ([Expr1022]>1) then 'Различно по товарам' else [Expr1023]))
       |--Compute Scalar(DEFINE:([m].[GR051]=[m].[GR051]))
            |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([m].[GR051]=[m].[GR051]))
                 |--Compute Scalar(DEFINE:([m].[GR051]=[m].[GR051]))
                 |    |--Compute Scalar(DEFINE:([Expr1022]=Convert([Expr1140])))
                 |         |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1140]=COUNT_BIG([Expr1091]), [Expr1023]=MIN([Expr1091])))
                 |              |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1091] ASC))
                 |                   |--Table Spool
                 |                        |--Compute Scalar(DEFINE:([Expr1080]=isnull([m].[GR0301], '10'), [Expr1081]=isnull([m].[GR052], '1'), [Expr1082]=If (isnull([m].[GR053], ' ')='2') then '1' else isnull([m].[GR053], ' '), [Expr1083]=isnull([m].[GR054], '  '), [Expr1084]=isnull([m].[GR055], '  '), [Expr1085]=isnull([m].[GR025_RISKM], ''), [Expr1086]=isnull([m].[GR022_RISKM], 'Jan  1 1900 12:00AM'), [Expr1087]=isnull([m].[GR022A], ''), [Expr1088]=isnull([m].[GR023_RISKM], ''), [Expr1089]=isnull([m].[GR024_RISKM], ''), [Expr1090]=isnull([m].[GR0302], ''), [Expr1091]=isnull([m].[GR031_RISKM], '')))
                 |                             |--Filter(WHERE:([m].[GR052]=NULL OR (((([m].[GR052]+isnull([m].[GR053], '0')+isnull([m].[GR054], '00')<>'0005' AND [m].[GR052]+isnull([m].[GR053], '0')+isnull([m].[GR054], '00')<>'3000') AND [m].[GR052]+isnull([m].[GR053], '0')+isnull([m].[GR054], '00')<>'2000') AND [m].[GR052]+isnull([m].[GR053], '0')+isnull([m].[GR054], '00')<>'1002') AND [Expr1061])))
                 |                                  |--Nested Loops(Left Semi Join, WHERE:(((([m].[GR052]=NULL OR IsFalseOrNull([m].[GR052]+isnull([m].[GR053], '0')+isnull([m].[GR054], '00')<>'0005')) OR IsFalseOrNull([m].[GR052]+isnull([m].[GR053], '0')+isnull([m].[GR054], '00')<>'3000')) OR IsFalseOrNull([m].[GR052]+isnull([m].[GR053], '0')+isnull([m].[GR054], '00')<>'2000')) OR IsFalseOrNull([m].[GR052]+isnull([m].[GR053], '0')+isnull([m].[GR054], '00')<>'1002'))OUTER REFERENCES:([m].[SerialRiskv]), DEFINE:([Expr1061] = [PROBE VALUE]))
                 |                                       |--Filter(WHERE:([m].[GR052]=NULL OR ((([m].[GR052]+isnull([m].[GR053], '0')+isnull([m].[GR054], '00')<>'0005' AND [m].[GR052]+isnull([m].[GR053], '0')+isnull([m].[GR054], '00')<>'3000') AND [m].[GR052]+isnull([m].[GR053], '0')+isnull([m].[GR054], '00')<>'2000') AND [m].[GR052]+isnull([m].[GR053], '0')+isnull([m].[GR054], '00')<>'1002')))
                 |                                       |    |--Clustered Index Seek(OBJECT:([gtd].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [m]), SEEK:([m].[ND_Key]=[@ND_Key]) ORDERED FORWARD)
                 |                                       |--Row Count Spool
                 |                                            |--Clustered Index Seek(OBJECT:([gtd].[dbo].[DCL_RISKV].[PK_DCL_RISKV] AS [r]), SEEK:([r].[ND_Key]=[@ND_Key] AND [r].[SerialRiskv]=[m].[SerialRiskv]),  WHERE:([r].[POINT_CODE]=[@POINT_CODE]) ORDERED FORWARD)
                 |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([m].[GR051]=[m].[GR051]))
                      |--Compute Scalar(DEFINE:([m].[GR051]=[m].[GR051]))
                      |    |--Compute Scalar(DEFINE:([Expr1020]=Convert([Expr1141])))
                      |         |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1141]=COUNT_BIG([Expr1090]), [Expr1021]=MIN([Expr1090])))
                      |              |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1090] ASC))
                      |                   |--Table Spool
                      |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([m].[GR051]=[m].[GR051]))
                           |--Compute Scalar(DEFINE:([m].[GR051]=[m].[GR051]))
                           |    |--Compute Scalar(DEFINE:([Expr1018]=Convert([Expr1142])))
                           |         |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1142]=COUNT_BIG([Expr1089]), [Expr1019]=MIN([Expr1089])))
                           |              |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1089] ASC))
                           |                   |--Table Spool
                           |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([m].[GR051]=[m].[GR051]))
                                |--Compute Scalar(DEFINE:([m].[GR051]=[m].[GR051]))
                                |    |--Compute Scalar(DEFINE:([Expr1016]=Convert([Expr1143])))
                                |         |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1143]=COUNT_BIG([Expr1088]), [Expr1017]=MIN([Expr1088])))
                                |              |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1088] ASC))
                                |                   |--Table Spool
                                |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([m].[GR051]=[m].[GR051]))
                                     |--Compute Scalar(DEFINE:([m].[GR051]=[m].[GR051]))
                                     |    |--Compute Scalar(DEFINE:([Expr1014]=Convert([Expr1144])))
                                     |         |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1144]=COUNT_BIG([Expr1087]), [Expr1015]=MIN([Expr1087])))
                                     |              |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1087] ASC))
                                     |                   |--Table Spool
                                     |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([m].[GR051]=[m].[GR051]))
                                          |--Compute Scalar(DEFINE:([m].[GR051]=[m].[GR051]))
                                          |    |--Compute Scalar(DEFINE:([Expr1012]=Convert([Expr1145])))
                                          |         |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1145]=COUNT_BIG([Expr1086]), [Expr1013]=MIN([Expr1086])))
                                          |              |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1086] ASC))
                                          |                   |--Table Spool
                                          |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([m].[GR051]=[m].[GR051]))
                                               |--Compute Scalar(DEFINE:([m].[GR051]=[m].[GR051]))
                                               |    |--Compute Scalar(DEFINE:([Expr1010]=Convert([Expr1146])))
                                               |         |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1146]=COUNT_BIG([Expr1085]), [Expr1011]=MIN([Expr1085])))
                                               |              |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1085] ASC))
                                               |                   |--Table Spool
                                               |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([m].[GR051]=[m].[GR051]))
                                                    |--Compute Scalar(DEFINE:([m].[GR051]=[m].[GR051]))
                                                    |    |--Compute Scalar(DEFINE:([Expr1008]=Convert([Expr1147])))
                                                    |         |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1147]=COUNT_BIG([Expr1084])))
                                                    |              |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1084] ASC))
                                                    |                   |--Table Spool
                                                    |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([m].[GR051]=[m].[GR051]))
                                                         |--Compute Scalar(DEFINE:([m].[GR051]=[m].[GR051]))
                                                         |    |--Compute Scalar(DEFINE:([Expr1007]=Convert([Expr1148])))
                                                         |         |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1148]=COUNT_BIG([Expr1083])))
                                                         |              |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1083] ASC))
                                                         |                   |--Table Spool
                                                         |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([m].[GR051]=[m].[GR051]))
                                                              |--Compute Scalar(DEFINE:([m].[GR051]=[m].[GR051]))
                                                              |    |--Compute Scalar(DEFINE:([Expr1006]=Convert([Expr1149])))
                                                              |         |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1149]=COUNT_BIG([Expr1082])))
                                                              |              |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1082] ASC))
                                                              |                   |--Table Spool
                                                              |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([m].[GR051]=[m].[GR051]))
                                                                   |--Compute Scalar(DEFINE:([m].[GR051]=[m].[GR051]))
                                                                   |    |--Compute Scalar(DEFINE:([Expr1005]=Convert([Expr1150])))
                                                                   |         |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1150]=COUNT_BIG([Expr1081]), [Expr1009]=MIN([Expr1081])))
                                                                   |              |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1081] ASC))
                                                                   |                   |--Table Spool
                                                                   |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([m].[GR051]=[m].[GR051]))
                                                                        |--Compute Scalar(DEFINE:([m].[GR051]=[m].[GR051]))
                                                                        |    |--Compute Scalar(DEFINE:([Expr1004]=Convert([Expr1151])))
                                                                        |         |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1151]=COUNT_BIG([Expr1080])))
                                                                        |              |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1080] ASC))
                                                                        |                   |--Table Spool
                                                                        |--Compute Scalar(DEFINE:([m].[GR051]=[m].[GR051]))
                                                                             |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1003]=MIN(isnull([m].[GR0301], ''))))
                                                                                  |--Sort(ORDER BY:([m].[GR051] ASC))
                                                                                       |--Table Spool
vehfl
Дата: 30.01.2009 11:38:46
sql server 2000
vehfl
Дата: 30.01.2009 11:46:33
таблица
USE [gtd]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [DCL_RISKM](
	[ND_Key] [binary](6) NOT NULL,
	[SerialRiskv] [smallint] NOT NULL,
	[SerialRiskm] [smallint] NOT NULL,
	[GR022_RISKM] [datetime] NULL,
	[GR022A] [varchar](8) NULL,
	[GR023_RISKM] [varchar](4) NULL,
	[GR024_RISKM] [varchar](40) NULL,
	[GR025_RISKM] [varchar](8) NULL,
	[GR0301] [varchar](2) NULL,
	[GR0302] [varchar](4) NULL,
	[GR031_RISKM] [varchar](255) NULL,
	[GR051] [varchar](3) NULL,
	[GR053] [char](1) NULL,
	[DMODIFY] [datetime] NULL,
	[TMODIFY] [varchar](8) NULL,
	[GR052] [char](1) NULL,
	[GR054] [char](2) NULL,
	[GR0511] [smallint] NULL,
	[GR0512] [smallint] NULL,
	[GR055] [char](2) NULL,
 CONSTRAINT [PK_DCL_RISKM] PRIMARY KEY CLUSTERED 
(
	[ND_Key] ASC,
	[SerialRiskv] ASC,
	[SerialRiskm] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [DCL_RISKM]  WITH CHECK ADD  CONSTRAINT [FK_DCL_RISKM] FOREIGN KEY([ND_Key], [SerialRiskv])
REFERENCES [dbo].[DCL_RISKV] ([ND_Key], [SerialRiskv])
GO
ALTER TABLE [DCL_RISKM] CHECK CONSTRAINT [FK_DCL_RISKM]
Senya_L
Дата: 30.01.2009 11:57:36
vehfl
Разбирать подробно лень, запрос не сложный, но дюже больно здоровый.
Несколько советов.

1) Подзапрос
       exists(select *
              from gtd..DCL_RISKV r
              where r.ND_Key = @ND_Key and r.SerialRiskv = m.SerialRiskv and r.POINT_CODE = @POINT_CODE
             )
переделать на INNER JOIN.

2) Вот такие штуки
  case 
    when count(distinct isnull(GR025_RISKM, ''))>1 then 'Различно по товарам'
    else min(isnull(GR025_RISKM,''))
  end GR025,
переделать на EXISTS.

3) Предикат m.GR052 is null можно убрать, если конкатенация по ANSI делается.
vehfl
Дата: 30.01.2009 12:20:02
Senya_L
2) Вот такие штуки
  case 
    when count(distinct isnull(GR025_RISKM, ''))>1 then 'Различно по товарам'
    else min(isnull(GR025_RISKM,''))
  end GR025,
переделать на EXISTS.

что то не понимаю каким образом это можно переделать на EXISTS
Senya_L
Дата: 30.01.2009 12:35:18
vehfl
Senya_L
2) Вот такие штуки
  case 
    when count(distinct isnull(GR025_RISKM, ''))>1 then 'Различно по товарам'
    else min(isnull(GR025_RISKM,''))
  end GR025,
переделать на EXISTS.

что то не понимаю каким образом это можно переделать на EXISTS

  case 
    when 
        exists (select * from [DCL_RISKM] t where t.GR025_RISKM = m.GR025_RISKM and t.ND_Key <> m.ND_Key) then 'Различно по товарам'
    else -- тут непонятно, что у вас делается
  end GR025,
От дистинктов надо избавляться
count(distinct isnull(GR025_RISK
Дата: 30.01.2009 13:22:08
Senya_L
vehfl
Senya_L
2) Вот такие штуки
  case 
    when count(distinct isnull(GR025_RISKM, ''))>1 then 'Различно по товарам'
    else min(isnull(GR025_RISKM,''))
  end GR025,
переделать на EXISTS.

что то не понимаю каким образом это можно переделать на EXISTS

  case 
    when 
        exists (select * from [DCL_RISKM] t where t.GR025_RISKM = m.GR025_RISKM and t.ND_Key <> m.ND_Key) then 'Различно по товарам'
    else -- тут непонятно, что у вас делается
  end GR025,
От дистинктов надо избавляться

спасибо...

в случае если count(distinct isnull(GR025_RISKM, ''))=1, то возвращаю это единственное значение... а так как идет групировка по GR051, то беру минимальное значение isnull(GR025_RISKM, ''), которое будет в этом случае совпадать со значниями GR025_RISKM во всех строках..
Senya_L
Дата: 30.01.2009 13:38:47
count(distinct isnull(GR025_RISK
...
Ваш запрос нагоняет тоску. Думаю его можно вообще переделать капитально.
vehfl
Дата: 30.01.2009 13:54:17
ну я сам не в восторге...

меня самого пугают размеры...

поэтому и интересуюсь возможными путями оптимизации...
одну идею подсказали))... (проверил стало быстрей)
Taffy
Дата: 30.01.2009 14:03:53
ради интереса повозилась
1-ый вариант

+
SET SHOWPLAN_TEXT ON
go
--здесь анализируемый скрипт

declare @nd_key smallint, @POINT_CODE smallint
select @nd_key = 1, @POINT_CODE = 1
declare @t table(nd_key smallint, POINT_CODE smallint)
insert into @t select @nd_key, @POINT_CODE




select gr051,
	GR0301 = case when c_GR0301 = 1 and c_GR052 = 1 and c_GR053 = 1 and c_GR054 = 1 and c_GR055 = 1 
				then GR0301
				else 'G' end,
	GR052 = case when c_GR0301 = 1 and c_GR052 = 1 and c_GR053 = 1 and c_GR054 = 1 and c_GR055 = 1 
				then GR052
				else 'G' end,
	GR025 = case when c_GR025_RISKM >1 
				then 'Различно по товарам'
				else m_GR025_RISKM end,
	GR022 = case when c_GR022_RISKM >1 
				then 'Различно по товарам'
				else m_GR022_RISKM end,
	GR022A = case when c_GR022A >1 
				then 'Различно по товарам'
				else m_GR022A end,
	GR023 = case when c_GR023_RISKM >1 
				then 'Различно по товарам'
				else m_GR023_RISKM end,
	GR024 = case when c_GR024_RISKM >1 
				then 'Различно по товарам'
				else m_GR024_RISKM end,
	GR0302 = case when c_GR0302 >1 
				then 'Различно по товарам'
				else m_GR0302 end,	
	GR031 = case when c_GR031_RISKM >1 
				then 'Различно по товарам'
				else m_GR031_RISKM end,
	1
FROM
(
select GR051, 
	c_GR0301 = count(distinct isnull(GR0301, '10')),
	c_GR052 = count(distinct isnull(GR052, '1')),
	c_GR053 = count(distinct case isnull(GR053, '')
						 when '2' then '1'
						 else isnull(GR053, '')
					   end),
	c_GR054 = count(distinct isnull(GR054, '')),
	c_GR055 = count(distinct isnull(GR055, '')),
	GR0301 = min(isnull(GR0301, '')),
	GR052 = min(isnull(GR052, '1')),

	c_GR025_RISKM = count(distinct isnull(GR025_RISKM, '')),
	m_GR025_RISKM = min(isnull(GR025_RISKM,'')),
	c_GR022_RISKM = count(distinct isnull(GR022_RISKM, '')),
	m_GR022_RISKM = convert(varchar(10), min(isnull(GR022_RISKM,'')), 104),
	c_GR022A = count(distinct isnull(GR022A, '')),
	m_GR022A = min(isnull(GR022A,'')),
	c_GR023_RISKM = count(distinct isnull(GR023_RISKM, '')),
	m_GR023_RISKM = min(isnull(GR023_RISKM,'')),
	c_GR024_RISKM = count(distinct isnull(GR024_RISKM, '')),
	m_GR024_RISKM = min(isnull(GR024_RISKM,'')),
	c_GR0302 = count(distinct isnull(GR0302, '')),
	m_GR0302 = min(isnull(GR0302,'')),
	c_GR031_RISKM = count(distinct isnull(GR031_RISKM, '')),
	m_GR031_RISKM = min(isnull(GR031_RISKM,''))
from @t t inner join 
	DCL_RISKM m on m.ND_Key = t.ND_Key
where m.GR052 is null
       or 
       m.GR052+isnull(m.GR053,'0')+isnull(m.GR054,'00') not in ('1002', '2000', '3000','0005')
       and 
       exists(select *
              from @t t1 inner join
					DCL_RISKV r on t1.ND_Key = r.ND_Key and 
										r.SerialRiskv = m.SerialRiskv and 
										r.POINT_CODE = t1.POINT_CODE
             )
                     
group by GR051
) a
go
SET SHOWPLAN_TEXT OFF
go


StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1034]=CASE WHEN [Expr1012]=(1) AND [Expr1013]=(1) AND [Expr1014]=(1) AND [Expr1015]=(1) AND [Expr1016]=(1) THEN [Expr1017] ELSE 'G' END, [Expr1035]=CASE WHEN [Expr1012]=(1) AND [Expr1013]=(1) AND [Expr1014]=(1) AND [Expr1015]=(1) AND [Expr1016]=(1) THEN [Expr1018] ELSE 'G' END, [Expr1036]=CASE WHEN [Expr1019]>(1) THEN 'Различно по товарам' ELSE CONVERT_IMPLICIT(varchar(19),[Expr1020],0) END, [Expr1037]=CASE WHEN [Expr1021]>(1) THEN 'Различно по товарам' ELSE CONVERT(varchar(10),[Expr1022],104) END, [Expr1038]=CASE WHEN [Expr1023]>(1) THEN 'Различно по товарам' ELSE CONVERT_IMPLICIT(varchar(19),[Expr1024],0) END, [Expr1039]=CASE WHEN [Expr1025]>(1) THEN 'Различно по товарам' ELSE CONVERT_IMPLICIT(varchar(19),[Expr1026],0) END, [Expr1040]=CASE WHEN [Expr1027]
|--Compute Scalar(DEFINE:([m].[GR051]=[bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051] = [bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
|--Compute Scalar(DEFINE:([m].[GR051]=[bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| |--Compute Scalar(DEFINE:([Expr1031]=CONVERT_IMPLICIT(int,[Expr1138],0)))
| |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1138]=Count(*), [Expr1032]=MIN([Expr1065])))
| |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1065] ASC))
| |--Table Spool
| |--Nested Loops(Inner Join, OUTER REFERENCES:([t].[nd_key]))
| |--Table Scan(OBJECT:(@t AS [t]))
| |--Compute Scalar(DEFINE:([Expr1053]=isnull([bsti].[dbo].[DCL_RISKM].[GR0301] as [m].[GR0301],'10'), [Expr1054]=isnull([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052],'1'), [Expr1055]=CASE WHEN isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'') = '2' THEN '1' ELSE isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'') END, [Expr1056]=isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],''), [Expr1057]=isnull([bsti].[dbo].[DCL_RISKM].[GR055] as [m].[GR055],''), [Expr1058]=isnull([bsti].[dbo].[DCL_RISKM].[GR0301] as [m].[GR0301],''), [Expr1059]=isnull([bsti].[dbo].[DCL_RISKM].[GR025_RISKM] as [m].[GR025_RISKM],''), [Expr1060]=isnull([bsti].[dbo].[DCL_RISKM].[GR022_RISKM] as [m].[GR022_RISKM],'1900-01-01 00:00:00.000'), [Expr1061]=i
| |--Stream Aggregate(GROUP BY:([m].[ND_Key], [m].[SerialRiskv], [m].[SerialRiskm]) DEFINE:([m].[GR022_RISKM]=ANY([bsti].[dbo].[DCL_RISKM].[GR022_RISKM] as [m].[GR022_RISKM]), [m].[GR022A]=ANY([bsti].[dbo].[DCL_RISKM].[GR022A] as [m].[GR022A]), [m].[GR023_RISKM]=ANY([bsti].[dbo].[DCL_RISKM].[GR023_RISKM] as [m].[GR023_RISKM]), [m].[GR024_RISKM]=ANY([bsti].[dbo].[DCL_RISKM].[GR024_RISKM] as [m].[GR024_RISKM]), [m].[GR025_RISKM]=ANY([bsti].[dbo].[DCL_RISKM].[GR025_RISKM] as [m].[GR025_RISKM]), [m].[GR0301]=ANY([bsti].[dbo].[DCL_RISKM].[GR0301] as [m].[GR0301]), [m].[GR0302]=ANY([bsti].[dbo].[DCL_RISKM].[GR0302] as [m].[GR0302]), [m].[GR031_RISKM]=ANY([bsti].[dbo].[DCL_RISKM].[GR031_RISKM] as [m].[GR031_RISKM]), [m].[GR051]=ANY([bsti].[dbo].[D
| |--Merge Join(Concatenation)
| |--Filter(WHERE:([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052] IS NULL OR (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'00')) <> '0005' AND (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'00')) <> '3000' AND (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'00')) <> '2000' AND (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR0
| | |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [m]), WHERE:([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052] IS NULL AND CONVERT_IMPLICIT(smallint,[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key],0)=@t.[nd_key] as [t].[nd_key]) ORDERED FORWARD)
| |--Stream Aggregate(GROUP BY:([m].[ND_Key], [m].[SerialRiskv], [m].[SerialRiskm]) DEFINE:([m].[GR022_RISKM]=ANY([bsti].[dbo].[DCL_RISKM].[GR022_RISKM] as [m].[GR022_RISKM]), [m].[GR022A]=ANY([bsti].[dbo].[DCL_RISKM].[GR022A] as [m].[GR022A]), [m].[GR023_RISKM]=ANY([bsti].[dbo].[DCL_RISKM].[GR023_RISKM] as [m].[GR023_RISKM]), [m].[GR024_RISKM]=ANY([bsti].[dbo].[DCL_RISKM].[GR024_RISKM] as [m].[GR024_RISKM]), [m].[GR025_RISKM]=ANY([bsti].[dbo].[DCL_RISKM].[GR025_RISKM] as [m].[GR025_RISKM]), [m].[GR0301]=ANY([bsti].[dbo].[DCL_RISKM].[GR0301] as [m].[GR0301]), [m].[GR0302]=ANY([bsti].[dbo].[DCL_RISKM].[GR0302] as [m].[GR0302]), [m].[GR031_RISKM]=ANY([bsti].[dbo].[DCL_RISKM].[GR031_RISKM] as [m].[GR031_RISKM]), [m].[GR051]=ANY([bsti
| |--Nested Loops(Inner Join, WHERE:([bsti].[dbo].[DCL_RISKV].[POINT_CODE] as [r].[POINT_CODE]=@t.[POINT_CODE] as [t1].[POINT_CODE] AND @t.[nd_key] as [t1].[nd_key]=CONVERT_IMPLICIT(smallint,[bsti].[dbo].[DCL_RISKV].[ND_Key] as [r].[ND_Key],0)))
| |--Nested Loops(Inner Join, WHERE:([bsti].[dbo].[DCL_RISKV].[SerialRiskv] as [r].[SerialRiskv]=[bsti].[dbo].[DCL_RISKM].[SerialRiskv] as [m].[SerialRiskv]))
| | |--Filter(WHERE:([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052] IS NULL OR (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'00')) <> '0005' AND (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'00')) <> '3000' AND (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'00')) <> '2000' AND (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR0
| | | |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [m]), WHERE:(CONVERT_IMPLICIT(smallint,[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key],0)=@t.[nd_key] as [t].[nd_key] AND (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'00')) <> '0005' AND (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'00')) <> '3000' AND (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[
| | |--Table Scan(OBJECT:([bsti].[dbo].[DCL_RISKV] AS [r]))
| |--Table Scan(OBJECT:(@t AS [t1]))
|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051] = [bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051] = [bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051] = [bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051] = [bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051] = [bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051] = [bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | | | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051] = [bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | | | | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051] = [bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | | | | | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051] = [bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | | | | | | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051] = [bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | | | | | | | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([m].[GR051])=([m].[GR051]), RESIDUAL:([bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051] = [bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | | | | | | | | |--Compute Scalar(DEFINE:([m].[GR051]=[bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | | | | | | | | | |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1017]=MIN([Expr1058])))
| | | | | | | | | | | |--Sort(ORDER BY:([m].[GR051] ASC))
| | | | | | | | | | | |--Table Spool
| | | | | | | | | | |--Compute Scalar(DEFINE:([m].[GR051]=[bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1012]=CONVERT_IMPLICIT(int,[Expr1139],0)))
| | | | | | | | | | |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1139]=Count(*)))
| | | | | | | | | | |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1053] ASC))
| | | | | | | | | | |--Table Spool
| | | | | | | | | |--Compute Scalar(DEFINE:([m].[GR051]=[bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | | | | | | | |--Compute Scalar(DEFINE:([Expr1013]=CONVERT_IMPLICIT(int,[Expr1140],0)))
| | | | | | | | | |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1140]=Count(*), [Expr1018]=MIN([Expr1054])))
| | | | | | | | | |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1054] ASC))
| | | | | | | | | |--Table Spool
| | | | | | | | |--Compute Scalar(DEFINE:([m].[GR051]=[bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | | | | | | |--Compute Scalar(DEFINE:([Expr1014]=CONVERT_IMPLICIT(int,[Expr1141],0)))
| | | | | | | | |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1141]=Count(*)))
| | | | | | | | |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1055] ASC))
| | | | | | | | |--Table Spool
| | | | | | | |--Compute Scalar(DEFINE:([m].[GR051]=[bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | | | | | |--Compute Scalar(DEFINE:([Expr1015]=CONVERT_IMPLICIT(int,[Expr1142],0)))
| | | | | | | |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1142]=Count(*)))
| | | | | | | |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1056] ASC))
| | | | | | | |--Table Spool
| | | | | | |--Compute Scalar(DEFINE:([m].[GR051]=[bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | | | | |--Compute Scalar(DEFINE:([Expr1016]=CONVERT_IMPLICIT(int,[Expr1143],0)))
| | | | | | |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1143]=Count(*)))
| | | | | | |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1057] ASC))
| | | | | | |--Table Spool
| | | | | |--Compute Scalar(DEFINE:([m].[GR051]=[bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | | | |--Compute Scalar(DEFINE:([Expr1019]=CONVERT_IMPLICIT(int,[Expr1144],0)))
| | | | | |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1144]=Count(*), [Expr1020]=MIN([Expr1059])))
| | | | | |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1059] ASC))
| | | | | |--Table Spool
| | | | |--Compute Scalar(DEFINE:([m].[GR051]=[bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | | |--Compute Scalar(DEFINE:([Expr1021]=CONVERT_IMPLICIT(int,[Expr1145],0)))
| | | | |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1145]=Count(*), [Expr1022]=MIN([Expr1060])))
| | | | |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1060] ASC))
| | | | |--Table Spool
| | | |--Compute Scalar(DEFINE:([m].[GR051]=[bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | | |--Compute Scalar(DEFINE:([Expr1023]=CONVERT_IMPLICIT(int,[Expr1146],0)))
| | | |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1146]=Count(*), [Expr1024]=MIN([Expr1061])))
| | | |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1061] ASC))
| | | |--Table Spool
| | |--Compute Scalar(DEFINE:([m].[GR051]=[bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| | |--Compute Scalar(DEFINE:([Expr1025]=CONVERT_IMPLICIT(int,[Expr1147],0)))
| | |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1147]=Count(*), [Expr1026]=MIN([Expr1062])))
| | |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1062] ASC))
| | |--Table Spool
| |--Compute Scalar(DEFINE:([m].[GR051]=[bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
| |--Compute Scalar(DEFINE:([Expr1027]=CONVERT_IMPLICIT(int,[Expr1148],0)))
| |--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1148]=Count(*), [Expr1028]=MIN([Expr1063])))
| |--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1063] ASC))
| |--Table Spool
|--Compute Scalar(DEFINE:([m].[GR051]=[bsti].[dbo].[DCL_RISKM].[GR051] as [m].[GR051]))
|--Compute Scalar(DEFINE:([Expr1029]=CONVERT_IMPLICIT(int,[Expr1149],0)))
|--Stream Aggregate(GROUP BY:([m].[GR051]) DEFINE:([Expr1149]=Count(*), [Expr1030]=MIN([Expr1064])))
|--Sort(DISTINCT ORDER BY:([m].[GR051] ASC, [Expr1064] ASC))
|--Table Spool


и 2 вариант (с экзистами)

+


SET SHOWPLAN_TEXT ON
go
--здесь анализируемый скрипт

declare @nd_key smallint, @POINT_CODE smallint
select @nd_key = 1, @POINT_CODE = 1
declare @t table(nd_key smallint, POINT_CODE smallint)
insert into @t select @nd_key, @POINT_CODE




select gr051,
	GR0301 = case when c_GR0301 = 1 and c_GR052 = 1 and c_GR053 = 1 and c_GR054 = 1 and c_GR055 = 1 
				then GR0301
				else 'G' end,
	GR052 = case when c_GR0301 = 1 and c_GR052 = 1 and c_GR053 = 1 and c_GR054 = 1 and c_GR055 = 1 
				then GR052
				else 'G' end,
	GR025 = case when c_GR025_RISKM =1 
				then 'Различно по товарам'
				else m_GR025_RISKM end,
	GR022 = case when c_GR022_RISKM =1 
				then 'Различно по товарам'
				else m_GR022_RISKM end,
	GR022A = case when c_GR022A =1 
				then 'Различно по товарам'
				else m_GR022A end,
	GR023 = case when c_GR023_RISKM =1 
				then 'Различно по товарам'
				else m_GR023_RISKM end,
	GR024 = case when c_GR024_RISKM =1 
				then 'Различно по товарам'
				else m_GR024_RISKM end,
	GR0302 = case when c_GR0302 =1 
				then 'Различно по товарам'
				else m_GR0302 end,	
	GR031 = case when c_GR031_RISKM =1 
				then 'Различно по товарам'
				else m_GR031_RISKM end,
	1
FROM
(
select m.GR051, 
	c.c_GR0301,
	c.c_GR052,
	c.c_GR053,
	c.c_GR054 ,
	c.c_GR055,
	GR0301 = min(isnull(m.GR0301, '')),
	GR052 = min(isnull(m.GR052, '1')),

	c.c_GR025_RISKM,
	m_GR025_RISKM = min(isnull(m.GR025_RISKM,'')),
	c.c_GR022_RISKM,
	m_GR022_RISKM = convert(varchar(10), min(isnull(GR022_RISKM,'')), 104),
	c.c_GR022A ,
	m_GR022A = min(isnull(m.GR022A,'')),
	c.c_GR023_RISKM,
	m_GR023_RISKM = min(isnull(m.GR023_RISKM,'')),
	c.c_GR024_RISKM ,
	m_GR024_RISKM = min(isnull(m.GR024_RISKM,'')),
	c.c_GR0302,
	m_GR0302 = min(isnull(m.GR0302,'')),
	c.c_GR031_RISKM ,
	m_GR031_RISKM = min(isnull(m.GR031_RISKM,''))
from @t t inner join 
	DCL_RISKM m on m.ND_Key = t.ND_Key inner join
	(select m.ND_Key, 
	c_GR0301 = case when 
        exists (select * from [DCL_RISKM] t where isnull(t.GR0301, '10') = isnull(m.GR0301, '10') and t.ND_Key <> m.ND_Key)
				then 0 else 1 end,
	c_GR052 =  case when 
               exists (select * from [DCL_RISKM] t where isnull(t.GR052, '1') = isnull(m.GR052, '1') and t.ND_Key <> m.ND_Key)
				then 0 else 1 end,
	c_GR053 = case when 
               exists (select * from [DCL_RISKM] t where (case isnull(t.GR053, '')
						 when '2' then '1'
						 else isnull(t.GR053, '')
					   end) = (case isnull(m.GR053, '')
						 when '2' then '1'
						 else isnull(m.GR053, '')
					   end) and t.ND_Key <> m.ND_Key)
				then 0 else 1 end,
	c_GR054 = case when 
               exists (select * from [DCL_RISKM] t where isnull(t.GR054, '') = isnull(m.GR054, '') and t.ND_Key <> m.ND_Key)
				then 0 else 1 end,
	c_GR055 = case when 
               exists (select * from [DCL_RISKM] t where isnull(t.GR055, '') = isnull(m.GR055, '') and t.ND_Key <> m.ND_Key)
				then 0 else 1 end,
	c_GR025_RISKM = case when 
               exists (select * from [DCL_RISKM] t where isnull(t.GR025_RISKM, '') = isnull(m.GR025_RISKM, '') and t.ND_Key <> m.ND_Key)
				then 0 else 1 end,
	c_GR022_RISKM = case when 
               exists (select * from [DCL_RISKM] t where isnull(t.GR022_RISKM, '') = isnull(m.GR022_RISKM, '') and t.ND_Key <> m.ND_Key)
				then 0 else 1 end,
	c_GR022A = case when 
               exists (select * from [DCL_RISKM] t where isnull(t.GR022A, '') = isnull(m.GR022A, '') and t.ND_Key <> m.ND_Key)
				then 0 else 1 end,
	c_GR023_RISKM = case when 
               exists (select * from [DCL_RISKM] t where isnull(t.GR023_RISKM, '') = isnull(m.GR023_RISKM, '') and t.ND_Key <> m.ND_Key)
				then 0 else 1 end,
	c_GR024_RISKM = case when 
               exists (select * from [DCL_RISKM] t where isnull(t.GR024_RISKM, '') = isnull(m.GR024_RISKM, '') and t.ND_Key <> m.ND_Key)
				then 0 else 1 end,
	c_GR0302 = case when 
               exists (select * from [DCL_RISKM] t where isnull(t.GR0302, '') = isnull(m.GR0302, '') and t.ND_Key <> m.ND_Key)
				then 0 else 1 end,
	c_GR031_RISKM = case when 
               exists (select * from [DCL_RISKM] t where isnull(t.GR031_RISKM, '') = isnull(m.GR031_RISKM, '') and t.ND_Key <> m.ND_Key)
				then 0 else 1 end
    from @t t inner join 
	DCL_RISKM m on m.ND_Key = t.ND_Key
	where m.GR052 is null
       or 
       m.GR052+isnull(m.GR053,'0')+isnull(m.GR054,'00') not in ('1002', '2000', '3000','0005')
       and 
       exists(select *
              from @t t1 inner join
					DCL_RISKV r on t1.ND_Key = r.ND_Key and 
										r.SerialRiskv = m.SerialRiskv and 
										r.POINT_CODE = t1.POINT_CODE
             )) c on m.ND_Key = c.ND_Key

where m.GR052 is null
       or 
       m.GR052+isnull(m.GR053,'0')+isnull(m.GR054,'00') not in ('1002', '2000', '3000','0005')
       and 
       exists(select *
              from @t t1 inner join
					DCL_RISKV r on t1.ND_Key = r.ND_Key and 
										r.SerialRiskv = m.SerialRiskv and 
										r.POINT_CODE = t1.POINT_CODE
             )
                     
group by m.GR051,	c.c_GR0301,
	c.c_GR052,
	c.c_GR053,
	c.c_GR054 ,
	c.c_GR055,c.c_GR025_RISKM,
	c.c_GR022_RISKM,
	c.c_GR022A ,
	c.c_GR023_RISKM,
	c.c_GR024_RISKM ,
	c.c_GR0302,
	c.c_GR031_RISKM
) a
go
SET SHOWPLAN_TEXT OFF
go




StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1082]=CASE WHEN [Expr1020]=(1) AND [Expr1024]=(1) AND [Expr1028]=(1) AND [Expr1032]=(1) AND [Expr1036]=(1) THEN [Expr1072] ELSE 'G' END, [Expr1083]=CASE WHEN [Expr1020]=(1) AND [Expr1024]=(1) AND [Expr1028]=(1) AND [Expr1032]=(1) AND [Expr1036]=(1) THEN [Expr1073] ELSE 'G' END, [Expr1084]=CASE WHEN [Expr1040]=(1) THEN 'Различно по товарам' ELSE CONVERT_IMPLICIT(varchar(19),[Expr1074],0) END, [Expr1085]=CASE WHEN [Expr1044]=(1) THEN 'Различно по товарам' ELSE CONVERT(varchar(10),[Expr1075],104) END, [Expr1086]=CASE WHEN [Expr1048]=(1) THEN 'Различно по товарам' ELSE CONVERT_IMPLICIT(varchar(19),[Expr1076],0) END, [Expr1087]=CASE WHEN [Expr1052]=(1) THEN 'Различно по товарам' ELSE CONVERT_IMPLICIT(varchar(19),[Expr1077],0) END, [Expr1088]=CASE WHEN [Expr1056]
|--Stream Aggregate(GROUP BY:([m].[GR051], [Expr1020], [Expr1024], [Expr1028], [Expr1032], [Expr1036], [Expr1040], [Expr1044], [Expr1048], [Expr1052], [Expr1056], [Expr1060], [Expr1064]) DEFINE:([Expr1072]=MIN([Expr1148]), [Expr1073]=MIN([Expr1149]), [Expr1074]=MIN([Expr1150]), [Expr1075]=MIN([Expr1151]), [Expr1076]=MIN([Expr1152]), [Expr1077]=MIN([Expr1153]), [Expr1078]=MIN([Expr1154]), [Expr1079]=MIN([Expr1155]), [Expr1080]=MIN([Expr1156])))
|--Sort(ORDER BY:([m].[GR051] ASC, [Expr1020] ASC, [Expr1024] ASC, [Expr1028] ASC, [Expr1032] ASC, [Expr1036] ASC, [Expr1040] ASC, [Expr1044] ASC, [Expr1048] ASC, [Expr1052] ASC, [Expr1056] ASC, [Expr1060] ASC, [Expr1064] ASC))
|--Compute Scalar(DEFINE:([Expr1020]=CASE WHEN [Expr1092] THEN (0) ELSE (1) END, [Expr1024]=CASE WHEN [Expr1093] THEN (0) ELSE (1) END, [Expr1028]=CASE WHEN [Expr1094] THEN (0) ELSE (1) END, [Expr1032]=CASE WHEN [Expr1095] THEN (0) ELSE (1) END, [Expr1036]=CASE WHEN [Expr1096] THEN (0) ELSE (1) END, [Expr1040]=CASE WHEN [Expr1097] THEN (0) ELSE (1) END, [Expr1044]=CASE WHEN [Expr1098] THEN (0) ELSE (1) END, [Expr1048]=CASE WHEN [Expr1099] THEN (0) ELSE (1) END, [Expr1052]=CASE WHEN [Expr1100] THEN (0) ELSE (1) END, [Expr1056]=CASE WHEN [Expr1101] THEN (0) ELSE (1) END, [Expr1060]=CASE WHEN [Expr1102] THEN (0) ELSE (1) END, [Expr1064]=CASE WHEN [Expr1103] THEN (0) ELSE (1) END))
|--Hash Match(Inner Join, HASH:([t].[nd_key])=([Expr1157]), RESIDUAL:([Expr1157]=@t.[nd_key] as [t].[nd_key]))
|--Table Scan(OBJECT:(@t AS [t]))
|--Hash Match(Inner Join, HASH:([m].[ND_Key])=([m].[ND_Key]), RESIDUAL:([bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key]=[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key]))
|--Hash Match(Inner Join, HASH:([t].[nd_key])=([Expr1158]), RESIDUAL:([Expr1158]=@t.[nd_key] as [t].[nd_key]))
| |--Table Scan(OBJECT:(@t AS [t]))
| |--Nested Loops(Left Semi Join, OUTER REFERENCES:([m].[ND_Key], [m].[GR031_RISKM]), DEFINE:([Expr1103] = [PROBE VALUE]))
| |--Nested Loops(Left Semi Join, OUTER REFERENCES:([m].[ND_Key], [m].[GR0302]), DEFINE:([Expr1102] = [PROBE VALUE]))
| | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([m].[ND_Key], [m].[GR024_RISKM]), DEFINE:([Expr1101] = [PROBE VALUE]))
| | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([m].[ND_Key], [m].[GR023_RISKM]), DEFINE:([Expr1100] = [PROBE VALUE]))
| | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([m].[ND_Key], [m].[GR022A]), DEFINE:([Expr1099] = [PROBE VALUE]))
| | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([m].[ND_Key], [m].[GR022_RISKM]), DEFINE:([Expr1098] = [PROBE VALUE]))
| | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([m].[ND_Key], [m].[GR025_RISKM]), DEFINE:([Expr1097] = [PROBE VALUE]))
| | | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([m].[ND_Key], [m].[GR055]), DEFINE:([Expr1096] = [PROBE VALUE]))
| | | | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([m].[ND_Key], [m].[GR054]), DEFINE:([Expr1095] = [PROBE VALUE]))
| | | | | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([m].[ND_Key], [m].[GR053]), DEFINE:([Expr1094] = [PROBE VALUE]))
| | | | | | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([m].[ND_Key], [m].[GR052]), DEFINE:([Expr1093] = [PROBE VALUE]))
| | | | | | | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([m].[ND_Key], [m].[GR0301]), DEFINE:([Expr1092] = [PROBE VALUE]))
| | | | | | | | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([m].[SerialRiskv], [m].[GR053], [m].[GR052], [m].[GR054]))
| | | | | | | | | | | | | |--Filter(WHERE:([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052] IS NULL OR [Expr1161] <> '0005' AND [Expr1161] <> '3000' AND [Expr1161] <> '2000' AND [Expr1161] <> '1002'))
| | | | | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1158]=CONVERT_IMPLICIT(smallint,[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key],0), [Expr1161]=([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'00')))
| | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [m]))
| | | | | | | | | | | | | |--Concatenation
| | | | | | | | | | | | | |--Filter(WHERE:(STARTUP EXPR([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052] IS NULL)))
| | | | | | | | | | | | | | |--Constant Scan
| | | | | | | | | | | | | |--Hash Match(Inner Join, HASH:([r].[POINT_CODE], [Expr1162])=([t1].[POINT_CODE], [t1].[nd_key]), RESIDUAL:([bsti].[dbo].[DCL_RISKV].[POINT_CODE] as [r].[POINT_CODE]=@t.[POINT_CODE] as [t1].[POINT_CODE] AND @t.[nd_key] as [t1].[nd_key]=[Expr1162]))
| | | | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1162]=CONVERT_IMPLICIT(smallint,[bsti].[dbo].[DCL_RISKV].[ND_Key] as [r].[ND_Key],0)))
| | | | | | | | | | | | | | |--Table Scan(OBJECT:([bsti].[dbo].[DCL_RISKV] AS [r]), WHERE:([bsti].[dbo].[DCL_RISKV].[SerialRiskv] as [r].[SerialRiskv]=[bsti].[dbo].[DCL_RISKM].[SerialRiskv] as [m].[SerialRiskv]))
| | | | | | | | | | | | | |--Filter(WHERE:(STARTUP EXPR((([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'00')) <> '0005' AND (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'00')) <> '3000' AND (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'00')) <> '2000' AND (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].
| | | | | | | | | | | | | |--Table Scan(OBJECT:(@t AS [t1]))
| | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [t]), WHERE:([bsti].[dbo].[DCL_RISKM].[ND_Key] as [t].[ND_Key]<>[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key] AND isnull([bsti].[dbo].[DCL_RISKM].[GR0301] as [t].[GR0301],'10')=isnull([bsti].[dbo].[DCL_RISKM].[GR0301] as [m].[GR0301],'10')))
| | | | | | | | | | | |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [t]), WHERE:([bsti].[dbo].[DCL_RISKM].[ND_Key] as [t].[ND_Key]<>[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key] AND isnull([bsti].[dbo].[DCL_RISKM].[GR052] as [t].[GR052],'1')=isnull([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052],'1')))
| | | | | | | | | | |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [t]), WHERE:([bsti].[dbo].[DCL_RISKM].[ND_Key] as [t].[ND_Key]<>[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key] AND CASE WHEN isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [t].[GR053],'') = '2' THEN '1' ELSE isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [t].[GR053],'') END=CASE WHEN isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'') = '2' THEN '1' ELSE isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'') END))
| | | | | | | | | |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [t]), WHERE:([bsti].[dbo].[DCL_RISKM].[ND_Key] as [t].[ND_Key]<>[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key] AND isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [t].[GR054],'')=isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'')))
| | | | | | | | |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [t]), WHERE:([bsti].[dbo].[DCL_RISKM].[ND_Key] as [t].[ND_Key]<>[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key] AND isnull([bsti].[dbo].[DCL_RISKM].[GR055] as [t].[GR055],'')=isnull([bsti].[dbo].[DCL_RISKM].[GR055] as [m].[GR055],'')))
| | | | | | | |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [t]), WHERE:([bsti].[dbo].[DCL_RISKM].[ND_Key] as [t].[ND_Key]<>[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key] AND isnull([bsti].[dbo].[DCL_RISKM].[GR025_RISKM] as [t].[GR025_RISKM],'')=isnull([bsti].[dbo].[DCL_RISKM].[GR025_RISKM] as [m].[GR025_RISKM],'')))
| | | | | | |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [t]), WHERE:([bsti].[dbo].[DCL_RISKM].[ND_Key] as [t].[ND_Key]<>[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key] AND isnull([bsti].[dbo].[DCL_RISKM].[GR022_RISKM] as [t].[GR022_RISKM],'1900-01-01 00:00:00.000')=isnull([bsti].[dbo].[DCL_RISKM].[GR022_RISKM] as [m].[GR022_RISKM],'1900-01-01 00:00:00.000')))
| | | | | |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [t]), WHERE:([bsti].[dbo].[DCL_RISKM].[ND_Key] as [t].[ND_Key]<>[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key] AND isnull([bsti].[dbo].[DCL_RISKM].[GR022A] as [t].[GR022A],'')=isnull([bsti].[dbo].[DCL_RISKM].[GR022A] as [m].[GR022A],'')))
| | | | |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [t]), WHERE:([bsti].[dbo].[DCL_RISKM].[ND_Key] as [t].[ND_Key]<>[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key] AND isnull([bsti].[dbo].[DCL_RISKM].[GR023_RISKM] as [t].[GR023_RISKM],'')=isnull([bsti].[dbo].[DCL_RISKM].[GR023_RISKM] as [m].[GR023_RISKM],'')))
| | | |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [t]), WHERE:([bsti].[dbo].[DCL_RISKM].[ND_Key] as [t].[ND_Key]<>[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key] AND isnull([bsti].[dbo].[DCL_RISKM].[GR024_RISKM] as [t].[GR024_RISKM],'')=isnull([bsti].[dbo].[DCL_RISKM].[GR024_RISKM] as [m].[GR024_RISKM],'')))
| | |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [t]), WHERE:([bsti].[dbo].[DCL_RISKM].[ND_Key] as [t].[ND_Key]<>[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key] AND isnull([bsti].[dbo].[DCL_RISKM].[GR0302] as [t].[GR0302],'')=isnull([bsti].[dbo].[DCL_RISKM].[GR0302] as [m].[GR0302],'')))
| |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [t]), WHERE:([bsti].[dbo].[DCL_RISKM].[ND_Key] as [t].[ND_Key]<>[bsti].[dbo].[DCL_RISKM].[ND_Key] as [m].[ND_Key] AND isnull([bsti].[dbo].[DCL_RISKM].[GR031_RISKM] as [t].[GR031_RISKM],'')=isnull([bsti].[dbo].[DCL_RISKM].[GR031_RISKM] as [m].[GR031_RISKM],'')))
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([m].[SerialRiskv], [m].[GR053], [m].[GR052], [m].[GR054]))
|--Filter(WHERE:([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052] IS NULL OR [Expr1159] <> '0005' AND [Expr1159] <> '3000' AND [Expr1159] <> '2000' AND [Expr1159] <> '1002'))
| |--Compute Scalar(DEFINE:([Expr1148]=isnull([bsti].[dbo].[DCL_RISKM].[GR0301] as [m].[GR0301],''), [Expr1149]=isnull([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052],'1'), [Expr1150]=isnull([bsti].[dbo].[DCL_RISKM].[GR025_RISKM] as [m].[GR025_RISKM],''), [Expr1151]=isnull([bsti].[dbo].[DCL_RISKM].[GR022_RISKM] as [m].[GR022_RISKM],'1900-01-01 00:00:00.000'), [Expr1152]=isnull([bsti].[dbo].[DCL_RISKM].[GR022A] as [m].[GR022A],''), [Expr1153]=isnull([bsti].[dbo].[DCL_RISKM].[GR023_RISKM] as [m].[GR023_RISKM],''), [Expr1154]=isnull([bsti].[dbo].[DCL_RISKM].[GR024_RISKM] as [m].[GR024_RISKM],''), [Expr1155]=isnull([bsti].[dbo].[DCL_RISKM].[GR0302] as [m].[GR0302],''), [Expr1156]=isnull([bsti].[dbo].[DCL_RISKM].[GR031_RISKM] as [m].[GR031_RISKM],''), [Ex
| |--Clustered Index Scan(OBJECT:([bsti].[dbo].[DCL_RISKM].[PK_DCL_RISKM] AS [m]))
|--Concatenation
|--Filter(WHERE:(STARTUP EXPR([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052] IS NULL)))
| |--Constant Scan
|--Hash Match(Inner Join, HASH:([r].[POINT_CODE], [Expr1160])=([t1].[POINT_CODE], [t1].[nd_key]), RESIDUAL:([bsti].[dbo].[DCL_RISKV].[POINT_CODE] as [r].[POINT_CODE]=@t.[POINT_CODE] as [t1].[POINT_CODE] AND @t.[nd_key] as [t1].[nd_key]=[Expr1160]))
|--Compute Scalar(DEFINE:([Expr1160]=CONVERT_IMPLICIT(smallint,[bsti].[dbo].[DCL_RISKV].[ND_Key] as [r].[ND_Key],0)))
| |--Table Scan(OBJECT:([bsti].[dbo].[DCL_RISKV] AS [r]), WHERE:([bsti].[dbo].[DCL_RISKV].[SerialRiskv] as [r].[SerialRiskv]=[bsti].[dbo].[DCL_RISKM].[SerialRiskv] as [m].[SerialRiskv]))
|--Filter(WHERE:(STARTUP EXPR((([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'00')) <> '0005' AND (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'00')) <> '3000' AND (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR054],'00')) <> '2000' AND (([bsti].[dbo].[DCL_RISKM].[GR052] as [m].[GR052]+isnull([bsti].[dbo].[DCL_RISKM].[GR053] as [m].[GR053],'0'))+isnull([bsti].[dbo].[DCL_RISKM].[GR054] as [m].[GR0
|--Table Scan(OBJECT:(@t AS [t1]))