SHARE
TWEET

Untitled

a guest May 26th, 2019 68 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DECLARE @tbl TABLE (FullName NVARCHAR(MAX))
  2. INSERT @tbl(FullName)
  3. VALUES
  4. (N'All-America Research Team: Basic Materials - Best UAC (Chemicals)'),
  5. ('All-America Research Team: Basic Materials - Chemicals'),
  6. ('All-America Fixed-Income Research Team: Client Services - Best Credit-Rating Agencies'),
  7. ('All-America Fixed-Income Research Team'),
  8. ('All-America Research Team'),
  9. ('All-America Trading Team'),
  10. ('All-Asia Research Team'),
  11. ('All-Asia Reserach Team'),
  12. ('All-Brazil Research Team'),
  13. ('All-China Research Team'),
  14. ('All-Europe Equity Research Team'),
  15. ('All-Europe Equity Trading Tearm Buy-Side'),
  16. ('All-Europe Fixed-Income Research Team'),
  17. ('All-Europe Fixed-Income Reserach Team'),
  18. ('All-Europe Research Team'),
  19. ('All-Europe Trading Team'),
  20. ('All-India Research Team'),
  21. ('All-India Reserach Team'),
  22. ('All-India Web Research Team'),
  23. ('All-Japan Research Team'),
  24. ('All-Japan Reserach Team'),
  25. ('All-Russia Research Team'),
  26. ('Emerging EMEA Research Team'),
  27. ('Emerging-Markets Equity Research Team'),
  28. ('Latin America Research Team'),
  29. ('Latin America Reserach Team')
  30.  
  31. ;WITH cte AS
  32. (
  33. SELECT
  34.     FullName,
  35.     value
  36. FROM @tbl
  37. CROSS APPLY STRING_SPLIT((TRANSLATE(FullName, '+-*/%():','        ')),' ')
  38. ),
  39. cte2 AS
  40. (
  41. SELECT
  42.     cte.FullName,
  43.     cte.value,
  44.     SOUNDEX(cte.value) AS 'Sound',
  45.     DENSE_RANK() OVER (ORDER BY cte.FullName) AS 'GroupRank',
  46.     ROW_NUMBER() OVER (PARTITION BY cte.FullName ORDER BY (SELECT NULL)) AS 'Weight'
  47. FROM cte WHERE cte.value != ''
  48. ),
  49. cte3 AS
  50. (
  51. SELECT c.*,
  52.        cc.FullName AS _FullName,
  53.        cc.value AS _value,
  54.        cc.Sound AS _Sound,
  55.        cc.GroupRank AS _GroupRank,
  56.        cc.Weight AS _Weight,
  57.     LAG(cc.Sound, 1,0) OVER (ORDER BY cc.GroupRank,cc.Weight,c.GroupRank,c.Weight) lag,
  58.     LEAD(cc.Sound, 1,0) OVER (ORDER BY cc.GroupRank,cc.Weight,c.GroupRank,c.Weight) lead
  59. FROM  cte2 c
  60. LEFT JOIN cte2 cc
  61. ON c.GroupRank != cc.GroupRank OR (cc.GroupRank != c.GroupRank AND cc.Weight != c.Weight)
  62.  
  63. ),cte4 AS
  64. (
  65. SELECT *,
  66. CASE
  67.     WHEN Sound = _Sound THEN 0
  68.     WHEN Sound = lag OR Sound = lead THEN ABS(_Weight - Weight)
  69.     END AS nm
  70. FROM cte3
  71. WHERE cte3.Weight =cte3._Weight
  72. OR CASE
  73.     WHEN Sound = _Sound THEN 0
  74.     WHEN Sound = lag OR Sound = lead THEN _Weight
  75.     END IS NOT NULL
  76. ),
  77. cte5 AS
  78. (
  79. SELECT *, ROW_NUMBER() OVER (PARTITION BY GroupRank,Weight ORDER BY GroupRank,Weight) rnk2 FROM
  80. cte4
  81. )
  82. SELECT DISTINCT value,
  83. COUNT(value) OVER (PARTITION BY SOUNDEX(value)) AS w
  84. FROM cte5  
  85. WHERE rnk2 = 1 AND nm IS NOT NULL
  86. ORDER BY w DESC, value ASC
  87.  
  88.  
  89.  
  90. --SELECT SOUNDEX('Research'),  SOUNDEX('Reserach')
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top