Need help to take a report

• incubator123

  Grasshopper

  Points: 21

  More actions

  February 8, 2011 at 9:48 pm

  #233372

  Hi

  I have a real estate database ,in which I have the following 2 tables

  1) Propertymaster

  ID PROPERTY_STATE PROPERTY_COUNTY

  770885 PA Philadelphia

  778460 AK Anchorage

  778461 CO Douglas

  778462 CO Douglas

  778463 CO Douglas

  778464 CA Lake

  778465 CO Douglas

  2) property loan

  ID FCL_LOAN_AMOUNT FCL_BALANCE

  770885 NULL 107024.02

  778460 1000 220000

  778461 NULL 181390

  778462 220000 153600

  778463 NULL 32400

  778464 NULL 1000

  I need to take a county wise count of the records having $200K plus loan amount for the entire US state

  The following are the conditions you need to check to get the records with $200K plus loan amount

  1) You have to consider 2 fields FCL_BALANCE and LOAN_AMOUNT in property loan

  2) If the LOAN_AMOUNT is greater than $200K there is no need to check the field FCL_BALANCE you can count the record .

  3) If the field LOAN_AMOUNT is Null then you need consider the field FCL_BALANCE and if it is greater than $200K you can count the record.

  Sample output

  State County Count

  AK Anchorage 10

  CO Douglas 1

  AK Matanuska Susitna 3

  CA Lake 33

  Thanks

  Jenn

• Mike01

  SSChampion

  Points: 11395

  More actions

  February 9, 2011 at 8:15 am

  #1284504

  The data you provided doesn't match your expected results. Try this:

  declare @Propertymaster table(ID int,

  PROPERTY_STATE char(2),

  PROPERTY_COUNTY varchar(15))

  insert into @Propertymaster

  select 770885,'PA','Philadelphia' union all

  select 778460,'AK','Anchorage' union all

  select 778461,'CO','Douglas' union all

  select 778462,'CO','Douglas' union all

  select 778463,'CO','Douglas' union all

  select 778464,'CA','Lake' union all

  select 778465,'CO','Douglas'

  declare @propertyloan table(

  ID int,

  FCL_LOAN_AMOUNT int,

  FCL_BALANCE decimal(8,2))

  insert into @propertyloan

  select 770885, NULL, 107024.02 union all

  select 778460, 1000, 220000 union all

  select 778461, NULL, 181390 union all

  select 778462, 220000, 153600 union all

  select 778463, NULL, 32400 union all

  select 778464, NULL, 1000 union all

  select 778464, 200002, 1000 union all

  select 778464, NULL, 200001

  select pm.PROPERTY_STATE, pm.PROPERTY_COUNTY, COUNT(pm.id)

  from @Propertymaster pm

  inner join @propertyloan pl

  on pm.ID = pl.ID

  where Coalesce(FCL_LOAN_AMOUNT, FCL_BALANCE) > 200000

  group by pm.PROPERTY_STATE, pm.PROPERTY_COUNTY

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• incubator123

  Grasshopper

  Points: 21

  More actions

  February 9, 2011 at 8:58 pm

  #1284931

  Hi Mike

  Thanks it worked for me

  Regards

  Jenn