SHOW:
|
|
- or go back to the newest paste.
| 1 | IF OBJECT_ID('[dbo].[typeBuildReqs]') IS NOT NULL
| |
| 2 | DROP TABLE [dbo].[typeBuildReqs] | |
| 3 | ||
| 4 | CREATE TABLE [dbo].[typeBuildReqs] | |
| 5 | ( | |
| 6 | - | typeID smallint, |
| 6 | + | typeID bigint, |
| 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 |
