September 18, 2009 at 2:34 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 6:09 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 8:17 am
Sergey Vavinskiy
The "days" is DATEDIFF(day, [prew.month], [month])
Be careful using the above for example using the first row of your data
1 2009/01/30 200 2008/12/29 190 30 0.33
SELECT DATEDIFF(day,'2009/01/30','2008/12/29')
results in considering 32 days for the weight gain.
When in fact what I think you need to consider is December 30th and December 31st plus the 29 days of January which totals 31 days for the period of time. If my thoughts are correct then change the T-SQL to
SELECT DATEDIFF(day,'2008/12/29','2009/01/30') - 1 AS 'Days to gain weight' which will result in 31 days
September 18, 2009 at 11:54 am
Almoust correct.
It looks like the author doesn't use a corner dates finding a such called "pure date difference". I assume that looking into his data and calculated number of days - it is 30 for the first row, but not 31 or 32.
In this case I have to correct my post:
The columns "days" and "ADG" both are calculated columns.
The "days" is DATEDIFF(day, [prew.month], [month])-2
The "ADG" is ([weight]-[prew.weight])/(DATEDIFF(day, [prew.month], [month])-2)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply