Exporting data from multiple records table

Posted by Jeena under Sql Server on 7/21/2012 | Points: 10 | Views : 824 | Status : [Member] | Replies : 3
Hi ,

I have 3 tables

general_info table -- Master Table(property_id primary key)
Tax_table -- Child table(will contain Multiple records for a property for each year)(property_id foreign key)
Sale_Table --Child table(will contain Multiple records for a property for each year)(property_id foreign key)

SELECT * FROM tax_info where site_county='MyCounty' and site_state='MyState' ORDER BY property_id,tax_year DESC


This Query will return the records for property based on it tax_year and the first record for each property id will be the recent one,

SELECT * FROM sale_info where site_county='MyCounty' and site_state='MyState' ORDER BY property_id,sale_date DESC


This Query will return the records for property based on it sale_date and the first record for each property id will be the recent one,


I need to take only the recent tax entry for a property,and sale and need to show in an excel file as follows

PropertyId address tayear sale_date


I have achieved this in C# using data set,but when the no of records increases more than 500000 it will throw system.out of memory exception and
it will causes error.So i would like to do it db side.IS there any way i can achieve this




Responses

Posted by: Pandians on: 7/21/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Check it out!

Change the script as you need!
;With RecentTaxInfo

As
(
Select Max(T1.tax_year) tax_year, T1.property_id from tax_info T1
Group by T1.property_id
),RecentSalesInfo
As
(
Select Max(SI.sale_date) sale_date, SI.property_id from sale_info SI
Group by SI.property_id
)

SELECT TI.*, SSI.* FROM tax_info TI With(Nolock) Join RecentTaxInfo RI
On (TI.tax_year = RI.tax_year And TI.property_id = RI.property_id)
Left Join RecentSalesInfo SI With(Nolock) On (SI.property_id = T1.property_id)
Join sale_info SSI With(Nolock) On (SSI.property_id = SI.property_id)
where TI.site_county='MyCounty'
and TI.site_state='MyState'
You can copy the records to Excel using Export or Manual

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jeena, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jasminej on: 7/23/2012 [Member] Starter | Points: 25

Up
0
Down
Yes. I agree with this!

thank you

Jeena, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jeena on: 7/23/2012 [Member] Starter | Points: 25

Up
0
Down

Hi

I have tried the query as follows

select General.*,General.mail_street_name,Tax.tax_year,Sale.date_transfer
from

((select general.*,Mail.mail_street_name
from property_general_info general
left join property_mailing_info Mail on general.property_id=Mail.property_id
left join property_owner_info Owner on general.property_id=Owner.property_id
where general.site_state='STATE' and general.site_county='COUNTY')General

left join

(Select Max(tax_year) as tax_year,property_id from property_tax_info where site_state='state' and site_county='COUNTY'
Group by property_id) Tax

on General.property_id=Tax.property_id left join
(Select Max(date_transfer) as date_transfer,property_id from
property_sale_info where site_state='STATE' and site_county='COUNTY' Group by property_id) Sale
on General.property_id=Sale.property_id)


and it worked well also,but its taking too much time to download the data..
Is there any suggestions for optimizing this query??

Thanks

Jeena, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response