September 18, 2009 at 2:38 am
Hi
I have animals that are weighed on a monthly basis. It may be on the 28th-30th of the month, so may not be on the same day of the month each month. I need to work out the Average daily gain (ADG) of each animal. So the difference in weight each monrh / number of days between weighings.
e.g. Animal month weight prev.month prev.weight days ADG
1 2009/01/30 200 2008/12/29 190 30 0.33
2 2009/01/30 245 2008/12/29 230 30 -0.5
1 2009/02/27 212 2008/01/30 200 31 0.39
2 2009/02/27 260 2008/01/30 245 31 0.48
I hope it is clear what i am after here.
Thanks
September 18, 2009 at 2:41 am
I did something very similar yesterday , try this link
September 18, 2009 at 6:07 am
The columns "days" and "ADG" both are calculated columns.
The "days" is DATEDIFF(day, [prew.month],[month])
The "ADG" is ([weight]-[prew.weight])/DATEDIFF(day, [prew.month],[month])
September 18, 2009 at 1:25 pm
Troy,
I'm a litte fuzzy on what exactly you need done here. The attached script is assuming that you're looking for an animal's ADG over every weighing it has ever had. If it's not exactly what you're looking for, hopefully it can at least give you a jumping off point. Let me know if you have any questions.
CREATE TABLE Animals (
AnimalID int,
[Days] int,
APD decimal(4,2)
)
INSERT INTO Animals
VALUES (1, 30, 0.33)
INSERT INTO Animals
VALUES (2, 30, -0.5)
INSERT INTO Animals
VALUES (1, 31, 0.39)
INSERT INTO Animals
VALUES (2, 31, 0.48)
SELECT AnimalID, CAST(SUM(([Days] * APD)) / SUM([Days]) as decimal(4,2)) AS ADG
FROM Animals
GROUP BY AnimalID
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply