Solved

nested sub query combination

Posted on 2014-10-15
12
98 Views
Last Modified: 2014-10-20
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
0
Comment
Question by:deborahhowson00
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
12 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40381902
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
 
LVL 33

Expert Comment

by:ste5an
ID: 40381935
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
 

Author Comment

by:deborahhowson00
ID: 40381951
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:deborahhowson00
ID: 40381955
Ste5an your solution still gives the same error I'm afraid!  Thanks.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40381956
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40381962
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
 

Author Comment

by:deborahhowson00
ID: 40381966
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40381971
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
 

Assisted Solution

by:deborahhowson00
deborahhowson00 earned 0 total points
ID: 40381974
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40381976
Good - glad I could help.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40381989
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
 

Author Closing Comment

by:deborahhowson00
ID: 40391577
Thanks Phil!
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

761 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question