May 27, 2003 at 11:37 am
here is my statement
Select Top 4 DateDiff(n, [Sub Date],[Reply date]) as Minutes
From SNFReportView Where ([SubDate] Is Not Null} and ([Reply date] Is Not Null) and (status <> 'ACK' and status <> 'Accepted' or status is null) order by [Sub Date] Desc
so what is returning is 4 numbers taken from the bottom of the list. but what we want is the average of those, can you help us?
thanks,
matt
May 27, 2003 at 11:54 am
Try something like this:
select avg(minutes) average_minutes from
(Select Top 4 DateDiff(n, [Sub Date],[Reply date]) as Minutes
From SNFReportView Where ([SubDate] Is Not Null} and ([Reply date] Is Not Null) and (status <> 'ACK' and status <> 'Accepted' or status is null) order by [Sub Date] Desc
) a
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
May 27, 2003 at 11:55 am
thanks,
we will give that one a try
May 27, 2003 at 11:58 am
what is the " ) a" at the end of your statement?
May 27, 2003 at 12:03 pm
That just associated a name, in this case "a" , with the record set return from the subquery (select top 4 ...). Without that you get an error.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
May 27, 2003 at 1:51 pm
no that didn't work for us sorry. go any other ideas?
May 27, 2003 at 1:56 pm
this worked sorry, we just did it wrong. ok thanks again.
May 28, 2003 at 7:22 am
could you give a little more info on how the 'a' at the end works? i want to know whats going on with that.
thanks
May 28, 2003 at 8:26 am
When you uses a SELECT statement in the from clause to create a record set you need to give the record set a name. Here is an example:
select * from (select top 3 * from pubs.dbo.sales) a
Here I have given the record set a name "a". Basically the record set is in memory, and SQL server needs a name to assocaited with the record set create by the command "select top 3 * from pubs.dbo.sales". If you don't give the record set a name SQL Server throws an error.
Now if you wanted to return only a single column like qty, your code would quality the column name with an "a", like so "a.qty", instead of pubs.dbo.sales.qty. In fact qualifying the qty column with pubs.dbo.sales would get an error. Try the following two statements:
select a.qty from (select top 3 * from pubs.dbo.sales order by qty desc) a
select pubs.dbo.sales.qty from (select top 3 * from pubs.dbo.sales order by qty desc) a
This first one works, and the second one gets you an error.
Hope this explaination works. If not let me know.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
May 28, 2003 at 9:11 am
select * from (select top 3 * from pubs.dbo.sales) a
when i took off the a on this statment it put this in the place of 'a' = DERIVEDTBL
May 28, 2003 at 9:19 am
select qty from (select top 3 * from pubs.dbo.sales order by qty desc)
works without the a. so i get the idea. in this case you wouldn't have to qualify?
May 28, 2003 at 9:38 am
What tool are you using to submit your commands? I'm using QA, and if you don't provide a name for the record set (DERIVEDTBL) produced by "select top 3 * from pubs.dbo.sales" then QA displays the following error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
May 28, 2003 at 9:45 am
i am using Enterprise Manager, that must be the reason!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply