CODEWITHSUNDEEP
sqlsql-servert-sql
nickfinity
nickfinity

Reputation: 1119

Need help summing/grouping values

I don't know if I should be doing this in SQL or not. It's part of an SSIS package, so maybe I should be using a script task, but before I go that route I'd like to know for sure that's required.

I have a fiddle setup - http://sqlfiddle.com/#!18/f2aec/5.

Here is my data:

create table test (
    id int IDENTITY (1,1),
    account int,
    amount decimal(9,2),
    code varchar(10)
);

insert into test (account, amount, code)
values 
      (0, 100, 'A')
      ,(0, 100, 'B')

      ,(1, 100, 'A')
      ,(1, -50, 'B')
      
      ,(2, 100, 'A')
      ,(2, 200, 'B')
      ,(2, -100, 'C')
      
      ,(3, 100, 'A')
      ,(3, -200, 'B')
      ,(3, 50, 'C')
      
      ,(4, -500, 'B')
      ,(4, -500, 'C')
      
      ,(5, -1200, 'B')
      ,(5, 1000, 'C')
      
      ,(6, 150, 'A')
      ,(6, -100, 'B')
      ,(6, 200, 'C')
      
      ,(7, 150, 'A')
      ,(7, 200, 'B')
      ,(7, -100, 'C')
      
      ,(8, 550, 'A')
      ,(8, -700, 'B')
      ,(8, 300, 'C')
      ,(8, -100, 'D');

Here is my desired output:

-- desired output
-- 0 100.00 A
-- 0 100.00 B
-- 1 50.00 A
-- 2 200.00 B
-- 3 -50.00 A
-- 4 -500.00 B
-- 4 -500.00 C
-- 5 -200.00 B
-- 6 50.00 A
-- 6 200.00 B (currently wrong)
-- 7 50.00 A
-- 7 200.00 B (currently wrong)
-- 8 50.00 C (right now this is showing as C, but I would be okay if it was A)

Basically, if all records for an account are positive then I should show all records, same if all are negative, but if there is a mix I want to net the values and put them in the first code bucket.

Here is what I've tried so far (though I've been spinning my wheels so a completely fresh approach might be best).


;with cte as (
    select *
        ,sum(amount) over (PARTITION by account) as accounttotal
        ,sum(amount) over (PARTITION by account order by case when amount < 0 then 0 else 1 end, code) as runningtotal
        ,case when (select count(1) from test b where b.account = test.account and b.amount < 0) > 0 then 1 else 0 end as hasnegative
        ,ROW_NUMBER() over(partition by account order by case when amount < 0 then 0 else 1 end, code) as rownum
    from test
)
--select *  from cte 
,cte2 as (
    select 
        *
        ,max(rownum) over(partition by account ) as maxrownum 
        ,FIRST_VALUE(code) over(partition by account order by case when runningtotal > 0 then 0 else 1 end, code) as firstcode
        ,sum(case when amount < 0 then 1 else 0 end) over(partition by account) as negativecount
    from cte
)
--select * from cte2
select 
    --*,
    account as accountnumber
    ,case when hasnegative = 0 or negativecount = maxrownum then amount else runningtotal end as val
    ,case when hasnegative = 0 or negativecount = maxrownum then code else firstcode end as code

from cte2
where 
    runningtotal > 0 
    or rownum = maxrownum 
    or negativecount = maxrownum
order by account, code

Upvotes: 1

Views: 66

Answers (1)

eshirvana
eshirvana

Reputation: 24613

not sure , but this might be the answer you are looking for:

SELECT  account , SUM(amount) amount , min(code) code FROM (
SELECT *, dense_RANK() OVER (PARTITION BY account , CASE WHEN amount> 0 THEN 1 ELSE -1 END ORDER BY code) rn2 FROM (
    SELECT account , SUM(amount) amount , min(code) code FROM (
        SELECT *, dense_RANK() OVER (PARTITION BY account , CASE WHEN amount> 0 THEN 1 ELSE -1 END ORDER BY id) rn FROM test) tt
    GROUP BY account , rn
    HAVING SUM(amount) <> 0 
    ) tt2
) tt3
GROUP BY account , rn2
ORDER BY account

Upvotes: 2

Related Questions