Solved

nested sub query combination

Posted on 2014-10-15
12
92 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
  • 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 32

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
 

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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 32

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now