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

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

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

{count} votes

7 answers

Sort by: Most helpful
  1. answered 2020-09-17T08:44:33.517+00:00
    Olaf Helper 25,476 Reputation points

    That more a task for a presentation layer, like a SSRS report.

    No comments

  2. answered 2020-09-17T09:06:18.23+00:00
    Ejaz Maqbool 1 Reputation point

    Hi OlafHelper,
    Issue is whole group of items can repeat, if I do grouping on ssrs then it will be in one group as summary item code will be same.


  3. answered 2020-09-17T11:01:33.353+00:00
    Ejaz Maqbool 1 Reputation point

    Hi please check

    25447-image.png

    No comments

  4. answered 2020-09-17T11:48:29.123+00:00
    Viorel 82,286 Reputation points

    Try one of the solutions:

    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 * from @mytable
    
    ---
    
    
    ;
    with Q1 as
    (
        select *
        from @mytable 
        where ITEM like '<%'
    ), 
    Q2 as
    (
        select *, (select count(*) from Q1 where [COUNTER] <= t.[COUNTER]) as g
        from @mytable as t
    ),
    Q3 as
    (
        select g, SUM([DAY]) as [DAY], SUM(Amount) as Amount
        from Q2
        where [COUNTER] not in (select [COUNTER] from Q1)
        group by g
    )
    select Q1.[COUNTER], Q1.ITEM_CONTENT, Q1.ITEM, Q1.UNIT, 
        FORMAT( Q1.QTY, '#,##0') as QTY, 
        FORMAT( Q3.[DAY], '#,##0') as [DAY], 
        FORMAT( Q3.Amount, '#,##0') as Amount  
    from Q3
    inner join Q2 on Q2.G = Q3.g
    inner join Q1 on Q1.[COUNTER] = Q2.[COUNTER]
    order by [COUNTER]
    

  5. answered 2020-09-17T14:11:06.427+00:00
    Jingyang Li 1 Reputation point
    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