asked
AVG in Weeks
I have the following 2 tables which records every buying and selling of Goods as datetime 1- Selling Table ------------------------------------------------------ Date_of_Selling ------------------------------------------------------ 15-9-2006 25-8-2007 13-8-2009 16-1-2009 22-1-2010 .. .. _______________________________________________ and here 2nd Table 2-Buying Table ---------------------------------------------------- Date_of_Buying ---------------------------------------------------- 22-9-2004 25-16-2006 1-8-2010 22-8-2010 ... .. .. How can I find the average number of weeks between selling and buying ... using timestampfunction or whatever ? the result should be like : 5.3 weeks between selling and buying , Thanks in advance ...
Hello, The function you want is [DATEDIFF][1]. So you want to sum up the difference between the selling and buying dates. I'd suggest getting the difference in Days, not weeks, then summing it, changing to a data type which can represent a decimal value, and dividing by 7 (to return to weeks, but as a decimal) then by the number in your set, as DATEDIFF returns an integer, so if you want a result in parts of a week then you will need to return it as a more granular answer and then divide to get to your required unit. You haven't told me how to relate a selling event to a buying event, but the query would be something like: SELECT CAST ( --Need the answer in a format that will cope with a decimal point SUM ( --All the records to get an average DATEDIFF (DD, SellingTable.Date_of_Selling, BuyingTable.Date_of_Buying) --This actually calculates the diffference ) AS DECIMAL(10,2) --Decimal format to allow decimal point ) /7 -- Convert our days total into a weeks total / count(*) -- Divide by number of records in the set to get an average FROM Dave. [1]:
http://technet.microsoft.com/en-us/library/ms189794.aspx
15 People are following this question.
