Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
slophoo
Starting Member
2 Posts |
 Posted - 2009-02-25 : 23:03:01
|
| I am comparing two dates and returning the difference in number of days(DaysFoundToFixed). This part works. I just simply need to return the AVG number of days afterwards. Since DaysFoundToFixed is not an actual column, I can't seem to run an "AVG" on it.Can anyone suggest how I might also return the average within the same query? Here's what I have so far:SELECT DefectId, AssignedToId, Name, DateFixed, DateFound, 'Day' AS Expr1, DATEDIFF(d, DateFound, DateFixed) AS DaysFoundToFixedFROM DefectsWHERE (CAST(FLOOR(CAST(DateFixed AS float)) AS datetime) > '2008-12-01') AND (CAST(FLOOR(CAST(DateFound AS float)) AS datetime) > '2008-12-01')ORDER BY DaysFoundToFixed |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-26 : 03:26:57
|
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
SQLforGirls
Starting Member
48 Posts |
Posted - 2009-02-26 : 03:33:56
|
| Peso is right, you need to provide more information here. But in particular, you need to tell us what values you want to average. If you have a table containing ALL defects, do you want to select the average number of daysFoundToFixed across ALL defects? Or perhaps only across all defects that share the same AssignedToId?The reason a simple AVG( DATEDIFF(d, DateFound, DateFixed) ) wouldn't give you any useful data is that you would only be averaging a single value each time. You need to define the set of rows on which to perform the AVG.Hope this helps you narrow down your question more quickly. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-02-26 : 05:21:14
|
| moved from script library forum.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
|
|
|
slophoo
Starting Member
2 Posts |
Posted - 2009-02-26 : 13:22:07
|
| Yes. I want to average across all items where datefixed is within this month. So, list every time with a datediff shown. Then provide one average for all those items 'fixed' within February. Later on, i'll want to provide an average for each month, so we can see how the average changes from month to month. thanks! |
|
|
|
|
|
|
|
|
almost there and need help on a query!