Help with Datasets

[Luke Davis]

I've been programming in C# for about 3 months now and I've completed a
previous project that worked a lot with data, so I know the basics.

This is what I need to do, try and see what you would do as an expert C#
programmer. I have to make a paired sales analysis which takes the same
home sold twice in a given time span and show how the price has changed to
show true market conditions.

Right now we have 120,000 rows of data that have basic home information like
so:

Table Name = Properties
Columns:
APN / Address / Bedrooms / Bathrooms / Selling Price / Zip Code / SQFT /
Selling Date / MLS Number

APN is unique to the property, but the MLS Number is unique to the database.
So if a house sells twice in the last year, it'll have two MLS Numbers, but
only one APN. I need to take all homes (APN's) that have sold more than
once (they appear twice in the database) and do some math and put it into a
GridView perhaps that looks like this:

Columns:
APN / Address / Date1 / Price$1 / Date2 / Price$2 / PriceChange$ /
TimeChange / ChangeOverTime

I need to take the address from the first entry in the database, then take
the date and price from the first entry, and the date and price from the
second entry, then do Price1 - Price2 and put that into PriceChange$, then
do the same for Date1 - Date2 for TimeChange, and finally do
(PriceChange/TimeChange) / Price1 and put that into ChangeOverTime. Then
there needs to be a column average for PriceChange$, TimeChange, and
ChangeOverTime, and they need to be loaded into variables that I can put
outside the gridview.

Alright, now I hope you might know why I came to the board for help. I'm
not really sure how to pull from different rows of a database and place them
into the same row of a table, gridview, etc.
--
Luke Davis, MCSE: Security
DEM Networks - Senior Systems Architect
7225 N First, Suite 105
Fresno, CA 93720
Office: 1 [phone]
Fax: [phone]
www.demnetworks.com

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

You will have a hard time doing this in C#, when in SQL it's a pice of cake
:) , just a couple of grouping by clauses will solve your problem.

Can you put this data in a SQ:L DB?

[Luke Davis]

Yeah I can make a second db to load them into, but I'm not sure what SQL
commands I would use to grab information from multiple rows and put them
into one row. I know I need to make a stored procedure that can manage the
math but after that I'm not really sure what to do.

--
Luke Davis, MCSE: Security
DEM Networks - Senior Systems Architect
7225 N First, Suite 105
Fresno, CA 93720
Office: 1 [phone]
Fax: [phone]
www.demnetworks.com

"Ignacio Machin ( .NET/ C# MVP )" <machin TA laceupsolutions.comwrote in
message news:%2****************@TK2MSFTNGP02.phx.gbl...

Hi,

You will have a hard time doing this in C#, when in SQL it's a pice of
cake :) , just a couple of grouping by clauses will solve your problem.

Can you put this data in a SQ:L DB?

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

You use "group by" to create one row per one particular value.

Now, a question, what happen if a given house was sold more than two times?
"Luke Davis" <lu**@gorealco.comwrote in message
news:eh**************@TK2MSFTNGP05.phx.gbl...

Yeah I can make a second db to load them into, but I'm not sure what SQL
commands I would use to grab information from multiple rows and put them
into one row. I know I need to make a stored procedure that can manage
the math but after that I'm not really sure what to do.

--
Luke Davis, MCSE: Security
DEM Networks - Senior Systems Architect
7225 N First, Suite 105
Fresno, CA 93720
Office: 1 [phone]
Fax: [phone]
www.demnetworks.com

"Ignacio Machin ( .NET/ C# MVP )" <machin TA laceupsolutions.comwrote in
message news:%2****************@TK2MSFTNGP02.phx.gbl...

>Hi,

You will have a hard time doing this in C#, when in SQL it's a pice of
cake :) , just a couple of grouping by clauses will solve your problem.

Can you put this data in a SQ:L DB?

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

Luke,
What you describe really sounds like a classic database normailization
issue. If you have a listing that could have 2 or more MLS numbers, this
needs to be normalized out into an MLSNUMBERS table that has a foreign key
into the Listings table. And possibly, another associative or "junction"
table, depending on your business logic. So when you get a DataSet back, it
can have several tables in it with keys among the tables. You can make a
DataSet behave like an "in memory" database by creating DataRelations.
-- Peter
Recursion: see Recursion
site: http://www.eggheadcafe.com
unBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder: http://www.blogmetafinder.com

"Luke Davis" wrote:

I've been programming in C# for about 3 months now and I've completed a
previous project that worked a lot with data, so I know the basics.

This is what I need to do, try and see what you would do as an expert C#
programmer. I have to make a paired sales analysis which takes the same
home sold twice in a given time span and show how the price has changed to
show true market conditions.

Right now we have 120,000 rows of data that have basic home information like
so:

Table Name = Properties
Columns:
APN / Address / Bedrooms / Bathrooms / Selling Price / Zip Code / SQFT /
Selling Date / MLS Number

APN is unique to the property, but the MLS Number is unique to the database.
So if a house sells twice in the last year, it'll have two MLS Numbers, but
only one APN. I need to take all homes (APN's) that have sold more than
once (they appear twice in the database) and do some math and put it into a
GridView perhaps that looks like this:

Columns:
APN / Address / Date1 / Price$1 / Date2 / Price$2 / PriceChange$ /
TimeChange / ChangeOverTime

I need to take the address from the first entry in the database, then take
the date and price from the first entry, and the date and price from the
second entry, then do Price1 - Price2 and put that into PriceChange$, then
do the same for Date1 - Date2 for TimeChange, and finally do
(PriceChange/TimeChange) / Price1 and put that into ChangeOverTime. Then
there needs to be a column average for PriceChange$, TimeChange, and
ChangeOverTime, and they need to be loaded into variables that I can put
outside the gridview.

Alright, now I hope you might know why I came to the board for help. I'm
not really sure how to pull from different rows of a database and place them
into the same row of a table, gridview, etc.
--
Luke Davis, MCSE: Security
DEM Networks - Senior Systems Architect
7225 N First, Suite 105
Fresno, CA 93720
Office: 1 [phone]
Fax: [phone]
www.demnetworks.com