Getting A categorised Subquery in SQL

Question

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.

Accepted Answer

Hi @lawrence ，

Thank you for the update.

I can't see the picture you expect to output. Sometimes uploading screenshots will fail. I often encounter this situation. You need to upload again and check if the upload is successful.

I did a test based on your original expected output. I encountered some confusion:

Why does the description belonging to A in your table belong to B in your expected result?And I'm not sure how you get the description column in your expected result.
How is your Premium column calculated, I can’t find the relationship between it and the PremiumRate column.

Here is what I currently do:

 CREATE TABLE [dbo].[Underwriting_Policy_Schedule_Header_Tittle](  
  [HeaderCode] [char](4)  NOT NULL PRimary key,  
  [HeaderDescription] [varchar](200) NULL,  
  [HeaderShortDescription] [varchar](70) NULL,  
      
 )  
      
      
 INSERT INTO [dbo].[Underwriting_Policy_Schedule_Header_Tittle]  
  VALUES  
      ('0004','PLANT ALL RISKS','PAR'),  
      ('0005','MATERIAL DAMAGE','MAT DMG'),  
     ('0006','PUBLIC LIABILITY','PUB. LIAB.'),  
     ('0007','ELECTRONIC EQUIPMENT INSURANCE','ELECT. EQUIP. INS.')  
      
 CREATE TABLE [dbo].[Underwriting_Policy_Schedule_Header](  
  [RecordID] [int]  NOT NULL, -- PK  
  [PolicyRefCode] [char](10) NOT NULL,  -- FK  
  [HeaderCode] [char](4) NULL, -- FK  
  [ScheduleCategory] [char](1) NULL,  
  [ScheduleName] [varchar](150) NULL,  
  [ScopeofCover] [varchar](2048) NULL,  
  [CoverExtension] [varchar](2048) NULL,  
 )   
      
 INSERT INTO [dbo].[Underwriting_Policy_Schedule_Header]  
            ([RecordID]  
            ,[PolicyRefCode]  
     ,[HeaderCode]  
     ,[ScheduleCategory]  
     ,[ScheduleName]  
  ,[ScopeofCover]  
     ,[CoverExtension]  
     )  
  VALUES  
      (13202,'0000000001','0004','A','PLANT ALL RISK','SSRC - Free Plus Flood & Tornado - Free','The policy will provide indemnity for all risks of physical loss or damage including loss or damage occasioned by electrical and mechanical breakdown which are normal insurable and not normally excluded, including all contents, machinery, plants, equipments, materials, stock, spares, accessories and real property of any description which are the properties of the insured.'),  
            (13203,'0000000002','0005','B','MATERIAL DAMAGE','SSRC - Free ','The policy will indemnify you in respect of any loss arising from claims for breach of duty which may be made against you by reason of any neglect, error or ommission whatever or whereever committed or alleged to have been committed in the conduct of your business in a professional capacity by any person in your employment.'),  
     (13204,'0000000003','0006','C','PUBLIC LIABILITY','Flood & Tornado -free ','The policy will indemnify you in respect of any loss arising from claims for breach of duty which may be made against you by reason of any neglect, error or ommission whatever or whereever committed or alleged to have been committed in the conduct of your business in a professional capacity by any person in your employment.'),  
     (13205,'0000000004','0007','D','ELECTRONIC EQUIPMENT','Flood & Tornado -free ','The policy will provide indemnity for all risks of physical loss or damage including loss or damage occasioned by electrical and mechanical breakdown which are normal insurable and not normally excluded, including all contents, machinery, plants, equipments, materials, stock, spares, accessories and real property of any description which are the properties of the insured.')  
  
      
 CREATE TABLE [dbo].[Underwriting_Policy_Schedule](  
  [RecordID] int IDENTITY(1,1) NOT NULL,   
  [ItemNo] char(10),  
  [PolicyRefCode] char(10),     
  [HeaderCode] char(4) NULL,   
  [ScheduleCategory] char(1) NULL,  
  [Description] varchar(2048) NULL,  
  [SumInsured] decimal(19, 4) NULL,  
  [PremiumRate] decimal(10, 7) NULL,  
  [PremiumAmount] decimal(19, 4) NULL,  
  [NetPremiumAmount] decimal(19, 4) NULL,  
      
 )  
      
 INSERT INTO [dbo].Underwriting_Policy_Schedule  
          ([ItemNo]  
  ,[PolicyRefCode]    
  ,[HeaderCode]  
  ,[ScheduleCategory]  
  ,[Description]  
  ,[SumInsured]  
  ,[PremiumRate]  
  ,[PremiumAmount]  
  ,[NetPremiumAmount])  
  VALUES  
      ('1','0000000001','0004','A','Generators',37221450,1.5,558321.75,558321.75),  
     ('2','0000000001','0004','A','Sewage Treatment Plant',14987000.00,0.2,3435069.58,3435069.58),  
     ('3','0000000001','0004','A','Transformer( 300 KVA)',10532200.00,1.8,189579.60,189579.60),  
     ('4','0000000001','0004','A','Elevator',39900000.00,1.2,478800.00, 478800.00),  
      
     ('1','0000000002','0005','B','On main hotel building of 32 bedrooms including but not limited to all outbuildings, water tanks,boreholes,pumps,diesel tank, landlords fixtures and fiittings situate 18, Obafemi Anibaba, off Admirality Road beside FoodcoLekki Lagos.',460700953.15,0.2,921401.91, 921401.91),  
     ('2','0000000002','0005','B','On complete boundary walls, gates and fences including electric fence',200000,0.2,40000.58,40000.58),  
     ('3','0000000002','0005','B','On all items of any description including but not limited to Furniture, Furnishings, decorative Light fittings, Kitchen equipment of various description,Coldroom,refrigerators, Chandeliers Air Conditioning,Painting and Artworks,Water heater,Bed and Mattresses, Personal effect of Guest, used and unused Stationery, HVAC/Dry contact and all other contents contained in the hotel',581001992.87,0.9,522901.74,522901.74),  
      
     ('1','0000000003','0006','C','Limit of Indemnity',25000000,0.1,25000.00,25000.00),  
     ('2','0000000003','0006','C','Limit ro Third Party Property Damage',10000000,0.1,10000.00,10000.00),  
         
     ('1','0000000004','0007','D','On electronic equipments of all description including but not limited to Television ',63239115.80,0.5,318195.58,318195.58),  
     ('2','0000000004','0007','D','On Computer Related devices',200000,0.2,40000.58,40000.58),  
     ('3','0000000004','0007','D','On Electronic Apllicances',10279786,0.5,51398.93,51398.93)  
  
SELECT * FROM [dbo].[Underwriting_Policy_Schedule_Header_Tittle]  
SELECT * FROM [dbo].[Underwriting_Policy_Schedule_Header]  
SELECT * FROM [dbo].[Underwriting_Policy_Schedule]  
  
  
;WITH cte  
as(SELECT us.[HeaderDescription],up.[ScheduleCategory]  
FROM [dbo].[Underwriting_Policy_Schedule_Header_Tittle] us  
JOIN [dbo].[Underwriting_Policy_Schedule_Header] up  
ON us.HeaderCode=up.HeaderCode)  
,cte2 as(SELECT c.[ScheduleCategory],c.[HeaderDescription],ups.[ItemNo],ups.[Description],ups.[PremiumAmount]  
FROM cte c  
JOIN [dbo].[Underwriting_Policy_Schedule] ups  
ON c.[ScheduleCategory]=ups.[ScheduleCategory])  
,cte3 AS(SELECT  DISTINCT [ScheduleCategory],NULL [ItemNo],[HeaderDescription],NULL [PremiumAmount]  
FROM cte2   
UNION ALL  
SELECT  [ScheduleCategory],[ItemNo],[Description],[PremiumAmount]  
FROM cte2)  
  
SELECT * FROM cte3  
ORDER BY [ScheduleCategory],[ItemNo]  
  
  
DROP TABLE [dbo].[Underwriting_Policy_Schedule_Header_Tittle]  
DROP TABLE [dbo].[Underwriting_Policy_Schedule_Header]  
DROP TABLE [dbo].[Underwriting_Policy_Schedule]

Output:

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer

Generating what you describe using pure TSQL is not going to work. What you are trying to do is a report. You should use a reporting tool like SSRS, or even Excel.

Answer

Hi @lawrence ，

Welcome to microsoft TSQL Q&A forum!

It seems that what you want is the report. As Tom said, pure tsql cannot meet your needs and you need to use SSRS.

If you want to achieve it through SSRS, then you can post a new question on the SSRS forum, where people will provide you with more help.

Reporting Services (SSRS)
SQL Server Reporting Services installation, configuration, design, performance, troubleshooting, and other SSRS related questions.

Tsql can only get results similar to the following figure,If you want this result, please
share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result. So that we’ll get a right direction and make some test.

Also, is there anything wrong with your code?

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

CREATE TABLE [dbo].[Underwriting_Policy_Schedule_Header](
        [RecordID] [int] IDENTITY(1,1) NOT NULL, -- PK
        [PolicyRefCode] [char](10) NOT NULL,  -- FK
        [HeaderCode] [char](4) NULL, -- FK
        [ScheduleCategory] [char](1) NULL,
        [ScheduleName] [varchar](150) NULL,
        [ScopeofCover] [varchar](2048) NULL,
        [CoverExtension] [varchar](2048) NULL,
        [CoverExclusion] [varchar](2048) NULL,
    ) 

    INSERT INTO [dbo].[Underwriting_Policy_Schedule_Header]
               ([RecordID]
               ,[PolicyRefCode]
           ,[HeaderCode]
           ,[ScheduleCategory]
           ,[ScheduleName]
           ,[CoverExtension]
           ,[ScopeofCover])
     VALUES
           (13202,'0000000001''0004''A''PLANT ALL RISK''SSRC - Free ''The policy will provide indemnity for all risks of physical loss or damage including loss or damage which are the properties of the insured.'),
               (13203,'0000000002''0005''B''MATERIAL DAMAGE''SSRC - Free ''The policy will indemnify you in respect of any loss arising from claims for breach of duty which may be made against you by reason of any neglect, error or ommission.'),
           (13204,'0000000003''0006''C''PUBLIC LIABILITY''Flood & Tornado -free ''The policy will indemnify you in respect of any loss.'),
           (13205,'0000000004''0007''D''ELECTRONIC EQUIPMENT''Flood & Tornado -free ''The policy will provide indemnity for all risks of physical loss or damage.'),

Answer

Hi @EchoLiu-MSFT ,
Thank you so much for your detail response. I will go for the suggested TSQL figure sample result.
Below are the Schema and Insert Values for the 3 Tables involved to conduct your test. Again, I included the result output format.
Thanks a million.

CREATE TABLE [dbo].[Underwriting_Policy_Schedule_Header_Tittle](  
 [HeaderCode] [char](4)  NOT NULL PRimary key,  
 [HeaderDescription] [varchar](200) NULL,  
 [HeaderShortDescription] [varchar](70) NULL,  
  
)  
  
Data  
  
INSERT INTO [dbo].[Underwriting_Policy_Schedule_Header_Tittle]  
       ([HeaderCode]  
       ,[HeaderDescription]  
       ,[HeaderShortDescription])  
 VALUES  
     ('0004','PLANT ALL RISKS''PAR'),  
     ('0005','MATERIAL DAMAGE''MAT DMG'),  
    ('0006','PUBLIC LIABILITY''PUB. LIAB.'),  
    ('0007','ELECTRONIC EQUIPMENT INSURANCE''ELECT. EQUIP. INS.')  

  
CREATE TABLE [dbo].[Underwriting_Policy_Schedule_Header](  
 [RecordID] [int] IDENTITY(1,1) NOT NULL, -- PK  
 [PolicyRefCode] [char](10) NOT NULL,  -- FK  
 [HeaderCode] [char](4) NULL, -- FK  
 [ScheduleCategory] [char](1) NULL,  
 [ScheduleName] [varchar](150) NULL,  
 [ScopeofCover] [varchar](2048) NULL,  
 [CoverExtension] [varchar](2048) NULL,  
 [CoverExclusion] [varchar](2048) NULL,  
)   
  
INSERT INTO [dbo].[Underwriting_Policy_Schedule_Header]  
           ([RecordID]  
           ,[PolicyRefCode]  
    ,[HeaderCode]  
    ,[ScheduleCategory]  
    ,[ScheduleName]  
    ,[CoverExtension]  
    ,[ScopeofCover])  
 VALUES  
     (13202,'0000000001''0004''A''PLANT ALL RISK''SSRC - Free Plus Flood & Tornado - Free''The policy will provide indemnity for all risks of physical loss or damage including loss or damage occasioned by electrical and mechanical breakdown which are normal insurable and not normally excluded, including all contents, machinery, plants, equipments, materials, stock, spares, accessories and real property of any description which are the properties of the insured.'),  
           (13203,'0000000002''0005''B''MATERIAL DAMAGE''SSRC - Free ''The policy will indemnify you in respect of any loss arising from claims for breach of duty which may be made against you by reason of any neglect, error or ommission whatever or whereever committed or alleged to have been committed in the conduct of your business in a professional capacity by any person in your employment.'),  
    (13204,'0000000003''0006''C''PUBLIC LIABILITY''Flood & Tornado -free ''The policy will indemnify you in respect of any loss arising from claims for breach of duty which may be made against you by reason of any neglect, error or ommission whatever or whereever committed or alleged to have been committed in the conduct of your business in a professional capacity by any person in your employment.'),  
    (13205,'0000000004''0007''D''ELECTRONIC EQUIPMENT''Flood & Tornado -free ''The policy will provide indemnity for all risks of physical loss or damage including loss or damage occasioned by electrical and mechanical breakdown which are normal insurable and not normally excluded, including all contents, machinery, plants, equipments, materials, stock, spares, accessories and real property of any description which are the properties of the insured.'),  
  
  
CREATE TABLE [dbo].[Underwriting_Policy_Schedule](  
 [RecordID] [int] IDENTITY(1,1) NOT NULL, PK  
 [ItemNo] [char](10) NULL,  
 [PolicyRefCode] [char](10) NOT NULL,   FK  
 [HeaderCode] [char](4) NULL, FK  
 [ScheduleCategory] [char](1) NULL,  
 [Description] [varchar](2048) NULL,  
 [SumInsured] [decimal](19, 4) NULL,  
 [PremiumRate] [decimal](10, 7) NULL,  
 [PremiumAmount] [decimal](19, 4) NULL,  
 [NetPremiumAmount] [decimal](19, 4) NULL,  
  
)  
  
INSERT INTO [dbo].Underwriting_Policy_Schedule  
         ([ItemNo]  
 ,[PolicyRefCode]    
 ,[HeaderCode]  
 ,[ScheduleCategory]  
 ,[Description]  
 ,[SumInsured]  
 ,[PremiumRate]  
 ,[PremiumAmount]  
 ,[NetPremiumAmount])  
 VALUES  
     ('1','0000000001''0004''A''Generators',37221450,1.5,558321.75,558321.75),  
    ('2','0000000001''0004''A''Sewage Treatment Plant',14987000.00,0.2,3435069.58,3435069.58),  
    ('3','0000000001''0004''A''Transformer( 300 KVA)',10532200.00,1.8,189579.60,189579.60),  
    ('4','0000000001''0004''A''Elevator',39900000.00,1.2,478800.00, 478800.00),  
  
    ('1','0000000002''0005''B''On main hotel building of 32 bedrooms including but not limited to all outbuildings, water tanks,boreholes,pumps,diesel tank, landlords fixtures and fiittings situate 18, Obafemi Anibaba, off Admirality Road beside FoodcoLekki Lagos.',460700953.15,0.2,921401.91, 921401.91),  
    ('2','0000000002''0005''B''On complete boundary walls, gates and fences including electric fence',200000,0.2,40000.58,40000.58),  
    ('3','0000000002''0005''B''On all items of any description including but not limited to Furniture, Furnishings, decorative Light fittings, Kitchen equipment of various description,Coldroom,refrigerators, Chandeliers Air Conditioning,Painting and Artworks,Water heater,Bed and Mattresses, Personal effect of Guest, used and unused Stationery, HVAC/Dry contact and all other contents contained in the hotel',581001992.87,0.9,522901.74,522901.74),  
  
    ('1','0000000003''0006''C''Limit of Indemnity',25000000,0.1,25000.00,25000.00),  
    ('2','0000000003''0006''C''Limit ro Third Party Property Damage',10000000,0.1,10000.00,10000.00),  
     
    ('1','0000000004''0007''D''On electronic equipments of all description including but not limited to Television ',63239115.80,0.5,318195.58,318195.58),  
    ('2','0000000004''0007''D''On Computer Related devices',200000,0.2,40000.58,40000.58),  
    ('3','0000000004''0007''D''On Electronic Apllicances',10279786,0.5,51398.93,51398.93)  
  
   Best regards,  
   Lawrence  
  
Result output attached. ![119075-industrialallrisk3.png][1]

Share via

Getting A categorised Subquery in SQL

5 additional answers

Your answer