Answered by:
selecting non refunded records
Question

Hello,
here this is how the table looks like
id amount srv_cd
1 100.00 sv
2 100.00 sv
3 100.00 sv
4 200.00 sc
5 200.00 sc
6 300.00 scdI'm trying to get result set in this format ,If the srv_cd has negative value in it then srv_cd should be grouped to find the sum of the srv_cd for example srv_cd=sv sum should be 100 and if there are no negative values it shouldn't be aggregated.
The output should look like this
Amount srv_cd
100.00 sv
200.00 sc
200.00 sc
300.00 scd
Any help is appreciated.
Here is the sample data
create
table#duplicatepay
(
id
intidentity(1,1),
amount
decimal(11,2),
srv_cd
varchar(10)
)
insert
into#duplicatepay(amount,srv_cd)
values
(100,'sv'),
(
100,'sv'),
(
100,'sv')
,(
200,'sc')
,(
200,'sc')
,(
300,'scd')
sree
 Edited by srikanth.kasu Friday, June 23, 2017 6:42 PM
Answers

Here's one way to do it...
CREATE TABLE [#duplicatepay] ( [id] INT IDENTITY(1, 1) ,[amount] DECIMAL(11, 2) ,[srv_cd] VARCHAR(10) ); INSERT INTO [#duplicatepay] ( [amount] ,[srv_cd] ) VALUES (100, 'sv') ,(100, 'sv') ,(100 ,'sv' ) ,(200, 'sc') ,(200, 'sc') ,(300, 'scd'); SELECT SUM([dp].[amount]) ,[dp].[srv_cd] FROM [#duplicatepay] AS [dp] WHERE [dp].[srv_cd] IN ( SELECT [sq].[srv_cd] FROM [#duplicatepay] AS [sq] WHERE [sq].[amount] < 0 ) GROUP BY [dp].[srv_cd] UNION ALL SELECT [dp].[amount] ,[dp].[srv_cd] FROM [#duplicatepay] AS [dp] WHERE [dp].[srv_cd] NOT IN ( SELECT [sq].[srv_cd] FROM [#duplicatepay] AS [sq] WHERE [sq].[amount] < 0 ); DROP TABLE [#duplicatepay];
 Proposed as answer by Sarat (SS) Friday, June 23, 2017 8:18 PM
 Marked as answer by srikanth.kasu Thursday, June 29, 2017 9:14 PM

create table #duplicatepay(id int identity(1,1),amount decimal(11,2),srv_cd varchar(10)) insert into #duplicatepay(amount,srv_cd) values(100,'sv'),(100,'sv'),(100,'sv') ,(200,'sc'),(200,'sc'),(300,'scd') Select Sum(Amount) Amount, srv_cd from #duplicatepay WHERE srv_cd IN( Select srv_cd from #duplicatepay where sign(amount)=1) Group by srv_cd Union ALL Select Amount, srv_cd from #duplicatepay a WHERE Not exists ( Select srv_cd from #duplicatepay d where sign(amount)=1 and a.srv_cd=d.srv_cd ) drop table #duplicatepay
 Proposed as answer by Sarat (SS) Friday, June 23, 2017 8:18 PM
 Marked as answer by srikanth.kasu Thursday, June 29, 2017 9:14 PM
All replies

Here's one way to do it...
CREATE TABLE [#duplicatepay] ( [id] INT IDENTITY(1, 1) ,[amount] DECIMAL(11, 2) ,[srv_cd] VARCHAR(10) ); INSERT INTO [#duplicatepay] ( [amount] ,[srv_cd] ) VALUES (100, 'sv') ,(100, 'sv') ,(100 ,'sv' ) ,(200, 'sc') ,(200, 'sc') ,(300, 'scd'); SELECT SUM([dp].[amount]) ,[dp].[srv_cd] FROM [#duplicatepay] AS [dp] WHERE [dp].[srv_cd] IN ( SELECT [sq].[srv_cd] FROM [#duplicatepay] AS [sq] WHERE [sq].[amount] < 0 ) GROUP BY [dp].[srv_cd] UNION ALL SELECT [dp].[amount] ,[dp].[srv_cd] FROM [#duplicatepay] AS [dp] WHERE [dp].[srv_cd] NOT IN ( SELECT [sq].[srv_cd] FROM [#duplicatepay] AS [sq] WHERE [sq].[amount] < 0 ); DROP TABLE [#duplicatepay];
 Proposed as answer by Sarat (SS) Friday, June 23, 2017 8:18 PM
 Marked as answer by srikanth.kasu Thursday, June 29, 2017 9:14 PM

create table #duplicatepay(id int identity(1,1),amount decimal(11,2),srv_cd varchar(10)) insert into #duplicatepay(amount,srv_cd) values(100,'sv'),(100,'sv'),(100,'sv') ,(200,'sc'),(200,'sc'),(300,'scd') Select Sum(Amount) Amount, srv_cd from #duplicatepay WHERE srv_cd IN( Select srv_cd from #duplicatepay where sign(amount)=1) Group by srv_cd Union ALL Select Amount, srv_cd from #duplicatepay a WHERE Not exists ( Select srv_cd from #duplicatepay d where sign(amount)=1 and a.srv_cd=d.srv_cd ) drop table #duplicatepay
 Proposed as answer by Sarat (SS) Friday, June 23, 2017 8:18 PM
 Marked as answer by srikanth.kasu Thursday, June 29, 2017 9:14 PM

Try
;with cte as ( select srv_cd from #duplicatepay where amount<0 ) select srv_cd, sum(amount) as amount from #duplicatepay where srv_cd in (select srv_cd from cte ) group by srv_cd UNION ALL select srv_cd, amount from #duplicatepay where srv_cd not in (select srv_cd from cte )
I dont see any perf change compared to other solutions, just that rows with negative amounts are filtered at one place .
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
 Edited by Sarat (SS) Friday, June 23, 2017 8:21 PM

or this:
create table #duplicatepay(id int identity(1,1),amount decimal(11,2),srv_cd varchar(10)) insert into #duplicatepay(amount,srv_cd) values(100,'sv'),(100,'sv'),(100,'sv') ,(200,'sc'),(200,'sc'),(300,'scd') ;with mycte as ( Select (Amount) Amount, srv_cd, isnull(Sum(case when amount>=0 then null else 1 End) Over(partition by srv_cd order by sign(amount) ) ,Row_NUMBER() Over(partition by srv_cd order by id )) grp from #duplicatepay ) Select Sum(Amount) Amount, srv_cd from mycte Group by srv_cd,grp drop table #duplicatepay
 Edited by Jingyang LiModerator Friday, June 23, 2017 8:29 PM

Hi srikanth,
According to your description, I understand that you want to calculate the total amount in the group srv_cd that has negative value of the column amount. If there are no negative values of the column amount, the column amount will not be calculated. Based on this, you may use Case when clause, Table joins, group by clause to achieve your requirements. Here is the example code for your reference.
create table #duplicatepay ( id int identity(1,1), amount decimal(11,2), srv_cd varchar(10) ) insert into #duplicatepay(amount,srv_cd) values(100,'sv'),(100,'sv'),(100,'sv'),(200,'sc'),(200,'sc'),( 300,'scd') GO with cte as ( select case T1.sign_value when 1 then 1 else T.id end as id,T.amount,T.srv_cd from #duplicatepay T left join ( select case when min(amount)>0 then 1 else 1 end as sign_value,srv_cd from #duplicatepay group by srv_cd) T1 on T.srv_cd=T1.srv_cd ) select sum(Amount) as Amount,max(srv_cd) as srv_cd from cte group by id
Hope above could be helpful to you.
Best Regards,
Will
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.