Help with a sql query - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with a sql query

Author

Topic

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-11-16 : 23:27:22

Hi i am trying to build a query that will find the different from order to production (How long it take to produce). Then get the average of that query and times it by two. Then get that number and find all the products that are greater then that number.

I found how to get how long it takes to produce that product:

Select datediff(day, Datebuilt, DateShipped) as Build_Time
From Car

But I don't know how to find the average of the Build_Time and use that number to filter the car table.

Thank you for any help you can give

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-16 : 23:33:04

[code]
select avg(Build_Time)
from
(
Select datediff(day, Datebuilt, DateShipped) as Build_Time
From Car
) a[/code]

KH
[spoiler]Time is always against us[/spoiler]

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-11-17 : 19:01:33

thank you for the help. I was wondering is the 'a' at the end of the query the variable that holds the results of the query? I get 42 when i run this statement. Now I want to user that 42 and show where build_time > (42).

Again thank you for your help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-17 : 19:49:34

the 'a' is the table alias for the derived query.


select *
from   Car c
where  datediff(day, c.Datebuilt, c.DateShipped) >
       (
            select avg(Build_Time)
            from
            (
                  Select datediff(day, Datebuilt, DateShipped) as Build_Time
                  From Car 
            ) a
       )

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-17 : 20:11:17

or can be simplified into


select	avg(datediff(day, Datebuilt, DateShipped))
from	Car

select	*
from	Car
where	datediff(day, Datebuilt, DateShipped) > 
	(
		select	avg(datediff(day, Datebuilt, DateShipped)) 
		from	Car
	)

KH
[spoiler]Time is always against us[/spoiler]

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-11-17 : 22:28:25

OH, I get it. thank you so much for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources