SQL Server advanced query
Budget: $30 – $250 USD
I have a SQL server project that needs some REALLY advanced work done on it. Needs to be done very quickly so bonus for quick turnaround.
Summary: I have two tables of data one is a look up table that I need to read values for the age of an animal and apply a payment index based on its age in days
I have two tables for an animal project where I would have in the first table (tblAnimals)
|animalID|AgeinDays|AnimalType| |--------|---------|----------| |1234|155|1 In a secondary lookup table (tblIndexValues) I have a list of values that would be attributed to all animal ID and are linked by the AnimalType:
ID AgeStart AgeEnd Valueindex AnimalType
1 0 90 2.27945205479452 1
2 91 182 4.16164383561644 1
3 183 273 6.43835616438356 1
4 274 365 6.04657534246575 1
5 366 456 5.38082191780822 1
6 457 547 5.52602739726027 1
7 548 638 5.77260273972603 1
8 639 730 5.40821917808219 1
9 731 821 5.08219178082192 1
10 822 912 5.23287671232877 1
11 913 1003 5.46575342465753 1
12 1004 1095 5.58904109589041 1
Question: If I have an animal that is 155 days old (AnimalType 1 in the summarised table above) I would need to be able to look up the Valueindex for its AnimalType and apply an interative/cumulative sum for the values making up its age in days.
In this example of a 155 day old animal I would expect the result to be:
90 * 2.27945205479452 (ie 30 days of 2.27945205479452 Valueindex)
65 * 4.16164383561644 (remainder of the age 155-90)
155 / 475.55
Both tables are ready I just need a function or query code to run to do the internal looping.
SQL SERVER EXPERTS ONLY, NOT MYSQL!!
Summary: I have two tables of data one is a look up table that I need to read values for the age of an animal and apply a payment index based on its age in days
I have two tables for an animal project where I would have in the first table (tblAnimals)
|animalID|AgeinDays|AnimalType| |--------|---------|----------| |1234|155|1 In a secondary lookup table (tblIndexValues) I have a list of values that would be attributed to all animal ID and are linked by the AnimalType:
ID AgeStart AgeEnd Valueindex AnimalType
1 0 90 2.27945205479452 1
2 91 182 4.16164383561644 1
3 183 273 6.43835616438356 1
4 274 365 6.04657534246575 1
5 366 456 5.38082191780822 1
6 457 547 5.52602739726027 1
7 548 638 5.77260273972603 1
8 639 730 5.40821917808219 1
9 731 821 5.08219178082192 1
10 822 912 5.23287671232877 1
11 913 1003 5.46575342465753 1
12 1004 1095 5.58904109589041 1
Question: If I have an animal that is 155 days old (AnimalType 1 in the summarised table above) I would need to be able to look up the Valueindex for its AnimalType and apply an interative/cumulative sum for the values making up its age in days.
In this example of a 155 day old animal I would expect the result to be:
90 * 2.27945205479452 (ie 30 days of 2.27945205479452 Valueindex)
65 * 4.16164383561644 (remainder of the age 155-90)
155 / 475.55
Both tables are ready I just need a function or query code to run to do the internal looping.
SQL SERVER EXPERTS ONLY, NOT MYSQL!!