none
selecting non refunded records RRS feed

  • 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 scd

    I'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




    Friday, June 23, 2017 6:38 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
    Friday, June 23, 2017 7:19 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
    Friday, June 23, 2017 7:51 PM
    Moderator

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
    Friday, June 23, 2017 7:19 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
    Friday, June 23, 2017 7:51 PM
    Moderator
  • 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
    Friday, June 23, 2017 8:18 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

     


    Friday, June 23, 2017 8:29 PM
    Moderator
  • 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.

    Monday, June 26, 2017 6:42 AM
    Moderator