BOM for SQL server – Guest post

1 Reply

As I don’t use MS SQL server, except when initially pulling the data, the sql elsewhere on my site may not work with it. Especially that for grabbing the bill of materials for blueprints. This is due to little things like there being no built in greater() function, and SQL server doing integer division, when you divide integers.

So, when Cilegon offered me their updated SQL to work with MS SQL server, I said ‘yes please’, so I can pass it on to all you good people. And you not so good people.

 

So here it is. You’ll need to create the function once. The rest is for the BOM.


DROP FUNCTION dbo.greater
GO

CREATE FUNCTION dbo.greater(
@a INT,
@b INT
) RETURNS INT
as
BEGIN
RETURN
CASE WHEN @a >= ISNULL(@b,@a)
THEN @a
ELSE @b
END
END
GO

DECLARE @typeID int = 26374
DECLARE @char_pe smallint = 5
DECLARE @bp_me smallint = 2


WITH cte1_base_mats AS(
SELECT typeID = t.typeID,
name = t.typeName,
m.quantity,
bt.wasteFactor
from invTypes t
inner join invTypeMaterials m on m.materialTypeID = t.typeID
left join invBlueprintTypes bt on bt.productTypeID = m.typeID
where m.typeID=@typeID
union
select typeID = t.typeID,
name = t.typeName,
m.quantity * r.quantity * -1 quantity,
wasteFactor
from invTypes t
inner join invTypeMaterials m on m.materialTypeID = t.typeID
inner join ramTypeRequirements r on r.requiredTypeID = m.typeID
inner join invBlueprintTypes bt on bt.blueprintTypeID = r.typeID
where r.activityID = 1 and bt.productTypeID=@typeID and r.recycle=1
)

SELECT typeID,
name,
quantity = CASE WHEN @bp_me >= 0
THEN ROUND(dbo.greater(0, sum(quantity)) + (dbo.greater(0, sum(quantity)) * ((CAST(wasteFactor AS FLOAT) / (@bp_me + 1)) / 100)),0)
ELSE ROUND(dbo.greater(0, sum(quantity)) + (dbo.greater(0, sum(quantity)) * (CAST(wasteFactor AS FLOAT) / CAST(100 AS FLOAT)) * (1 - @bp_me)),0)
END,
base = 1,
perfect = dbo.greater(0,sum(quantity))
FROM cte1_base_mats
GROUP BY typeID,
name,
wasteFactor
ORDER BY name

SELECT typeID = t.typeID,
name = t.typeName,
quantity = r.quantity * r.damagePerJob,
base = 0,
perfect = r.quantity * r.damagePerJob
FROM ramTypeRequirements r
INNER JOIN invTypes t ON t.typeID = r.requiredTypeID
INNER JOIN invBlueprintTypes bt on bt.blueprintTypeID = r.typeID
INNER JOIN invGroups g on g.groupID = t.groupID
WHERE r.activityID = 1
and bt.productTypeID = @typeID
and g.categoryID != 16