brahmareddy brahmareddy - 1 year ago 76
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 (

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
SELECT customer_id
,sum(salary) OVER (PARTITION BY customer_id) p_salary
FROM @table
) a
SELECT customer_id
,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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download