Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Eve Online Create Tech2 Build Requirements Table

By: k162space on Apr 13th, 2012  |  syntax: SQL  |  size: 2.56 KB  |  hits: 606  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  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
create a new version of this paste RAW Paste Data