nested sub query combination

I have a large query that pulls data, see file 1.  I have a small query that sums data from one of the same tables shown in file 2.  I am trying to combine the two together to that I get the 'Balance' in the large query.  I have tried doing this as shown in file 3.  However get a syntax error sayng 'Msg 156, Level 15, State 1, Line 118
Incorrect syntax near the keyword 'AS'.

Can someone let me know what I'm doing wrong.  Many thanks.
file1.txt
file2.txt
file3.txt
deborahhowson00Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
According to your files, you don't have 118 lines.

Can you therefore please submit the entirety of your code for that window.

I assume that you have line numbers on. If not, go to Tools - Options - Text Editor - Plain Text - Display - Line numbers.

This will help you identify line 118.
0
ste5anSenior DeveloperCommented:
Yup. Obvious, but it would be much better readable, when you would short table alias names for all tables in your query. It's right here

 
 LEFT OUTER JOIN dbo.ih_officer AS ih_officer_1 ON dbo.ih_location.estate_ofcr = ih_officer_1.officer_code 
LEFT OUTER JOIN dbo.ih_officer ON dbo.ih_location.arrears_ofcr = dbo.ih_officer.officer_code AS D2

Open in new window


which should be

 LEFT OUTER JOIN dbo.ih_officer AS ih_officer_1 ON dbo.ih_location.estate_ofcr = ih_officer_1.officer_code 
LEFT OUTER JOIN dbo.ih_officer  AS D2 ON dbo.ih_location.arrears_ofcr = dbo.ih_officer.officer_code

Open in new window

0
deborahhowson00Author Commented:
Not sure what happened but it's saynig:

Msg 156, Level 15, State 1, Line 83
Incorrect syntax near the keyword 'AS'


SELECT     TOP (100) PERCENT dbo.re_tenancy.tenancy_ref, dbo.re_tenancy.tncy_cat, dbo.re_tenancy.rent_group, dbo.re_tenancy.pmt_method, dbo.re_tncy_trans.account_code, 
                      dbo.re_tncy_trans.account_type, dbo.re_tenancy.arrears_stage, dbo.re_tenancy.tncy_status, dbo.re_tncy_trans.tncy_sys_ref, dbo.re_tenancy.curr_balance, 
                      dbo.re_tncy_trans.trans_amt, dbo.re_tncy_trans.created_date, dbo.re_tncy_trans.trans_date, dbo.re_tenancy.tncy_start, dbo.re_tenancy.tncy_end, 
                      CASE WHEN dbo.re_tncy_trans.trans_date >= dbo.re_tenancy.tncy_start AND 
                      dbo.re_tncy_trans.trans_date <= dbo.re_tenancy.tncy_end THEN 'CUR' ELSE 'FOR' END AS Tncy_Status_AtTime, dbo.re_tncy_place.place_ref, 
                      dbo.ih_location.location_sts, dbo.ih_location.sub_area_code, dbo.ih_location.mgt_area, dbo.ih_location.rtb, dbo.ih_location.arrears_ofcr, dbo.ih_location.estate_ofcr, 
                      dbo.ih_location.appointment_area_code, dbo.ih_location.former_arrs_ofcr, dbo.re_tncy_person.person_ref, dbo.re_tncy_person.start_date, 
                      dbo.re_tncy_person.end_date, dbo.re_tncy_person.on_tncy, dbo.re_tncy_person.tenant_order, dbo.re_tncy_person.fair_rent, dbo.co_person.person_title, 
                      dbo.co_person.forenames, dbo.co_person.surname, dbo.co_person.d_o_b, dbo.co_person.deceased, dbo.co_person.date_of_death, dbo.co_person.origin_code, 
                      dbo.co_person.gender, dbo.co_person.potentially_violent, dbo.co_person.potentially_vulnerable, dbo.re_tenancy.tncy_type, 
                      CASE WHEN dbo.ih_location.appointment_area_code = 'SE' OR
                      dbo.ih_location.appointment_area_code = 'WRC' THEN 'CENT' WHEN dbo.ih_location.appointment_area_code = 'PK' OR
                      dbo.ih_location.appointment_area_code = 'CA' OR
                      dbo.ih_location.appointment_area_code = 'NF' THEN 'EAST' WHEN dbo.ih_location.appointment_area_code = 'HECK' OR
                      dbo.ih_location.appointment_area_code = 'WK' OR
                      dbo.ih_location.appointment_area_code = 'WR' OR
                      dbo.ih_location.appointment_area_code = 'WN' THEN 'WEST' ELSE 'NULL' END AS Rev_Areas, 
                      dbo.ih_location.arrears_ofcr + ' - ' + dbo.ih_officer.ofcr_title + ' ' + dbo.ih_officer.forename + ' ' + 'dbo.ih_officer.surname' AS Arrears_Officer, 
                      dbo.ih_location.estate_ofcr + ' - ' + ih_officer_1.ofcr_title + ' ' + ih_officer_1.forename + ' ' + ih_officer_1.surname AS Estate_Officer, 
                      dbo.ih_location.former_arrs_ofcr + ' - ' + ih_officer_2.ofcr_title + ' ' + ih_officer_2.forename + ' ' + ih_officer_2.surname AS Former_Arr_Officer, dbo.co_place.street, 
                      dbo.co_place.address##1, dbo.co_place.address##2, dbo.co_place.address##3, dbo.co_place.address##4, dbo.co_place.address##5, dbo.co_place.street_name, 
                      dbo.co_place.post_code, dbo.ha_sub_area.description AS Sub_Area_Desc, CASE WHEN dbo.re_tenancy.tncy_status = 'CUR' OR
                      dbo.re_tenancy.tncy_status = 'TER' THEN 'CUR' ELSE 'FOR' END AS Report_Tncy_Status, CASE WHEN dbo.re_tenancy.tncy_status = 'FOR' AND 
                      dbo.re_tenancy.tncy_start < '03/20/2005' THEN 'WMDC Accounts' ELSE 'WDH Accounts' END AS Org_Acc_Type, dbo.co_place.place_type, 
                      dbo.co_place_type.description AS Place_Type_Desc, dbo.re_tncy_type.description AS Tncy_Type_Desc, 
                      CASE WHEN dbo.re_tenancy.tncy_type <> '' THEN 'All Tenancies' END AS All_Account_Types, 
                      CASE WHEN dbo.co_place.place_type = 'GENERAL' THEN 'General Needs' ELSE 'Supported/Housing for Older People' END AS Account_Place_Type, 
                      CASE WHEN dbo.re_tenancy.curr_balance < 0 OR
                      dbo.re_tenancy.curr_balance = 0 THEN 'Not In Arrears' ELSE 'In Arrears' END AS Arrears_Status, CASE WHEN dbo.re_tenancy.tncy_type LIKE 'AR%' OR
                      dbo.re_tenancy.tncy_type LIKE 'ASS%' OR
                      dbo.re_tenancy.tncy_type LIKE 'APT%' OR
                      dbo.re_tenancy.tncy_type LIKE 'AST%' OR
                      dbo.re_tenancy.tncy_type LIKE 'DEC%' OR
                      dbo.re_tenancy.tncy_type LIKE 'ASSI%' OR
                      dbo.re_tenancy.tncy_type LIKE 'SEC%' OR
                      dbo.re_tenancy.tncy_type LIKE 'SUR%' THEN 'Residential Accounts' WHEN dbo.co_place_details.scheme = 'HOMESPACE' THEN 'Homespace Properties' WHEN dbo.re_tenancy.tncy_type
                       = 'EXT' THEN 'External Organisations' WHEN dbo.re_tenancy.tncy_type = 'SERV' THEN 'Service Tenancies' WHEN dbo.ih_location.location_type = 'GARAGE' THEN 'Garage Accounts'
                       WHEN dbo.ih_location.location_type = 'SHOP' THEN 'Shop Accounts' WHEN dbo.re_tenancy.tncy_type = 'LEA' THEN 'Leasehold Accounts' WHEN dbo.re_tenancy.tncy_type
                       = 'SO' THEN 'Shared Owership' ELSE 'OTHER' END AS Acc_Type, CASE WHEN dbo.re_tenancy.curr_balance <= 100 AND 
                      dbo.re_tenancy.curr_balance > 0 THEN 'Up to £100' WHEN dbo.re_tenancy.curr_balance > 100.01 AND 
                      dbo.re_tenancy.curr_balance <= 250 THEN '£100.01 to £250' WHEN dbo.re_tenancy.curr_balance > 250.01 AND 
                      dbo.re_tenancy.curr_balance <= 500 THEN '£250.01 to £500' WHEN dbo.re_tenancy.curr_balance > 500.01 AND 
                      dbo.re_tenancy.curr_balance <= 750 THEN '£500.01 to £750' WHEN dbo.re_tenancy.curr_balance > 750.01 AND 
                      dbo.re_tenancy.curr_balance <= 1000 THEN '£750.01 to £1,000' WHEN dbo.re_tenancy.curr_balance > 1000.01 AND 
                      dbo.re_tenancy.curr_balance <= 1250 THEN '£1,000.01 to £1,250' WHEN dbo.re_tenancy.curr_balance > 1250.01 AND 
                      dbo.re_tenancy.curr_balance <= 1500 THEN '£1,250.01 to £1,500' WHEN dbo.re_tenancy.curr_balance > 1500 THEN 'Over £1500' WHEN dbo.re_tenancy.curr_balance =
                       0 THEN 'Zero Balance' WHEN dbo.re_tenancy.curr_balance < 0 THEN 'In Credit' ELSE NULL END AS Amount_Band, FLOOR((CAST(GETDATE() AS INTEGER) 
                      - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) AS Age, CASE WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) 
                      < 18 AND FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 16 AND 
                      dbo.co_person.deceased = 0 THEN 'Under 18' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 18 AND 
                      FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) < 24.9 AND 
                      dbo.co_person.deceased = 0 THEN '18-24 Yrs' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 25 AND 
                      FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) < 39.9 AND 
                      dbo.co_person.deceased = 0 THEN '25-39 Yrs' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 40 AND 
                      FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) < 49.9 AND 
                      dbo.co_person.deceased = 0 THEN '40-49 Yrs' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 50 AND 
                      FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) < 59.9 AND 
                      dbo.co_person.deceased = 0 THEN '50-59 Yrs' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 60 AND 
                      FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) < 64.9 AND 
                      dbo.co_person.deceased = 0 THEN '60-64 Yrs' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 65 AND 
                      FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) < 79.9 AND 
                      dbo.co_person.deceased = 0 THEN '65-79 Yrs' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 80 AND 
                      dbo.co_person.deceased = 0 THEN 'Over 80 Yrs' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) < 16 AND 
                      dbo.co_person.deceased = 0 THEN 'Minor' WHEN dbo.co_person.deceased = 1 THEN 'Deceased' ELSE NULL END AS Age_Range,
(SELECT SUM(trans_amt)
	FROM dbo.re_tncy_trans AS D1
	WHERE D1.tncy_sys_ref = D2.tncy_sys_ref AND D1.trans_date <= D2.trans_date) as Balance


FROM         dbo.co_place_details RIGHT OUTER JOIN
                      dbo.re_tncy_trans INNER JOIN
                      dbo.re_tenancy ON dbo.re_tncy_trans.tncy_sys_ref = dbo.re_tenancy.tncy_sys_ref INNER JOIN
                      dbo.re_tncy_place ON dbo.re_tenancy.tncy_sys_ref = dbo.re_tncy_place.tncy_sys_ref INNER JOIN
                      dbo.ih_location ON dbo.re_tncy_place.place_ref = dbo.ih_location.place_ref INNER JOIN
                      dbo.re_tncy_person ON dbo.re_tenancy.tncy_sys_ref = dbo.re_tncy_person.tncy_sys_ref INNER JOIN
                      dbo.re_tncy_type ON dbo.re_tenancy.tncy_type = dbo.re_tncy_type.tncy_type ON dbo.co_place_details.place_ref = dbo.ih_location.place_ref LEFT OUTER JOIN
                      dbo.co_person ON dbo.re_tncy_person.person_ref = dbo.co_person.person_ref LEFT OUTER JOIN
                      dbo.co_place INNER JOIN
                      dbo.co_place_type ON dbo.co_place.place_type = dbo.co_place_type.place_type ON dbo.ih_location.place_ref = dbo.co_place.place_ref LEFT OUTER JOIN
                      dbo.ha_sub_area ON dbo.ih_location.sub_area_code = dbo.ha_sub_area.sub_area_code LEFT OUTER JOIN
                      dbo.ih_officer AS ih_officer_2 ON dbo.ih_location.former_arrs_ofcr = ih_officer_2.officer_code LEFT OUTER JOIN
                      dbo.ih_officer AS ih_officer_1 ON dbo.ih_location.estate_ofcr = ih_officer_1.officer_code LEFT OUTER JOIN
                      dbo.ih_officer ON dbo.ih_location.arrears_ofcr = dbo.ih_officer.officer_code AS D2
WHERE     (dbo.re_tncy_trans.tncy_sys_ref = 301)
ORDER BY dbo.re_tenancy.tenancy_ref, dbo.re_tncy_trans.created_date DESC, dbo.re_tncy_trans.trans_date

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

deborahhowson00Author Commented:
Ste5an your solution still gives the same error I'm afraid!  Thanks.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
dbo.ih_officer ON dbo.ih_location.arrears_ofcr = dbo.ih_officer.officer_code AS D2

should read

 dbo.ih_officer  AS D2 ON dbo.ih_location.arrears_ofcr = dbo.ih_officer.officer_code
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
dbo.ih_officer ON dbo.ih_location.arrears_ofcr = dbo.ih_officer.officer_code AS D2

should read

 dbo.ih_officer  AS D2 ON dbo.ih_location.arrears_ofcr = D2.officer_code
0
deborahhowson00Author Commented:
Phil I then get this

Msg 4104, Level 16, State 1, Line 83
The multi-part identifier "dbo.ih_officer.officer_code" could not be bound.
Msg 207, Level 16, State 1, Line 84
Invalid column name 're_tncy_trans'.
Msg 4104, Level 16, State 1, Line 18
The multi-part identifier "dbo.ih_officer.ofcr_title" could not be bound.
Msg 4104, Level 16, State 1, Line 18
The multi-part identifier "dbo.ih_officer.forename" could not be bound.
Msg 207, Level 16, State 1, Line 67
Invalid column name 'tncy_sys_ref'.
Msg 207, Level 16, State 1, Line 67
Invalid column name 'trans_date'.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Sounds like you have multiple problems in your SQL.

For example, the fifth and sixth errors relate to this line:

WHERE D1.tncy_sys_ref = D2.tncy_sys_ref AND D1.trans_date <= D2.trans_date

D1 refers to dbo.re_tncy_trans, which presumably has tncy_sys_ref and trans_date.
D2 refers to dbo.ih_officer, which presumably doesn't have this problem.

The reason you are getting these new problems is that your original problem is fixed (at least, as far as anyone can make out who is not familiar with the code).

I would suggest you look at your code and table structure, and decide what this "D2" table is which is meant to have tncy_sys_ref and trans_date.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
deborahhowson00Author Commented:
Have managed to figure it out, correct code as follows:--

SELECT     dbo.re_tenancy.tenancy_ref, dbo.re_tenancy.tncy_cat, dbo.re_tenancy.rent_group, dbo.re_tenancy.pmt_method, D2.account_code, 
                      D2.account_type, dbo.re_tenancy.arrears_stage, dbo.re_tenancy.tncy_status, D2.tncy_sys_ref, dbo.re_tenancy.curr_balance, 
                      D2.trans_amt, D2.created_date, D2.trans_date, dbo.re_tenancy.tncy_start, dbo.re_tenancy.tncy_end, 
                      CASE WHEN D2.trans_date >= dbo.re_tenancy.tncy_start AND 
                      D2.trans_date <= dbo.re_tenancy.tncy_end THEN 'CUR' ELSE 'FOR' END AS Tncy_Status_AtTime, dbo.re_tncy_place.place_ref, 
                      dbo.ih_location.location_sts, dbo.ih_location.sub_area_code, dbo.ih_location.mgt_area, dbo.ih_location.rtb, dbo.ih_location.arrears_ofcr, dbo.ih_location.estate_ofcr, 
                      dbo.ih_location.appointment_area_code, dbo.ih_location.former_arrs_ofcr, dbo.re_tncy_person.person_ref, dbo.re_tncy_person.start_date, 
                      dbo.re_tncy_person.end_date, dbo.re_tncy_person.on_tncy, dbo.re_tncy_person.tenant_order, dbo.re_tncy_person.fair_rent, dbo.co_person.person_title, 
                      dbo.co_person.forenames, dbo.co_person.surname, dbo.co_person.d_o_b, dbo.co_person.deceased, dbo.co_person.date_of_death, dbo.co_person.origin_code, 
                      dbo.co_person.gender, dbo.co_person.potentially_violent, dbo.co_person.potentially_vulnerable, dbo.re_tenancy.tncy_type, 
                      CASE WHEN dbo.ih_location.appointment_area_code = 'SE' OR
                      dbo.ih_location.appointment_area_code = 'WRC' THEN 'CENT' WHEN dbo.ih_location.appointment_area_code = 'PK' OR
                      dbo.ih_location.appointment_area_code = 'CA' OR
                      dbo.ih_location.appointment_area_code = 'NF' THEN 'EAST' WHEN dbo.ih_location.appointment_area_code = 'HECK' OR
                      dbo.ih_location.appointment_area_code = 'WK' OR
                      dbo.ih_location.appointment_area_code = 'WR' OR
                      dbo.ih_location.appointment_area_code = 'WN' THEN 'WEST' ELSE 'NULL' END AS Rev_Areas, 
                      dbo.ih_location.arrears_ofcr + ' - ' + dbo.ih_officer.ofcr_title + ' ' + dbo.ih_officer.forename + ' ' + 'dbo.ih_officer.surname' AS Arrears_Officer, 
                      dbo.ih_location.estate_ofcr + ' - ' + ih_officer_1.ofcr_title + ' ' + ih_officer_1.forename + ' ' + ih_officer_1.surname AS Estate_Officer, 
                      dbo.ih_location.former_arrs_ofcr + ' - ' + ih_officer_2.ofcr_title + ' ' + ih_officer_2.forename + ' ' + ih_officer_2.surname AS Former_Arr_Officer, dbo.co_place.street, 
                      dbo.co_place.address##1, dbo.co_place.address##2, dbo.co_place.address##3, dbo.co_place.address##4, dbo.co_place.address##5, dbo.co_place.street_name, 
                      dbo.co_place.post_code, dbo.ha_sub_area.description AS Sub_Area_Desc, CASE WHEN dbo.re_tenancy.tncy_status = 'CUR' OR
                      dbo.re_tenancy.tncy_status = 'TER' THEN 'CUR' ELSE 'FOR' END AS Report_Tncy_Status, CASE WHEN dbo.re_tenancy.tncy_status = 'FOR' AND 
                      dbo.re_tenancy.tncy_start < '03/20/2005' THEN 'WMDC Accounts' ELSE 'WDH Accounts' END AS Org_Acc_Type, dbo.co_place.place_type, 
                      dbo.co_place_type.description AS Place_Type_Desc, dbo.re_tncy_type.description AS Tncy_Type_Desc, 
                      CASE WHEN dbo.re_tenancy.tncy_type <> '' THEN 'All Tenancies' END AS All_Account_Types, 
                      CASE WHEN dbo.co_place.place_type = 'GENERAL' THEN 'General Needs' ELSE 'Supported/Housing for Older People' END AS Account_Place_Type, 
                      CASE WHEN dbo.re_tenancy.curr_balance < 0 OR
                      dbo.re_tenancy.curr_balance = 0 THEN 'Not In Arrears' ELSE 'In Arrears' END AS Arrears_Status, CASE WHEN dbo.re_tenancy.tncy_type LIKE 'AR%' OR
                      dbo.re_tenancy.tncy_type LIKE 'ASS%' OR
                      dbo.re_tenancy.tncy_type LIKE 'APT%' OR
                      dbo.re_tenancy.tncy_type LIKE 'AST%' OR
                      dbo.re_tenancy.tncy_type LIKE 'DEC%' OR
                      dbo.re_tenancy.tncy_type LIKE 'ASSI%' OR
                      dbo.re_tenancy.tncy_type LIKE 'SEC%' OR
                      dbo.re_tenancy.tncy_type LIKE 'SUR%' THEN 'Residential Accounts' WHEN dbo.co_place_details.scheme = 'HOMESPACE' THEN 'Homespace Properties' WHEN dbo.re_tenancy.tncy_type
                       = 'EXT' THEN 'External Organisations' WHEN dbo.re_tenancy.tncy_type = 'SERV' THEN 'Service Tenancies' WHEN dbo.ih_location.location_type = 'GARAGE' THEN 'Garage Accounts'
                       WHEN dbo.ih_location.location_type = 'SHOP' THEN 'Shop Accounts' WHEN dbo.re_tenancy.tncy_type = 'LEA' THEN 'Leasehold Accounts' WHEN dbo.re_tenancy.tncy_type
                       = 'SO' THEN 'Shared Owership' ELSE 'OTHER' END AS Acc_Type, CASE WHEN dbo.re_tenancy.curr_balance <= 100 AND 
                      dbo.re_tenancy.curr_balance > 0 THEN 'Up to £100' WHEN dbo.re_tenancy.curr_balance > 100.01 AND 
                      dbo.re_tenancy.curr_balance <= 250 THEN '£100.01 to £250' WHEN dbo.re_tenancy.curr_balance > 250.01 AND 
                      dbo.re_tenancy.curr_balance <= 500 THEN '£250.01 to £500' WHEN dbo.re_tenancy.curr_balance > 500.01 AND 
                      dbo.re_tenancy.curr_balance <= 750 THEN '£500.01 to £750' WHEN dbo.re_tenancy.curr_balance > 750.01 AND 
                      dbo.re_tenancy.curr_balance <= 1000 THEN '£750.01 to £1,000' WHEN dbo.re_tenancy.curr_balance > 1000.01 AND 
                      dbo.re_tenancy.curr_balance <= 1250 THEN '£1,000.01 to £1,250' WHEN dbo.re_tenancy.curr_balance > 1250.01 AND 
                      dbo.re_tenancy.curr_balance <= 1500 THEN '£1,250.01 to £1,500' WHEN dbo.re_tenancy.curr_balance > 1500 THEN 'Over £1500' WHEN dbo.re_tenancy.curr_balance =
                       0 THEN 'Zero Balance' WHEN dbo.re_tenancy.curr_balance < 0 THEN 'In Credit' ELSE NULL END AS Amount_Band, FLOOR((CAST(GETDATE() AS INTEGER) 
                      - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) AS Age, CASE WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) 
                      < 18 AND FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 16 AND 
                      dbo.co_person.deceased = 0 THEN 'Under 18' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 18 AND 
                      FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) < 24.9 AND 
                      dbo.co_person.deceased = 0 THEN '18-24 Yrs' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 25 AND 
                      FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) < 39.9 AND 
                      dbo.co_person.deceased = 0 THEN '25-39 Yrs' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 40 AND 
                      FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) < 49.9 AND 
                      dbo.co_person.deceased = 0 THEN '40-49 Yrs' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 50 AND 
                      FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) < 59.9 AND 
                      dbo.co_person.deceased = 0 THEN '50-59 Yrs' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 60 AND 
                      FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) < 64.9 AND 
                      dbo.co_person.deceased = 0 THEN '60-64 Yrs' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 65 AND 
                      FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) < 79.9 AND 
                      dbo.co_person.deceased = 0 THEN '65-79 Yrs' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) >= 80 AND 
                      dbo.co_person.deceased = 0 THEN 'Over 80 Yrs' WHEN FLOOR((CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER)) / 365.25) < 16 AND 
                      dbo.co_person.deceased = 0 THEN 'Minor' WHEN dbo.co_person.deceased = 1 THEN 'Deceased' ELSE NULL END AS Age_Range,
(SELECT SUM(trans_amt)
	FROM dbo.re_tncy_trans AS D1
	WHERE D1.tncy_sys_ref = D2.tncy_sys_ref AND D1.trans_date <= D2.trans_date) as Balance


FROM         dbo.co_place_details RIGHT OUTER JOIN
                      dbo.re_tncy_trans  AS D2 INNER JOIN
                      dbo.re_tenancy ON D2.tncy_sys_ref = dbo.re_tenancy.tncy_sys_ref INNER JOIN
                      dbo.re_tncy_place ON dbo.re_tenancy.tncy_sys_ref = dbo.re_tncy_place.tncy_sys_ref INNER JOIN
                      dbo.ih_location ON dbo.re_tncy_place.place_ref = dbo.ih_location.place_ref INNER JOIN
                      dbo.re_tncy_person ON dbo.re_tenancy.tncy_sys_ref = dbo.re_tncy_person.tncy_sys_ref INNER JOIN
                      dbo.re_tncy_type ON dbo.re_tenancy.tncy_type = dbo.re_tncy_type.tncy_type ON dbo.co_place_details.place_ref = dbo.ih_location.place_ref LEFT OUTER JOIN
                      dbo.co_person ON dbo.re_tncy_person.person_ref = dbo.co_person.person_ref LEFT OUTER JOIN
                      dbo.co_place INNER JOIN
                      dbo.co_place_type ON dbo.co_place.place_type = dbo.co_place_type.place_type ON dbo.ih_location.place_ref = dbo.co_place.place_ref LEFT OUTER JOIN
                      dbo.ha_sub_area  ON dbo.ih_location.sub_area_code = dbo.ha_sub_area.sub_area_code LEFT OUTER JOIN
                      dbo.ih_officer AS ih_officer_2  ON dbo.ih_location.former_arrs_ofcr = ih_officer_2.officer_code LEFT OUTER JOIN
                      dbo.ih_officer AS ih_officer_1 ON dbo.ih_location.estate_ofcr = ih_officer_1.officer_code LEFT OUTER JOIN
                      dbo.ih_officer ON dbo.ih_location.arrears_ofcr = dbo.ih_officer.officer_code
WHERE     (D2.tncy_sys_ref = 301)
ORDER BY dbo.re_tenancy.tenancy_ref, D2.created_date DESC, D2.trans_date

Open in new window

0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Good - glad I could help.
0
ste5anSenior DeveloperCommented:
It's quite simple. But "Doesn't work" is not helpful. Please post the script which fails..

e.g. when you really want to join against IH_OFFICER:

SELECT TOP ( 100 ) PERCENT
        dbo.re_tenancy.tenancy_ref ,
        dbo.re_tenancy.tncy_cat ,
        dbo.re_tenancy.rent_group ,
        dbo.re_tenancy.pmt_method ,
        dbo.re_tncy_trans.account_code ,
        dbo.re_tncy_trans.account_type ,
        dbo.re_tenancy.arrears_stage ,
        dbo.re_tenancy.tncy_status ,
        dbo.re_tncy_trans.tncy_sys_ref ,
        dbo.re_tenancy.curr_balance ,
        dbo.re_tncy_trans.trans_amt ,
        dbo.re_tncy_trans.created_date ,
        dbo.re_tncy_trans.trans_date ,
        dbo.re_tenancy.tncy_start ,
        dbo.re_tenancy.tncy_end ,
        CASE WHEN dbo.re_tncy_trans.trans_date >= dbo.re_tenancy.tncy_start
                  AND dbo.re_tncy_trans.trans_date <= dbo.re_tenancy.tncy_end THEN 'CUR'
             ELSE 'FOR'
        END AS Tncy_Status_AtTime ,
        dbo.re_tncy_place.place_ref ,
        dbo.ih_location.location_sts ,
        dbo.ih_location.sub_area_code ,
        dbo.ih_location.mgt_area ,
        dbo.ih_location.rtb ,
        dbo.ih_location.arrears_ofcr ,
        dbo.ih_location.estate_ofcr ,
        dbo.ih_location.appointment_area_code ,
        dbo.ih_location.former_arrs_ofcr ,
        dbo.re_tncy_person.person_ref ,
        dbo.re_tncy_person.start_date ,
        dbo.re_tncy_person.end_date ,
        dbo.re_tncy_person.on_tncy ,
        dbo.re_tncy_person.tenant_order ,
        dbo.re_tncy_person.fair_rent ,
        dbo.co_person.person_title ,
        dbo.co_person.forenames ,
        dbo.co_person.surname ,
        dbo.co_person.d_o_b ,
        dbo.co_person.deceased ,
        dbo.co_person.date_of_death ,
        dbo.co_person.origin_code ,
        dbo.co_person.gender ,
        dbo.co_person.potentially_violent ,
        dbo.co_person.potentially_vulnerable ,
        dbo.re_tenancy.tncy_type ,
        CASE WHEN dbo.ih_location.appointment_area_code = 'SE'
                  OR dbo.ih_location.appointment_area_code = 'WRC' THEN 'CENT'
             WHEN dbo.ih_location.appointment_area_code = 'PK'
                  OR dbo.ih_location.appointment_area_code = 'CA'
                  OR dbo.ih_location.appointment_area_code = 'NF' THEN 'EAST'
             WHEN dbo.ih_location.appointment_area_code = 'HECK'
                  OR dbo.ih_location.appointment_area_code = 'WK'
                  OR dbo.ih_location.appointment_area_code = 'WR'
                  OR dbo.ih_location.appointment_area_code = 'WN' THEN 'WEST'
             ELSE 'NULL'
        END AS Rev_Areas ,
        dbo.ih_location.arrears_ofcr + ' - ' + dbo.ih_officer.ofcr_title + ' ' + dbo.ih_officer.forename + ' ' + 'dbo.ih_officer.surname' AS Arrears_Officer ,
        dbo.ih_location.estate_ofcr + ' - ' + ih_officer_1.ofcr_title + ' ' + ih_officer_1.forename + ' ' + ih_officer_1.surname AS Estate_Officer ,
        dbo.ih_location.former_arrs_ofcr + ' - ' + ih_officer_2.ofcr_title + ' ' + ih_officer_2.forename + ' ' + ih_officer_2.surname AS Former_Arr_Officer ,
        dbo.co_place.street ,
        dbo.co_place.address##1 ,
        dbo.co_place.address##2 ,
        dbo.co_place.address##3 ,
        dbo.co_place.address##4 ,
        dbo.co_place.address##5 ,
        dbo.co_place.street_name ,
        dbo.co_place.post_code ,
        dbo.ha_sub_area.description AS Sub_Area_Desc ,
        CASE WHEN dbo.re_tenancy.tncy_status = 'CUR'
                  OR dbo.re_tenancy.tncy_status = 'TER' THEN 'CUR'
             ELSE 'FOR'
        END AS Report_Tncy_Status ,
        CASE WHEN dbo.re_tenancy.tncy_status = 'FOR'
                  AND dbo.re_tenancy.tncy_start < '03/20/2005' THEN 'WMDC Accounts'
             ELSE 'WDH Accounts'
        END AS Org_Acc_Type ,
        dbo.co_place.place_type ,
        dbo.co_place_type.description AS Place_Type_Desc ,
        dbo.re_tncy_type.description AS Tncy_Type_Desc ,
        CASE WHEN dbo.re_tenancy.tncy_type <> '' THEN 'All Tenancies'
        END AS All_Account_Types ,
        CASE WHEN dbo.co_place.place_type = 'GENERAL' THEN 'General Needs'
             ELSE 'Supported/Housing for Older People'
        END AS Account_Place_Type ,
        CASE WHEN dbo.re_tenancy.curr_balance < 0
                  OR dbo.re_tenancy.curr_balance = 0 THEN 'Not In Arrears'
             ELSE 'In Arrears'
        END AS Arrears_Status ,
        CASE WHEN dbo.re_tenancy.tncy_type LIKE 'AR%'
                  OR dbo.re_tenancy.tncy_type LIKE 'ASS%'
                  OR dbo.re_tenancy.tncy_type LIKE 'APT%'
                  OR dbo.re_tenancy.tncy_type LIKE 'AST%'
                  OR dbo.re_tenancy.tncy_type LIKE 'DEC%'
                  OR dbo.re_tenancy.tncy_type LIKE 'ASSI%'
                  OR dbo.re_tenancy.tncy_type LIKE 'SEC%'
                  OR dbo.re_tenancy.tncy_type LIKE 'SUR%' THEN 'Residential Accounts'
             WHEN dbo.co_place_details.scheme = 'HOMESPACE' THEN 'Homespace Properties'
             WHEN dbo.re_tenancy.tncy_type = 'EXT' THEN 'External Organisations'
             WHEN dbo.re_tenancy.tncy_type = 'SERV' THEN 'Service Tenancies'
             WHEN dbo.ih_location.location_type = 'GARAGE' THEN 'Garage Accounts'
             WHEN dbo.ih_location.location_type = 'SHOP' THEN 'Shop Accounts'
             WHEN dbo.re_tenancy.tncy_type = 'LEA' THEN 'Leasehold Accounts'
             WHEN dbo.re_tenancy.tncy_type = 'SO' THEN 'Shared Owership'
             ELSE 'OTHER'
        END AS Acc_Type ,
        CASE WHEN dbo.re_tenancy.curr_balance <= 100
                  AND dbo.re_tenancy.curr_balance > 0 THEN 'Up to £100'
             WHEN dbo.re_tenancy.curr_balance > 100.01
                  AND dbo.re_tenancy.curr_balance <= 250 THEN '£100.01 to £250'
             WHEN dbo.re_tenancy.curr_balance > 250.01
                  AND dbo.re_tenancy.curr_balance <= 500 THEN '£250.01 to £500'
             WHEN dbo.re_tenancy.curr_balance > 500.01
                  AND dbo.re_tenancy.curr_balance <= 750 THEN '£500.01 to £750'
             WHEN dbo.re_tenancy.curr_balance > 750.01
                  AND dbo.re_tenancy.curr_balance <= 1000 THEN '£750.01 to £1,000'
             WHEN dbo.re_tenancy.curr_balance > 1000.01
                  AND dbo.re_tenancy.curr_balance <= 1250 THEN '£1,000.01 to £1,250'
             WHEN dbo.re_tenancy.curr_balance > 1250.01
                  AND dbo.re_tenancy.curr_balance <= 1500 THEN '£1,250.01 to £1,500'
             WHEN dbo.re_tenancy.curr_balance > 1500 THEN 'Over £1500'
             WHEN dbo.re_tenancy.curr_balance = 0 THEN 'Zero Balance'
             WHEN dbo.re_tenancy.curr_balance < 0 THEN 'In Credit'
             ELSE NULL
        END AS Amount_Band ,
        FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) AS Age ,
        CASE WHEN FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) < 18
                  AND FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) >= 16
                  AND dbo.co_person.deceased = 0 THEN 'Under 18'
             WHEN FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) >= 18
                  AND FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) < 24.9
                  AND dbo.co_person.deceased = 0 THEN '18-24 Yrs'
             WHEN FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) >= 25
                  AND FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) < 39.9
                  AND dbo.co_person.deceased = 0 THEN '25-39 Yrs'
             WHEN FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) >= 40
                  AND FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) < 49.9
                  AND dbo.co_person.deceased = 0 THEN '40-49 Yrs'
             WHEN FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) >= 50
                  AND FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) < 59.9
                  AND dbo.co_person.deceased = 0 THEN '50-59 Yrs'
             WHEN FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) >= 60
                  AND FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) < 64.9
                  AND dbo.co_person.deceased = 0 THEN '60-64 Yrs'
             WHEN FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) >= 65
                  AND FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) < 79.9
                  AND dbo.co_person.deceased = 0 THEN '65-79 Yrs'
             WHEN FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) >= 80
                  AND dbo.co_person.deceased = 0 THEN 'Over 80 Yrs'
             WHEN FLOOR(( CAST(GETDATE() AS INTEGER) - CAST(dbo.co_person.d_o_b AS INTEGER) ) / 365.25) < 16
                  AND dbo.co_person.deceased = 0 THEN 'Minor'
             WHEN dbo.co_person.deceased = 1 THEN 'Deceased'
             ELSE NULL
        END AS Age_Range ,
        ( SELECT    SUM(CurrBalanceAtDate)
          FROM      dbo.Arrears_Details AS D1
          WHERE     D1.tncy_sys_ref = dbo.ih_officer.tncy_sys_ref
                    AND D1.trans_date <= dbo.ih_officer.trans_date
        ) AS Balance
FROM    dbo.co_place_details
        RIGHT OUTER JOIN dbo.re_tncy_trans
        INNER JOIN dbo.re_tenancy ON dbo.re_tncy_trans.tncy_sys_ref = dbo.re_tenancy.tncy_sys_ref
        INNER JOIN dbo.re_tncy_place ON dbo.re_tenancy.tncy_sys_ref = dbo.re_tncy_place.tncy_sys_ref
        INNER JOIN dbo.ih_location ON dbo.re_tncy_place.place_ref = dbo.ih_location.place_ref
        INNER JOIN dbo.re_tncy_person ON dbo.re_tenancy.tncy_sys_ref = dbo.re_tncy_person.tncy_sys_ref
        INNER JOIN dbo.re_tncy_type ON dbo.re_tenancy.tncy_type = dbo.re_tncy_type.tncy_type ON dbo.co_place_details.place_ref = dbo.ih_location.place_ref
        LEFT OUTER JOIN dbo.co_person ON dbo.re_tncy_person.person_ref = dbo.co_person.person_ref
        LEFT OUTER JOIN dbo.co_place
        INNER JOIN dbo.co_place_type ON dbo.co_place.place_type = dbo.co_place_type.place_type ON dbo.ih_location.place_ref = dbo.co_place.place_ref
        LEFT OUTER JOIN dbo.ha_sub_area ON dbo.ih_location.sub_area_code = dbo.ha_sub_area.sub_area_code
        LEFT OUTER JOIN dbo.ih_officer AS ih_officer_2 ON dbo.ih_location.former_arrs_ofcr = ih_officer_2.officer_code
        LEFT OUTER JOIN dbo.ih_officer AS ih_officer_1 ON dbo.ih_location.estate_ofcr = ih_officer_1.officer_code
        LEFT OUTER JOIN dbo.ih_officer ON dbo.ih_location.arrears_ofcr = dbo.ih_officer.officer_code
WHERE   ( dbo.re_tncy_trans.tncy_sys_ref = 301 )
ORDER BY dbo.re_tenancy.tenancy_ref ,
        dbo.re_tncy_trans.created_date DESC ,
        dbo.re_tncy_trans.trans_date;

Open in new window


btw, please use the embed code feature instead of appending files.
0
deborahhowson00Author Commented:
Thanks Phil!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.