API tools faq
paste
Advertisement
Binkenstein

SQL query to evaluate talent/trait combos

Binkenstein
Aug 28th, 2018
609
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.20 KB | None | 0 0
raw download clone embed print report
  1. SELECT
  2. L15.[Name] AS L15Talent
  3. ,L30.[Name] AS L30Talent
  4. ,L60.[Name] AS L60Talent
  5. ,L90.[Name] AS L90Talent
  6. ,L100.[Name] AS L100Talent
  7. ,t1.[Name] AS Trait1
  8. ,t2.[Name] AS Trait2
  9. ,t3.[Name] AS Trait3
  10. --Calculate the combination DPS by taking the base DPS
  11. ,rb.[DPS]
  12. --Add the gains for each individual talent or trait
  13. + (r15.[DPS] - rb.[DPS])
  14. + (r30.[DPS] - rb.[DPS])
  15. + (r60.[DPS] - rb.[DPS])
  16. + (r90.[DPS] - rb.[DPS])
  17. + (r100.[DPS] - rb.[DPS])
  18. + (rt1.[DPS] - rb.[DPS])
  19. + (rt2.[DPS] - rb.[DPS])
  20. + (rt3.[DPS] - rb.[DPS])
  21. --Add the pain gain/loss after removing the base DPS & individual talent/trait gains
  22. + (s1530.[DPS] - rb.[DPS] - (r15.[DPS] - rb.[DPS]) - (r30.[DPS] - rb.[DPS]))
  23. + (s1560.[DPS] - rb.[DPS] - (r15.[DPS] - rb.[DPS]) - (r60.[DPS] - rb.[DPS]))
  24. + (s1590.[DPS] - rb.[DPS] - (r15.[DPS] - rb.[DPS]) - (r90.[DPS] - rb.[DPS]))
  25. + (s15100.[DPS] - rb.[DPS] - (r15.[DPS] - rb.[DPS]) - (r100.[DPS] - rb.[DPS]))
  26. + (s15t1.[DPS] - rb.[DPS] - (r15.[DPS] - rb.[DPS]) - (rt1.[DPS] - rb.[DPS]))
  27. + (s15t2.[DPS] - rb.[DPS] - (r15.[DPS] - rb.[DPS]) - (rt2.[DPS] - rb.[DPS]))
  28. + (s15t3.[DPS] - rb.[DPS] - (r15.[DPS] - rb.[DPS]) - (rt3.[DPS] - rb.[DPS]))
  29. + (s3060.[DPS] - rb.[DPS] - (r30.[DPS] - rb.[DPS]) - (r60.[DPS] - rb.[DPS]))
  30. + (s3090.[DPS] - rb.[DPS] - (r30.[DPS] - rb.[DPS]) - (r90.[DPS] - rb.[DPS]))
  31. + (s30100.[DPS] - rb.[DPS] - (r30.[DPS] - rb.[DPS]) - (r100.[DPS] - rb.[DPS]))
  32. + (s30t1.[DPS] - rb.[DPS] - (r30.[DPS] - rb.[DPS]) - (rt1.[DPS] - rb.[DPS]))
  33. + (s30t2.[DPS] - rb.[DPS] - (r30.[DPS] - rb.[DPS]) - (rt2.[DPS] - rb.[DPS]))
  34. + (s30t3.[DPS] - rb.[DPS] - (r30.[DPS] - rb.[DPS]) - (rt3.[DPS] - rb.[DPS]))
  35. + (s6090.[DPS] - rb.[DPS] - (r60.[DPS] - rb.[DPS]) - (r90.[DPS] - rb.[DPS]))
  36. + (s60100.[DPS] - rb.[DPS] - (r60.[DPS] - rb.[DPS]) - (r100.[DPS] - rb.[DPS]))
  37. + (s60t1.[DPS] - rb.[DPS] - (r60.[DPS] - rb.[DPS]) - (rt1.[DPS] - rb.[DPS]))
  38. + (s60t2.[DPS] - rb.[DPS] - (r60.[DPS] - rb.[DPS]) - (rt2.[DPS] - rb.[DPS]))
  39. + (s60t3.[DPS] - rb.[DPS] - (r60.[DPS] - rb.[DPS]) - (rt3.[DPS] - rb.[DPS]))
  40. + (s90100.[DPS] - rb.[DPS] - (r90.[DPS] - rb.[DPS]) - (r100.[DPS] - rb.[DPS]))
  41. + (s90t1.[DPS] - rb.[DPS] - (r90.[DPS] - rb.[DPS]) - (rt1.[DPS] - rb.[DPS]))
  42. + (s90t2.[DPS] - rb.[DPS] - (r90.[DPS] - rb.[DPS]) - (rt2.[DPS] - rb.[DPS]))
  43. + (s90t3.[DPS] - rb.[DPS] - (r90.[DPS] - rb.[DPS]) - (rt3.[DPS] - rb.[DPS]))
  44. + (s100t1.[DPS] - rb.[DPS] - (r100.[DPS] - rb.[DPS]) - (rt1.[DPS] - rb.[DPS]))
  45. + (s100t2.[DPS] - rb.[DPS] - (r100.[DPS] - rb.[DPS]) - (rt2.[DPS] - rb.[DPS]))
  46. + (s100t3.[DPS] - rb.[DPS] - (r100.[DPS] - rb.[DPS]) - (rt3.[DPS] - rb.[DPS]))
  47. + (st1t2.[DPS] - rb.[DPS] - (rt1.[DPS] - rb.[DPS]) - (rt2.[DPS] - rb.[DPS]))
  48. + (st1t3.[DPS] - rb.[DPS] - (rt1.[DPS] - rb.[DPS]) - (rt3.[DPS] - rb.[DPS]))
  49. + (st2t3.[DPS] - rb.[DPS] - (rt2.[DPS] - rb.[DPS]) - (rt3.[DPS] - rb.[DPS]))
  50. AS DPS
  51. FROM (SELECT * FROM Talent WHERE ClassID = 1 AND SpecID = 1 AND Row = 15) AS a --Return all L15 talents
  52. --Cross joins generate all possible combinations of talents
  53. CROSS JOIN (SELECT * FROM Talent WHERE ClassID = 1 AND SpecID = 1 AND Row = 30) AS b
  54. CROSS JOIN (SELECT * FROM Talent WHERE ClassID = 1 AND SpecID = 1 AND Row = 60) AS c
  55. CROSS JOIN (SELECT * FROM Talent WHERE ClassID = 1 AND SpecID = 1 AND Row = 90) AS d
  56. CROSS JOIN (SELECT * FROM Talent WHERE ClassID = 1 AND SpecID = 1 AND Row = 100) AS e
  57. CROSS JOIN (SELECT
  58. a.[ID] AS Trait1ID
  59. ,b.[ID] AS Trait2ID
  60. ,c.[ID] AS Trait3ID
  61. FROM (SELECT ID FROM Trait WHERE ClassID = 1 AND SpecID = 1) AS a --Cross join all traits for the spec
  62. CROSS JOIN (SELECT ID FROM Trait WHERE ClassID = 1 AND SpecID = 1) AS b
  63. CROSS JOIN (SELECT ID FROM Trait WHERE ClassID = 1 AND SpecID = 1) AS c
  64. WHERE a.[ID] <= b.[ID] AND b.[ID] <= c.[ID] --But only return those in ascending ID order so 1,1,2 is returned without using 2,1,1 or 1,2,1
  65. ) AS f
  66. CROSS JOIN (SELECT * FROM [Results]
  67. WHERE [Talent1ID] IS NULL AND [Talent2ID] IS NULL AND [Trait1ID] IS NULL
  68. AND [Trait2ID] IS NULL AND [ClassID] = 1 AND [SpecID] = 1) AS rb --Include base DPS value
  69. LEFT JOIN [Talent] AS L15 ON L15.[ID] = a.[ID]
  70. LEFT JOIN [Talent] AS L30 ON L30.[ID] = b.[ID]
  71. LEFT JOIN [Talent] AS L60 ON L60.[ID] = c.[ID]
  72. LEFT JOIN [Talent] AS L90 ON L90.[ID] = d.[ID]
  73. LEFT JOIN [Talent] AS L100 ON L100.[ID] = e.[ID]
  74. LEFT JOIN [Trait] AS t1 ON t1.[ID] = f.[Trait1ID]
  75. LEFT JOIN [Trait] AS t2 ON t2.[ID] = f.[Trait2ID]
  76. LEFT JOIN [Trait] AS t3 ON t3.[ID] = f.[Trait3ID]
  77. LEFT JOIN [Results] AS r15 ON r15.[Talent1ID] = a.[ID] AND r15.[Talent2ID] IS NULL AND r15.[Trait1ID] IS NULL AND r15.[Trait2ID] IS NULL --Return the individual talent/trait result rows
  78. LEFT JOIN [Results] AS r30 ON r30.[Talent1ID] = b.[ID] AND r30.[Talent2ID] IS NULL AND r30.[Trait1ID] IS NULL AND r30.[Trait2ID] IS NULL
  79. LEFT JOIN [Results] AS r60 ON r60.[Talent1ID] = c.[ID] AND r60.[Talent2ID] IS NULL AND r60.[Trait1ID] IS NULL AND r60.[Trait2ID] IS NULL
  80. LEFT JOIN [Results] AS r90 ON r90.[Talent1ID] = d.[ID] AND r90.[Talent2ID] IS NULL AND r90.[Trait1ID] IS NULL AND r90.[Trait2ID] IS NULL
  81. LEFT JOIN [Results] AS r100 ON r100.[Talent1ID] = e.[ID] AND r100.[Talent2ID] IS NULL AND r100.[Trait1ID] IS NULL AND r100.[Trait2ID] IS NULL
  82. LEFT JOIN [Results] AS rt1 ON rt1.[Trait1ID] = f.[Trait1ID] AND rt1.[Trait2ID] IS NULL AND rt1.[Talent1ID] IS NULL AND rt1.[Talent2ID] IS NULL
  83. LEFT JOIN [Results] AS rt2 ON rt2.[Trait1ID] = f.[Trait2ID] AND rt2.[Trait2ID] IS NULL AND rt2.[Talent1ID] IS NULL AND rt2.[Talent2ID] IS NULL
  84. LEFT JOIN [Results] AS rt3 ON rt3.[Trait1ID] = f.[Trait3ID] AND rt3.[Trait2ID] IS NULL AND rt3.[Talent1ID] IS NULL AND rt3.[Talent2ID] IS NULL
  85. LEFT JOIN [Results] AS s1530 ON s1530.[Talent1ID] = a.[ID] AND s1530.[Talent2ID] = b.[ID] --Add in talent/trait pairs
  86. LEFT JOIN [Results] AS s1560 ON s1560.[Talent1ID] = a.[ID] AND s1560.[Talent2ID] = c.[ID]
  87. LEFT JOIN [Results] AS s1590 ON s1590.[Talent1ID] = a.[ID] AND s1590.[Talent2ID] = d.[ID]
  88. LEFT JOIN [Results] AS s15100 ON s15100.[Talent1ID] = a.[ID] AND s15100.[Talent2ID] = e.[ID]
  89. LEFT JOIN [Results] AS s15t1 ON s15t1.[Talent1ID] = a.[ID] AND s15t1.[Trait2ID] = f.[Trait1ID]
  90. LEFT JOIN [Results] AS s15t2 ON s15t2.[Talent1ID] = a.[ID] AND s15t2.[Trait2ID] = f.[Trait2ID]
  91. LEFT JOIN [Results] AS s15t3 ON s15t3.[Talent1ID] = a.[ID] AND s15t3.[Trait2ID] = f.[Trait3ID]
  92. LEFT JOIN [Results] AS s3060 ON s3060.[Talent1ID] = b.[ID] AND s3060.[Talent2ID] = c.[ID]
  93. LEFT JOIN [Results] AS s3090 ON s3090.[Talent1ID] = b.[ID] AND s3090.[Talent2ID] = d.[ID]
  94. LEFT JOIN [Results] AS s30100 ON s30100.[Talent1ID] = b.[ID] AND s30100.[Talent2ID] = e.[ID]
  95. LEFT JOIN [Results] AS s30t1 ON s30t1.[Talent1ID] = b.[ID] AND s30t1.[Trait2ID] = f.[Trait1ID]
  96. LEFT JOIN [Results] AS s30t2 ON s30t2.[Talent1ID] = b.[ID] AND s30t2.[Trait2ID] = f.[Trait2ID]
  97. LEFT JOIN [Results] AS s30t3 ON s30t3.[Talent1ID] = b.[ID] AND s30t3.[Trait2ID] = f.[Trait3ID]
  98. LEFT JOIN [Results] AS s6090 ON s6090.[Talent1ID] = c.[ID] AND s6090.[Talent2ID] = d.[ID]
  99. LEFT JOIN [Results] AS s60100 ON s60100.[Talent1ID] = c.[ID] AND s60100.[Talent2ID] = e.[ID]
  100. LEFT JOIN [Results] AS s60t1 ON s60t1.[Talent1ID] = c.[ID] AND s60t1.[Trait2ID] = f.[Trait1ID]
  101. LEFT JOIN [Results] AS s60t2 ON s60t2.[Talent1ID] = c.[ID] AND s60t2.[Trait2ID] = f.[Trait2ID]
  102. LEFT JOIN [Results] AS s60t3 ON s60t3.[Talent1ID] = c.[ID] AND s60t3.[Trait2ID] = f.[Trait3ID]
  103. LEFT JOIN [Results] AS s90100 ON s90100.[Talent1ID] = d.[ID] AND s90100.[Talent2ID] = e.[ID]
  104. LEFT JOIN [Results] AS s90t1 ON s90t1.[Talent1ID] = d.[ID] AND s90t1.[Trait2ID] = f.[Trait1ID]
  105. LEFT JOIN [Results] AS s90t2 ON s90t2.[Talent1ID] = d.[ID] AND s90t2.[Trait2ID] = f.[Trait2ID]
  106. LEFT JOIN [Results] AS s90t3 ON s90t3.[Talent1ID] = d.[ID] AND s90t3.[Trait2ID] = f.[Trait3ID]
  107. LEFT JOIN [Results] AS s100t1 ON s100t1.[Talent1ID] = e.[ID] AND s100t1.[Trait2ID] = f.[Trait1ID]
  108. LEFT JOIN [Results] AS s100t2 ON s100t2.[Talent1ID] = e.[ID] AND s100t2.[Trait2ID] = f.[Trait2ID]
  109. LEFT JOIN [Results] AS s100t3 ON s100t3.[Talent1ID] = e.[ID] AND s100t3.[Trait2ID] = f.[Trait3ID]
  110. LEFT JOIN [Results] AS st1t2 ON st1t2.[Trait1ID] = f.[Trait1ID] AND st1t2.[Trait2ID] = f.[Trait2ID]
  111. LEFT JOIN [Results] AS st1t3 ON st1t3.[Trait1ID] = f.[Trait1ID] AND st1t3.[Trait2ID] = f.[Trait3ID]
  112. LEFT JOIN [Results] AS st2t3 ON st2t3.[Trait1ID] = f.[Trait2ID] AND st2t3.[Trait2ID] = f.[Trait3ID]
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!