I required Tsql query to show total values from detail lines against the summary lines on top of the each group

Ejaz Maqbool 1 Reputation point
2020-09-17T08:06:51.953+00:00

I have following rows in table, I want TSQL Query to get sum of all detail rows in front of top summary row with "<>" sign. and so on, each group of lines has one summary row with item code starting/ending "<>" and followed by detailed rows until next summary row appears.

25454-tsql-problem.jpg

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
{count} votes

7 answers

Sort by: Most helpful
  1. Ejaz Maqbool 1 Reputation point
    2020-09-20T10:29:33.863+00:00

    Hi Thanks everyone appreciate your answers, I apologize I missed the part of out in my question. Please see image I need detail rows also in out.

    25924-output-required.jpg


  2. MelissaMa-MSFT 24,176 Reputation points
    2020-09-21T01:19:05.76+00:00

    Hi @Ejaz Maqbool ,

    I made some modifications on Jingyang's query and please refer below:

    declare @mytable as table ( [COUNTER] int, ITEM_CONTENT int, ITEM varchar(20), UNIT varchar(100), QTY int, [DAY] int, Amount int )  
          
     insert into @mytable values  
     ( 1226548, 100, '<MIC>', 'Mobilization, Installation and Commissioning inclusive of:', 1, NULL, NULL ),  
     ( 1226549, 101, 'MBDMB', 'Mobilization/Demobilization',  1, 30000, 30000 ),   
     ( 1226550, 102, 'INSCM', 'Dewatering Works for Construction of Shugaiq 3-IWP — Seawater Intake Area',  1, 60000, 60000 ),   
     ( 1226551, 103, 'CSPVC', 'Supply of Temporary uPVC Casings for Well Installation',  1, 20000, 20000 ),   
     ( 1226552, 104, 'AGRGT', 'Supplyof 3/8" Aggregates for Well Installation',  1, 8000, 8000 ),   
     ( 1226553, 105, 'INSCM', 'Installation of Discharge Network to Discharge Point (max.125m)',  1, 23000, 23000 ),   
     ( 1226554, 200, '<SHM>', 'System Hire Charge inclusive of:',  1, NULL, NULL ),  
     ( 1226555, 201, 'HMDWS', 'Hire of Deepwell Dewatering System',  1, 1080, 1080 ),   
     ( 1226556, 202, 'HMDLN', 'Hire of Discharge & Electrical Network Infrastructures',  1, 270, 270 ),   
     ( 1226557, 203, 'HMGEN', '24 Hr Maintenance Crew Available on Site',  1, 300, 300 ),   
     ( 1226558, 204, 'PRSNL', 'Hire of Personnel',  1, 350, 350 )  
          
      
     Select [COUNTER], ITEM_CONTENT,ITEM,UNIT,  
    QTY,[DAY],[Amount]  
     from   
      (select [COUNTER], ITEM_CONTENT,ITEM,UNIT,    
          Case when ITEM like '<%' then avg(QTY) over(partition by left(ITEM_CONTENT,2)) else QTY end QTY,   
          Case when ITEM like '<%' then sum([DAY])over(partition by left(ITEM_CONTENT,2))  else [DAY] end as [DAY],   
          Case when ITEM like '<%' then sum(Amount) over(partition by left(ITEM_CONTENT,2))else Amount end as Amount    
       from @mytable  
       ) t  
       where ITEM like '<%'  
    union  
     Select [COUNTER], ITEM_CONTENT,ITEM,UNIT,  
    null QTY,null [DAY],null [Amount] from @mytable  
    where ITEM not like '<%'  
    order by COUNTER  
    

    Output:
    25995-output.png

    Best regards
    Melissa


    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.

    0 comments No comments