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

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

Hi please check

Answer 4

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]

Answer 5

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