Please I need your assistance on using Categorized subquery in SQL.
I have two tables (Underwriting_Policy_Schedule_Header) which stored the category headers while the second Table (Underwriting_Policy_Schedule) stored the header body. both are related using the HeaderCode.
What I wanted to achieve is shown below:
Section SerialNo Description Premium
A Material Damage
1 On main hotel building of 32 bedrooms including but not limited to all 50,000.00
2 On complete boundary walls, gates and fences including electric fence 5,000.00
Subtotal 55,000.00
B Plant All Risk
1 Generators 2,000.00
2 Sewage Treatment Plant 5,000.00
3 Transformer( 300 KVA) 10,000.00
Subtotal 17,000.00
C Public Liability
1 Limit of Indemnity Subtotal 25,000.00
D Electronic Equipment
1 On electronic equipments of all Elect. but not limited TV Subtotal 7,000.00
Grand Total 104,000.00
The SQL query i have tried is pasted below:
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY
HeaderCode) AS NUMBER, *
From (
SELECT HeaderCode, PolicyRefCode, ScopeofCover, CoverExtension, CoverExclusion, SubjectMatter, Remark, -1 CompanyOrder, Excess, 0 LTADiscount FROM Underwriting_Policy_Schedule_Header
WHERE HeaderCode='0021'
UNION
SELECT b.HeaderCode, PolicyRefCode, b.Description, b.SumInsured, b.PremiumAmount, b.NetPremiumAmount, b.NetCommission, 0 CompanyOrder, b.LTA_Amount, b.LTADiscount FROM Underwriting_Policy_Schedule b
WHERE HeaderCode='0021') AS TBLALL
) AS TBL
ORDER BY HeaderCode, PolicyRefCode, CompanyOrder ASC
Any assistance will be greatly appreciated.



