Brain hurting SQL challenge - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Brain hurting SQL challenge

Author

Topic

mtortolano
Starting Member

10 Posts

Posted - 2005-09-08 : 10:14:38

I'm fairly new to SQL and I'm trying to figure out if there is a way I can solve this problem without having to resort to writing iterative code to do it. The problem is how to match up a sparse set of observations on particular dates to a set of dates I want to graph over. Any help would be very welcome.

I'm monitoring properties for sale in my area so I can do some nice stats on them. My database looks like this:

Properties - PropertyId PrimaryKey INT, Address VARCHAR(50)
Observations - ObservationsId PrimaryKey INT, PropertyId INT, Price INT, ObservedAt DATETIME

To save space I only put an entry in the Observations table each time I detect a change in price (although I'm regretting this decision now :-). So I have information like this;

Properties:
1, 21 MyStreet
2, 32 MyStreet
3, 16 YourStreet

Observations
1, 1, 300000, 10 July 2005
2, 2, 100000, 10 July 2005
3, 3, 200000, 10 July 2005
4, 1, 290000, 17 July 2005
5, 3, 180000, 24 July 2005
6, 1, 280000, 31 July 2005
7, 2, 110000, 31 July 2005

Now the problem is I want to generate a result set which has the correct price for each property for every week that I've been monitoring to make graphing and stats generation easy.

So what I think I want is a result set which is the product of all the weekly dates with the closest matching observation per propertyid e.g.;

10 July 2005, 1, 1, 300000, 10 July 2005
10 July 2005, 2, 2, 100000, 10 July 2005
10 July 2005, 3, 3, 200000, 10 July 2005

17 July 2005, 4, 1, 290000, 17 July 2005
17 July 2005, 2, 2, 100000, 10 July 2005
17 July 2005, 3, 3, 200000, 10 July 2005

24 July 2005, 4, 1, 290000, 17 July 2005
24 July 2005, 2, 2, 100000, 10 July 2005
24 July 2005, 5, 3, 180000, 24 July 2005

31 July 2005, 6, 1, 280000, 31 July 2005
31 July 2005, 7, 2, 110000, 31 July 2005
31 July 2005, 5, 3, 180000, 24 July 2005

Anyone have any idea if I can do this just using plain old SQL (I'm tring to avoid any iterative code).

I've worked out how to generate the list of dates but I can't figure out how to match them up to the relevant closest observation date.

Thanks in advance, Michael.

www.showshifter.com

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-09-08 : 10:21:28

Closest....= max existing date <= current date?

The following might be a reasonable starting point....
select distinct(date) as datea from observations a
left join observations b on a.datea >= max(b.date)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-08 : 10:24:26

Here's a sample


Declare @p table (pId int, property varchar(100))
Insert Into @p
Select 1, '21 MyStreet' Union All
Select 2, '32 MyStreet' Union All
Select 3, '16 YourStreet'

Declare @o table (oId int, pId int, price money, oDate datetime)
Insert Into @o
Select 1, 1, 300000, '10 July 2005' Union All
Select 2, 2, 100000, '10 July 2005' Union All
Select 3, 3, 200000, '10 July 2005' Union All
Select 4, 1, 290000, '17 July 2005' Union All
Select 5, 3, 180000, '24 July 2005' Union All
Select 6, 1, 280000, '31 July 2005' Union All
Select 7, 2, 110000, '31 July 2005'

Select 
	B.pId,
	B.property,
	A.oDate,
	Price = (Select top 1 price from @o where pId = B.pId and oDate<=A.oDate Order By oDate desc)
From (Select distinct oDate From @o) A, @p B
Order By oDate, pId

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

mtortolano
Starting Member

10 Posts

Posted - 2005-09-08 : 10:56:08

Cheers guys, I didn't realise you could use a result (i.e. B.pId) as in input to a Subquery in the SELECT part of the expression.

I take it you can't do that in a WHERE clause as it won't have been resolved at that point.

mtortolano
Starting Member

10 Posts

Posted - 2005-09-10 : 16:26:38

All I have to do it figure out how to do it in Oracle 10 now as well :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources