SQL Server query problem
- by user335160
I want to achieved the results shown in the attached image. The Table Structure and Data are the ffg below:
Table Relationship
Overall IB Limit->one to many-> Facility Limit
Facility Limit->one to many-> Facility Sub Limit
Tables Structure and Data
Overall IB Limit
Id SCAF Reference Approval Date
1 NEW-001 January 1, 2011
2 NEW-002 January 2, 2011
3 NEW-003 January 3, 2011
----------------------------
Facility Limit
Id OverallIBLimitId Product Type
1 1 RPA
2 1 CG
3 2 RPA
4 3 CG
----------------------------
Facility Sub Limit
Id FacilityLimitId Sub-Limit Type Amount Tenor Status Status Date
1 1 RPA at max 2,000,0000.00 2 months Approved January 5, 2011
2 1 Oil 3,000,0000.00 3 yrs Approved January 5, 2011
3 2 CG at minor 4,000,0000.00 1 yr Approved January 5, 2011
4 2 CG at max 5,000,0000.00 6 months Approved January 5, 2011
5 2 Flood Component 1 5,000,0000.00 6 months Approved January 5, 2011
6 2 Flood Component 2 6,000,0000.00 3 yrs Approved January 5, 2011
7 3 RPA at minor 1,000,0000.00 6 months Approved January 5, 2011
8 4 One-Off 1,000,0000.00 6 months Approved January 5, 2011
