July 5, 2011 at 2:27 am
I posted before but maybe not as good as possible.
I'm currently developing an application and database that's 100% a hobby project, just for me to see what I can and can't do with the current SQL Server environment and to help me in planning some stuff in Eve Online.
Here's the problem, I have 5 level of products:
*R
*P1-P4
X amount of R results in 1 P1
1 unit of P2 requires 8 units of 2 types of P1
P3 requires X amount of units of 3 types of P2, and so on.
Each product has a price, that I update daily.
Now I need a way that I can calculate any estimated price of a product based on its own idea and how many levels lower I want to look.
Let say I want to know a P3 based on the value of the P1.
I have made a procedure last week that can do that for me but I'm not happy about the result.
I taught using hiearchyID but that doesn't work since not only have I got multiple roots, I got points where the root are direct results of the smae product but then used in different intermediate levels.
So anyone got any idea on how I can solve this.
July 5, 2011 at 2:45 pm
Please post DDL, DML to create sample data, expected results and what you have tried so far.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 5, 2011 at 3:10 pm
Look into Bill of Materials hierarchies as it sounds a lot like what you're dealing with as a starting point.
The multiple root node bit has me a bit confused as explained, and data/structure that explictly explains it would help.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 6, 2011 at 1:22 am
Ok explanation & code
In EvE Online everything you can buy and sell on the markets are items created by the players, by either mining or processing raw materials.
A while back the company that runs EvE, CCP, introduced planetry interaction, where we were given the means to produce certain items that before you could only buy of the NPC's in game or in find in missions.
In the game we extract materials from planets,level R, each R-type gets processed into 1 P1-type
A P1 can be used in the production of several P2's, a P2 is always a combination of 2 P1's
A P3 uses 2 P2's, while a P4 can consist out of 3 P3's or 2 P3's and a P1.
The ratios are
3000 R = 20 P1
40 P1 + 40 P1 = 5 P2
I haven't started on moving to the level above yet, so that's why I don't really have sample data on them.
I tried hierarchid but ran into problems when applying it to the P3, cause on the P3 level you get items that start off from the same R-item.
The sample data only contains R,P1 and P2 items
The item 'Enriched Uranium' (P2) is created by combining 'Toxic Metals' and 'Precious Metals'
'Toxic Metals' are created from 'Heavy Metals' and 'Precious Metals' from 'Noble Metals'
The idea is to predict the value of 'Enriched Uranium' based on the price for 'Heavy Metals' and 'Noble Metals' (actually to also predict it based on the price of 'Toxic Metals' and 'Precious Metals')
PILevels
CREATE TABLE [planetaryInteraction].[PILevels](
[PIID] [int] NOT NULL,
[PIDescription] [varchar](2) NULL,
[AmountNeededNextLevel] [int] NULL,
[AmountNeededNextLevelOne] [int] NULL,
[ImportTax] [decimal](10, 2) NULL,
[ExportTax] [decimal](10, 2) NULL,
CONSTRAINT [pk_PILevels] PRIMARY KEY CLUSTERED
(
[PIID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The materials need
CREATE TABLE [planetaryInteraction].[PIMaterialNeeded](
[PICreate] [int] NULL,
[PINeed] [int] NULL,
CONSTRAINT [pimn_unq] UNIQUE NONCLUSTERED
(
[PICreate] ASC,
[PINeed] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The table with materials
CREATE TABLE [planetaryInteraction].[PIMaterials](
[PIMatID] [int] NOT NULL,
[PIName] [varchar](50) NULL,
[PILevel] [int] NOT NULL,
[IsChild] [bit] NULL,
CONSTRAINT [pk_pimat] PRIMARY KEY CLUSTERED
(
[PIMatID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [planetaryInteraction].[PIMaterials] WITH CHECK ADD CONSTRAINT [fk_pimat] FOREIGN KEY([PILevel])
REFERENCES [planetaryInteraction].[PILevels] ([PIID])
GO
ALTER TABLE [planetaryInteraction].[PIMaterials] CHECK CONSTRAINT [fk_pimat]
GO
ALTER TABLE [planetaryInteraction].[PIMaterials] ADD DEFAULT ((1)) FOR [IsChild]
GO
PILevel Data
PIID PIDescription AmountNeededNextLevel AmountNeededNextLevelOne ImportTax ExportTax
----------- ------------- --------------------- ------------------------ --------------------------------------- ---------------------------------------
1 R 3000 150 0.00 0.00
2 P1 40 8 0.00 0.00
3 P2 0 0 0.00 0.00
4 P3 0 0 0.00 0.00
5 P4 0 0 0.00 0.00
Some basic material data(PIMaterials)
PIMatID PIName PILevel IsChild
----------- -------------------------------------------------- ----------- -------
1 Heavy Metals 1 0
2 Noble Metals 1 0
3 Toxic Metals 2 1
4 Precious Metals 2 1
5 Enriched Uranium 3 1
PIMaterialNeeded
PICreate PINeed
----------- -----------
3 1
4 2
5 3
5 4
select pim.PIMatID,pim.PIName,pimn.PINeed
from planetaryInteraction.PIMaterials pim
inner join planetaryInteraction.PIMaterialNeeded pimn on pim.PIMatID = pimn.PICreate
where PICreate in (1,2,3,4,5)
Gives off
PIMatID PIName PINeed
----------- -------------------------------------------------- -----------
3 Toxic Metals 1
4 Precious Metals 2
5 Enriched Uranium 3
5 Enriched Uranium 4
Better would be
PIMatID PIName PINeeds1 PINeeds2 PINeeds3
----------- -------------------------------------------------- --------------------------
3 Toxic Metals 1 - -
4 Precious Metals 2 - -
5 Enriched Uranium 3 4 -
July 7, 2011 at 8:03 am
declare @pi varchar(20)
declare @piid int, @pil int
set @pi = 'Enriched Uranium'
select @piid = PIMatID,@pil = PILevel from planetaryInteraction.PIMaterials where PIName = @pi
print @piid;
with PlanetI(PIMatID, PIName, PILevel, IsChild,AmountNeededNextLevelOne,AmountNeededRequestedLevelOne,Level,Cost)
as
(
select pim.PIMatID, pim.PIName, pim.PILevel, pim.IsChild,pil.AmountNeededNextLevelOne,0, @pil as Level,pip.PIPrice
from planetaryInteraction.PIMaterials pim
inner join planetaryInteraction.PILevels pil on pim.PILevel = pil.PIID
inner join planetaryInteraction.PIMatPrices pip on pim.PIMatID = pip.PIID
where pim.PIMatID = @piid
union all
select pim.PIMatID, pim.PIName, pim.PILevel, pim.IsChild,pil.AmountNeededNextLevelOne,
((select pil2.AmountNeededNextLevelOne from planetaryInteraction.PILevels pil2 where pil2.PIID = pil.PIID+1 )*pil.AmountNeededNextLevelOne),
Level - 1,pip.PIPrice
from planetaryInteraction.PIMaterials pim
inner join planetaryInteraction.PIMaterialNeeded pimn on pim.PIMatID = pimn.PINeed
inner join planetaryInteraction.PILevels pil on pim.PILevel = pil.PIID
inner join planetaryInteraction.PIMatPrices pip on pim.PIMatID = pip.PIID
inner join PlanetI on PlanetI.PIMatID = pimn.PICreate
)
select PIMatID, PIName, Cost As Market_Value,
(select sum(AmountNeededNextLevelOne*Cost) from PlanetI where PlanetI.Level < @pil)As Expected_Value,
(select sum(AmountNeededRequestedLevelOne*Cost) from PlanetI where PILevel = 1) As Expected_Value_1,
(select sum(AmountNeededRequestedLevelOne*Cost) from PlanetI where PILevel = 2) As Expected_Value_2
from PlanetI
where PlanetI.PIMatID = @piid
order by PlanetI.Level desc
--select *,(select sum(AmountNeededRequestedLevelOne*Cost) from PlanetI where PILevel = 1)from PlanetI
--where PlanetI.PIMatID = @piid
July 7, 2011 at 9:33 am
The structure I'd use for this would be a Materials table, and a separate hierarchies table.
So, a Raven battleship would be listed as a "Material". It would be the top node for a hierarchy including all the materials used to create it, including the blueprint. You might have two or more such hierarchies, one for BPO, one for BPC, for example. The Raven itself would appear in the hierarchy for a Caldari Navy Raven, since that includes the Raven as one of the "materials" for it.
By separating the hierarchy out into a separate table, with a "ResultID" column which FKs to your Materials table, a BillID column used as the hierachy nodes, and a "BillOfMaterials" column of type HierarchyID, you could easily solve the "multiple roots" issue.
Essentially, a many-to-many self-join. It could either be an adjacency hierarchy, or a HierarchyID structure. Either one works.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2011 at 5:37 am
That's the path I've chosen and with a cte I managed to get part of the data I want, it allso gives me the opportunity to make some further calculations.
I could include all items ingame but I'm not interested in that, with this part I'm developing I want to be able to generate reports that could tell me whether or not it is profitable to turn PI product into higher tier product.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply