Finding Duplicates and then Comparing Dates

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

Finding Duplicates and then Comparing Dates

Author

Topic

Madeline89
Starting Member

2 Posts

Posted - 2011-03-14 : 21:25:12

Hi,

I am working on a research paper looking at home sales in Milwaukee over an 8 year time span. I want to specifically analyze home sales that occurred within less than two years of each other.

I have property sales records for the full 8 years - about 50,000 sales total. Each property has a unique tax id number. Using this id number, I was able to identify properties that had been resold during the 8 year time span.

My problem is that I don't know how to figure out whether those homes that were resold during that 8 year span were actually resold within two years. I need to be able to compare the dates of the home sales for each id number in my original duplicates list.

My initial idea is to do something like this:

for each tax id number in my duplicates list (from 8 year span)
look at home sales associated with each id and compare the dates of those home sales - for the home sales that are within two years of each other, tag as two year duplicates and store in a new table.

I don't really understand how to do the syntax for that though. I'm really confused because I read somewhere that SQL doesn't have for loops - in that case, what should I use? Also, how do I get it to go from a given tax id number in my list back to my original master table that has all the entries for the individual home sales?

Thank you so much in advance!!! I have been working on this particular programming issue (reading coding books, trying different things) for literally a month off and on and have made pretty much zero progress. I got a book on SQL and data analysis but I just can't seem to figure out how to apply what's in the book to my situation.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-15 : 07:08:51

Madeline, can you answer the following questions?

1. Is your data stored in a Microsoft SQL database? If you are using some other type of DBMS- Oracle, MySQL etc., there are very few if any on this forum who can help.

2. Assuming MSSQL, I assume the data is stored in a table. Can you post the DDL for the table? To do this, open up SQL Server Management Studio, connect to the database server, and in the object explorer panel navigate to YourServer->Databases->YourDatabase->Tables->YourTableName. Right click on it and select Script table as -> Create To -> New Window. Post the output.

3. Open a new query window and run the command "select top 10 * from YourTable" and post the output.

4. In a tabular format, write out what you would like the output to look like.

There is an abundance of people on this forum who are SQL experts; one of them should be able to help you figure out the syntax and logic.

Madeline89
Starting Member

2 Posts

Posted - 2011-03-15 : 11:27:09

Hi again,

Thanks so much for answering!

Okay so this might sound really stupid...but it's in Microsoft Access right now, so is that a Microsoft SQL database? I used the help function in Access but I wasn't sure how to post the DDL. I'm using a University computer and I'm pretty sure it has Microsoft SQL server loaded onto it also. I just don't understand the difference between Access and SQL server. Access lets me write queries in SQL and Access calls itself a database but is it not a true database?

3. ID PropType Taxkey Address CondoProject District Nbhd Style Extwall Stories Year_Built Nr_of_rms Fin_sqft Units Bdrms Fbath Hbath Lotsize Sale_date Sale_price

1 Residential 1700903000 5806 N 33RD ST 1 900 Cape Cod Frame 1 1948 0 2016 1 3 3 0 7200 2002-07 93500
1 Residential 1719592000 5944 N 39TH ST 1 900 Cape Cod Stone 1.5 1938 0 2107 1 3 1 1 7936 2004-08 125500
1 Residential 1920011000 3706 W SHERIDAN AV 1 1160 AP 1 Stucco 2 1905 0 2938 4 3 1 0 9480 2003-12 125000
1 Residential 1920742000 5239 N 37TH ST 1 1160 Dplx Bungalow Aluminum / Vinyl 1.5 1928 0 2176 2 4 2 0 4800 2007-09 131000
1 Residential 2050437000 1974 W FAIRMOUNT AV 1 1340 Cape Cod Aluminum / Vinyl 1.5 1941 0 2245 1 5 2 0 10800 2005-08 45000
1 Residential 2310306000 4595 N 23RD ST 1 1620 Cape Cod Stone 1 1940 0 1488 1 4 1 0 4800 2010-05 100000
1 Residential 2310563000 2136 W CONGRESS ST 1 1620 Cape Cod Stone 1 1946 0 1670 1 4 1 1 6480 2009-09 165000
1 Residential 2430030000 4026 N 11TH ST 1 1620 Cape Cod Aluminum / Vinyl 1.5 1940 0 1987 1 4 1 1 5400 2006-05 98000
1 Residential 2440932000 4202 N 17TH ST 1 1620 Cape Cod Stone 1.5 1936 0 2166 1 4 2 0 5977 2008-12 85000
2 Residential 1379912110 6479 N 42ND ST 1 560 Cape Cod Aluminum / Vinyl 1.5 1941 0 1711 1 5 2 0 5760 2003-12 90000

4. I would like the output to be:
PropType Taxkey Address CondoProject District Nbhd Style Extwall Stories Year_Built Nr_of_rms Fin_sqft Units Bdrms Fbath Hbath Lotsize Sale_date Sale_price

where each word/phrase represents a column and each row represents a property sale. Each property sale should be one that is a "flip" - meaning that it occurred less than two years before or after a sale of the same property. Just for context in case y'all are wondering what I'm doing, I'm researching the phenomenon of "flipping homes" in Milwaukee, Wisconsin (my field of study is economics).

Thank you so much again!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-15 : 12:06:26

SQL just means Structured Query Language of which there are many. Microsoft's is called t-SQL, another is MySQL. They all do pretty much the same thing. If you access to Microsoft SQL Server, that will make it much easier for us to help you.
This query is done in t-SQL, but it is convertible to Access


DECLARE @Table Table(TaxKey int,SaleDate varchar(10))
INSERT INTO @Table

SELECT  5806,'2002-07' UNION ALL
SELECT 5944,'2004-08' UNION ALL 
SELECT 3706,'2003-12' UNION ALL 
SELECT 5239,'2007-09' UNION ALL 
SELECT 5806,'2005-08' UNION ALL 
SELECT 4595,'2010-05' UNION ALL 
SELECT 2136,'2009-09' UNION ALL 
SELECT 5806,'2006-05' UNION ALL 
SELECT 4202,'2008-12' UNION ALL 
SELECT 6479,'2003-12'


SELECT t1.taxKey,t1.OldSaleDate,t2.NewSaleDate
FROM

(
	select TaxKey,convert(datetime,SaleDate+'-01') as  OldSaleDate
	from @table  
) t1

CROSS JOIN 

(
	select TaxKey,convert(datetime,SaleDate+'-01') as NewSaleDate
	from @table  
) t2
 
 
WHERE
    t1.taxkey = t2.taxkey
and t1.OldSaleDate < t2.NewSaleDate
and datediff(month,t1.OldSaleDate ,t2.Newsaledate) < 25

The stuff inside "t1" and "t2" would be one query in Access. You'd then slap 2 copies of it on to your designer without joining them - which would make a CROSS JOIN. I had to convert the "dates" in your table to dates that sql recognizes, you may have to do that in Access as well.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources