G2A Many GEOs
SHARE
TWEET

Eve Online Create Tech2 Build Requirements Table

k162space Apr 13th, 2012 1,405 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. IF OBJECT_ID('[dbo].[typeBuildReqs]') IS NOT NULL
  2. DROP TABLE [dbo].[typeBuildReqs]
  3.  
  4. CREATE TABLE [dbo].[typeBuildReqs]
  5. (
  6. typeID SMALLINT,
  7. activityID tinyint,
  8. requiredTypeID SMALLINT,
  9. quantity INT,
  10. damagePerJob FLOAT,
  11. wasted   tinyint,
  12.  
  13. CONSTRAINT typetypeBuildReqs_PK PRIMARY KEY CLUSTERED
  14. (typeID, activityID, requiredTypeID, wasted)
  15. )
  16.  
  17. GO
  18.  
  19. INSERT INTO typeBuildReqs (typeID, activityID, requiredTypeID, quantity,
  20. damagePerJob,wasted)
  21.  
  22. (SELECT itm1.typeID, 1 AS activityID, itm1.requiredTypeID, (itm1.quantity-
  23. isnull(itm2.quantity,0)) AS quantity, 1 AS damagePerJob, 1 AS wasted
  24.  
  25. FROM
  26. (SELECT invBlueprintTypes.blueprintTypeID AS typeID, 1 AS
  27. activityID, invTypeMaterials.materialTypeID AS requiredTypeID,
  28. invTypeMaterials.quantity, 1 AS damagePerJob, 1 AS wasted
  29. FROM invTypeMaterials
  30.  
  31. INNER JOIN invBlueprintTypes
  32. ON invTypeMaterials.typeID =
  33. invBlueprintTypes.productTypeID) AS itm1
  34.  
  35. LEFT OUTER JOIN
  36.  
  37. (SELECT typeID, activityID, requiredTypeID ,
  38. SUM(quantity) AS quantity, damagePerJob, wasted
  39. FROM
  40. (SELECT t.typeID, 1 AS activityID, itm.materialTypeID
  41. AS requiredTypeID , (itm.quantity * t.quantity)
  42. AS quantity, 1 AS damagePerJob, 1 AS wasted
  43.  
  44. FROM   
  45. (SELECT DISTINCT rtr.typeID, rtr.requiredTypeID,
  46. rtr.quantity
  47. FROM ramTypeRequirements AS rtr
  48.  
  49. INNER JOIN      invTypes AS iT
  50. ON rtr.requiredTypeID = iT.typeID
  51.  
  52. INNER JOIN      invGroups AS iG
  53. ON iT.groupID = iG.groupID
  54.  
  55. WHERE ((rtr.activityID = 1) AND (rtr.recycle = 1)
  56. AND (iG.categoryID <> 4)
  57. AND (iG.categoryID <> 17))) AS t
  58.  
  59. INNER JOIN invTypeMaterials AS itm
  60. ON t.requiredTypeID = itm.typeID) AS itm3
  61. GROUP BY typeID, activityID, requiredTypeID , damagePerJob, wasted)
  62. AS itm2
  63.  
  64. ON itm2.typeID = itm1.typeID AND
  65. itm2.activityID = itm1.activityID AND
  66. itm2.requiredTypeID = itm1.requiredTypeID
  67.  
  68. WHERE (itm1.quantity-isnull(itm2.quantity,0)) > 0)
  69.  
  70. UNION
  71.  
  72. (SELECT rtr2.typeID, rtr2.activityID, rtr2.requiredTypeID, rtr2.quantity,
  73. rtr2.damagePerJob, 0 AS wasted
  74. FROM ramTypeRequirements AS rtr2
  75.  
  76. INNER JOIN      invTypes AS types
  77. ON rtr2.requiredTypeID = types.typeID
  78.  
  79. INNER JOIN      invBlueprintTypes AS bps
  80. ON rtr2.typeID = bps.blueprintTypeID
  81.  
  82. INNER JOIN      invGroups AS groups
  83. ON types.groupID = groups.groupID
  84.  
  85. LEFT OUTER JOIN (SELECT typeID, materialTypeID, quantity
  86. FROM invTypeMaterials) AS itm
  87. ON (bps.productTypeID = itm.typeID AND
  88. rtr2.requiredTypeID = itm.materialTypeID AND
  89. (rtr2.quantity <= itm.quantity OR
  90. rtr2.quantity > itm.quantity OR itm.quantity IS NULL))
  91.  
  92. WHERE ((groups.categoryID <> 16) AND (rtr2.activityID = 1) AND
  93. (rtr2.quantity > 0)))  
  94. GO
RAW Paste Data
Ledger Nano X - The secure hardware wallet
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