Correlated subquery column referencing outer date range

Question

Post reply

Correlated subquery column referencing outer date range

jrep

SSC-Addicted

Points: 413
More actions
August 25, 2006 at 1:20 pm

#114223

Any ideas how can I pass date range values from the where clause of an outer query to the inner correlated subquery ... without using a stored procedure?
Using the simplified sql below I need the average freight charge between the dates for all of the ShipCountry's orders. (I have hard coded the dates for demo purposes only as it is these that I need referenced from the outer query's where clause.)
select
     OrderDate,
     ShipCountry,
     ShipCity,
     Freight,
     /* how do I get to the outer query's date range ? */
     (SELECT AVG(Freight) FROM Orders WHERE ShipCountry = O.ShipCountry AND OrderDate between '01-jan-1997' and '01-jan-2000') AS CountryAverageFreight
from
     Northwind.dbo.Orders O
where
     ShipCity = 'Paris' and OrderDate between '01-jan-1997' and '01-jan-2000'
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

sqltung Hall of Fame Points: 3962 More actions · Answer 1

will you be able to use variables, like this?:

declare @startDate datetime, @endDate datetime

set @startDate = '1/1/1997'

set @endDate = '1/1/2000'

select

OrderDate,

ShipCountry,

ShipCity,

Freight,

/* how do I get to the outer query's date range ? */

(

SELECT AVG(Freight)

FROM Orders

WHERE ShipCountry = O.ShipCountry

AND OrderDate between @startDate and @endDate

) AS CountryAverageFreight

from

Northwind.dbo.Orders O

where ShipCity = 'Paris'

and OrderDate between @startDate and @endDate

jrep SSC-Addicted Points: 413 More actions · Answer 2

Yes but unfortunately I can't use variables / stored procs because this view is to be used as a datasource for Reporting Services Report Builder which only allows tables and views.

W F Ten Centuries Points: 1075 More actions · Answer 3

How are you going to get the dates in at all without variables or hard coding?

sqltung Hall of Fame Points: 3962 More actions · Answer 4

How will you use the date range? Will you do something like:

select *

from vMyView v

where v.StartDate = '1/1/1997' and v.EndDate = '1/1/2000'

jrep SSC-Addicted Points: 413 More actions · Answer 5

by passing them to the view as a where clause.

ie select * from vwView where ShipCity = 'Paris' and OrderDate between '01-jan-1997' and '01-jan-2000' .

W F Ten Centuries Points: 1075 More actions · Answer 6

No way you can do that in a view. When you query a view, you're querying static information just like querying a table; you can't calculate average on the fly like that.

jrep SSC-Addicted Points: 413 More actions · Answer 7

guys the main issue here is that the subquery needs to be able to reference the range of dates (not just a single date) of the outer query.

thanks

W F Ten Centuries Points: 1075 More actions · Answer 8

Instead of using a view, why not just query the tables directly? You're doing a query anyway.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 9

>>for Reporting Services Report Builder which only allows tables and views.

Huh ? Since when did RS prevent use of stored procs ?

jrep SSC-Addicted Points: 413 More actions · Answer 10

Report Builder can only use tables / views as Model datasources.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 11

I don't have access to SQL Server right now so I couldn't test this... I think it may work...

select

OrderDate,

ShipCountry,

ShipCity,

Freight,

/* how do I get to the outer query's date range ? */

(SELECT AVG(Freight) FROM Orders WHERE ShipCountry = O.ShipCountry AND OrderDate IN o.OrderDate) AS CountryAverageFreight

from

Northwind.dbo.Orders O

where

ShipCity = 'Paris' and OrderDate between '01-jan-1997' and '01-jan-2000'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jrep SSC-Addicted Points: 413 More actions · Answer 12

Thanks Jeff but no - I see what you're getting at but it doesn't quite do it.