brahmareddy brahmareddy - 4 months ago 9
SQL Question

expecting output with out using left join

first table is my input and expecting output like second table with out using left join.
this is the table data

declare @table table
(customer_id int,
indicator bit,
salary numeric(22,6)
,netresult numeric(22,6))

INSERT INTO @table (
customer_id
,indicator
,salary
)
VALUES
(1,1,2000),
(1,1,3000),
(2,1,1000),
(1,0,500),
(1,1,5000),
(2,1,2000),
(2,0,100)

select * from @table order by customer_id,indicator desc


I tried in below method it works. Is there any better alternative?

SELECT a.customer_id
,a.indicator
,a.salary
,netresult=p_salary-(2*n_salary)
FROM (
SELECT customer_id
,indicator
,salary
,sum(salary) OVER (PARTITION BY customer_id) p_salary
FROM @table
) a
LEFT JOIN (
SELECT customer_id
,indicator
,salary
,sum(salary) OVER (PARTITION BY customer_id) n_salary
FROM @table
WHERE indicator = 0
) b ON a.customer_id = b.customer_id
order by customer_id,indicator desc


Expected Output

enter image description here

Answer

I think you want this:

select t.customer_id, t.indicator,
       sum(case when indicator = 1 then salary else - salary end) over (partition by customer_id) as netresult
form @table t;

No joins are necessary.

Comments