Solution to complex query

Question

Solution to complex query

chris.r.armstrong

Newbie

Points: 6
More actions
February 3, 2012 at 9:36 am

#264025

Hi all,
This is my first post here, and yes I'm a noobie to SQL and SQL Server in general, so go easy on me! 😉
I have a db composed of just 1 table that is comprised of property transaction data in my county. The query I'm trying to write would show all properties that were purchased and then subsequently rented out within 1 year.
There are columns for status and closing date which I assume will need to be used in this query. Status fields were interested for this query would be 'SOLD' and 'RENTED'. Both status's use the 'closing date' column as well.
The only way to do this query is to look for matches in addresses, check if the transaction was 'SOLD' before the matching record was 'RENTED' and then to only include it if the closing date for the matching 'RENTED' transaction was within a year of the closing date for the 'SOLD' transaction.
I've been trying to figure out the best way to handle this, be it with a CASE statement, or even splitting this table into other tables by STATUS type and using join statements. At the end of the day, I guess I don't know what I'm doing and need a little guidance 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Please will you provide table DDL (in the form of a CREATE TABLE statement) and sample data (in the form of INSERT statements) to help us to help you.

Thanks

John

Michael L John One Orange Chip Points: 27056 More actions · Answer 2

For starters, without any DDL and some sample data, we cant really give you a lot of help.

That being said, here are a few thigns to look at:

1. If you are matching on an address, are you sure that the address actually match?

For example, is 100 Main st the same record as 100 Main Street? You may not get an accurate set of results.

Here's a basic stab at what you need, MAYBE, that hopefully will get you started:

SELECT <fields you need>

FROM OneBigTable A

INNER JOIN OneBigTable B ON A.Address = B.Address

AND A.Status = 'Sold'

AND B.Status = 'Rented'

WHERE DATEDIFF(dd, A.Date, B.Date) >= 365

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

chris.r.armstrong Newbie Points: 6 More actions · Answer 3

Ah, sorry...I'll have to go check how to extract the table DDL, I'm guessing there's probably one of those BEFORE YOU POST threads I should've read somewhere? 😉

Michael, does your solution require that I divide the one big table into smaller tables before proceeding?

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

chris.r.armstrong (2/3/2012)
Ah, sorry...I'll have to go check how to extract the table DDL, I'm guessing there's probably one of those BEFORE YOU POST threads I should've read somewhere? 😉
Michael, does your solution require that I divide the one big table into smaller tables before proceeding?

Take a look at the first link in my signature for said article. 🙂

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Here you go: how to post DDL and sample data http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michael L John One Orange Chip Points: 27056 More actions · Answer 6

No, you are joiing the table to itself. This is probably an easy (Argh!) query, but without DDL and sample data, it's a guessing game.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/