Exporting data from multiple records table

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

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

Latest Posts