I am working with a population of items that goes through conversions from 1 type to another based on percentages in a table.
The conversion hapen iteratively until there are conversions left.
What i would like to be able to do input a type and population and return a table of the converted population.
I’m really looking for ideas on how to approach this (using SQL Server). I’ll give a simple example below.
CREATE TABLE populationChanges (id INT, originaltype VARCHAR(50),NewType VARCHAR(50),percentOfPopulation DECIMAL(5,4))
INSERT INTO [dbo].[populationChanges]
(id,
[originaltype],
[NewType],
[percentOfPopulation]
)
VALUES
(1,'trout','bass', 0.4000),
(2,'trout','shark',0.6000),
(3,'shark','clownFish',0.3000),
(4,'shark','PufferFish',0.7000),
(5,'PufferFish','Tuna', 0.7000),
(6,'PufferFish','Coy',0.1000),
(7,'PufferFish','Shrimp', 0.2000),
(8,'coy','lobster', 1.0000),
(9,'Shrimp','King Crab',0.6000)
for example if I start with 1000 Shrimp since there is only 1 conversion it simply would return 600,’Lobster’
but if there are multiple conversions I need to go throught multiple stps
for example
1000 puffer fish becomes 700 tuna,100 coy and 200 shrimp, but the coy and shrimp go through another conversion so what a really end up with is
700 tuna,
100 lobster,
120 shrimp
my actual data has thousands of types and and up to 20 layers of conversions, and I can’t wrap my head around where to start.
any help would be appreciated
thanks