Блог

Я использую SQL Server 2012. Ниже приведен пример набора данных

 Prefix_Suffix   First_Nm    Last_Nm Acct_Registration_Line_1    Acct_Registration_Line_2    secondlevel TopLevel
NULL    Jane    Smith   NULL    NULL    smith1-rep  abc-quarterly
NULL    John    Smith   NULL    NULL    smith1-rep  abc-quarterly
Jane Smith  NULL    NULL    IRA FBO Jane Smith (EQUITY) PERSHING LLC AS CUSTODIAN   smith1-rep  abc-quarterly
Jane Smith  NULL    NULL    IRA FBO Jane Smith (FI) PERSHING LLC AS CUSTODIAN   smith1-rep  abc-quarterly
John Smith  NULL    NULL    IRA FBO John Smith (EQUITY) PERSHING LLC AS CUSTODIAN   smith1-rep  abc-quarterly
John Smith  NULL    NULL    IRA FBO John Smith (FI) PERSHING LLC AS CUSTODIAN   smith1-rep  abc-quarterly
  

Эти данные отображаются в различных последовательностях, иногда есть только строки, в которых заполнены First_Nm (имя) и Last_Nm (фамилия), а Prefix_Suffix имеет значение NULL. Иногда заполняется Prefix_Suffix, а поля имени и фамилии имеют значение NULL. Я могу справиться, если все это одно или все остальные равны НУЛЮ. Строки, с которыми я борюсь, — это когда в группе появляются оба нулевых сценария. Группа обозначается наличием того же значения в столбце второго уровня.

Если внутри группы Prefix_Suffix содержит значение, а имя и фамилия равны нулю, а также есть строки, в которых имя и фамилия содержат значение, а значение Prefix_Suffix равно нулю, то мне не нужны строки, которые имеют значение для Prefix_Sufix . Поэтому, когда возникает пример набора данных, я бы хотел отфильтровать его, чтобы исключить 4 строки, в которых заполнен Prefix_Suffix . Опять же, я хочу этот результат только в группах, которые имеют чередующиеся НУЛИ между Prefix_Suffix amp; First_nm, Last_Nm.

Желаемый результат

 Prefix_Suffix   First_Nm    Last_Nm Acct_Registration_Line_1    Acct_Registration_Line_2    secondlevel TopLevel
NULL    Jane    Smith   NULL    NULL    smith1-rep  abc-quarterly
NULL    John    Smith   NULL    NULL    smith1-rep  abc-quarterly
  

Вот текущий оператор select . Я пытался изолировать 2 нулевых сценария с помощью UNION ALL, но этого было недостаточно.

 WITH DATA AS
(
SELECT *,
COUNT(Acct_Nbr) OVER (PARTITION BY acct_nbr)AcctCount
 FROM ##temptable 
 WHERE  1 = 1
 AND acct_holder_role_cd <> 'sec'
)
SELECT * 
INTO ##PrefixedAccounts
FROM DATA
WHERE AcctCount = 1 AND Last_Nm IS NULL;
SELECT 
RcdTypId,Acct_Nbr,Acct_Short_Nm,Acct_Holder_Typ_Cd,Acct_Holder_Role_Cd, Prefix_Suffix
,First_Nm,Middle_Nm,Last_Nm,Acct_Registration_Line_1,Acct_Registration_Line_2
INTO ##temptable2
 FROM ##temptable 
WHERE NOT EXISTS
(
SELECT ##PrefixedAccounts.Acct_Nbr
FROM ##PrefixedAccounts 
WHERE ##PrefixedAccounts.Acct_Nbr = ##temptable.Acct_Nbr
);
WITH DATA AS
(
SELECT  
RcdTypId,Acct_Nbr,Acct_Short_Nm,Acct_Holder_Typ_Cd,Acct_Holder_Role_Cd,Prefix_Suffix,
NULL AS First_Nm, Middle_Nm, Last_Nm,Acct_Registration_Line_1,Acct_Registration_Line_2
FROM ##PrefixedAccounts
UNION ALL
SELECT
RcdTypId,Acct_Nbr,Acct_Short_Nm,Acct_Holder_Typ_Cd,Acct_Holder_Role_Cd,NULL AS Prefix_Suffix,First_Nm,Middle_Nm,Last_Nm,
NULL AS Acct_Registration_Line_1,NULL AS Acct_Registration_Line_2
FROM ##temptable2
)
SELECT * INTO ##ClientNames FROM DATA
DROP TABLE ##temptable2
DROP TABLE ##PrefixedAccounts

-- SELECT * FROM ##ClientNames ORDER BY prefix_Suffix desc
-- DROP TABLE ##ClientNames

SELECT distinct
cl.Prefix_Suffix,cl.First_Nm,cl.Last_Nm,cl.Acct_Registration_Line_1,cl.Acct_Registration_Line_2
,at.secondlevel,at.TopLevel FROM ##ClientNames cl
INNER JOIN ##AddeparTemplate at ON cl.Acct_nbr = at.owned
WHERE 1 = 1
AND (cl.Last_Nm IS NOT NULL OR cl.Acct_Registration_Line_1 IS NOT NULL)
ORDER BY cl.prefix_suffix, at.secondlevel
DROP TABLE ##ClientNames
  

Это то, что вы хотите?

 select t.*
from (select t.*,
             row_number() over (partition by coalesce(prefix_suffix, first_nm   ' '   last_nm)
                                order by (case when first_nm is not null then 1 else 2 end)
                               ) as seqnum
      from ##temptable t
     ) t
where seqnum = 1;