SUM Output double counting

Question

Hi,

I am trying to output my table to only capture the total value once.

As you can see from the attached table, the first 2 rows are the values and output that I need as it's split 70/30, however, the 3rd row is a total of rows 1 and 2 which I don't want to include as it's double counting.

I do understand why it's been captured because my logic asks to return data where 'Label' = 'A'.

The reason for applying that logic is due to not every job being split with a percentage allocation so I can have a job where 'Label' = 'A' and 'Percentage_Allocation' = 0 (as seen in rows 4 & 5 of screenshot)

This is my logic that outputs the table above.

select
distinct
a.mort_no,
case when isnumeric(substring(a.cust_no,5,6)) = 1 then substring(a.cust_no,5,6) else null end as cust_no,
c.start_date
substring(a.comp_id,3,6) as comp_id,
b.userid,
case percentage_allocation when 0 then sum(c.val) over (partition by a.mort_no , c.start_date, e.percentage_allocation, e.label )
else
sum(c.val) over (partition by a.mort_no , c.start_date, e.percentage_allocation, e.label ) * (e.percentage_allocation / 100) end as compvalue,
e.percentage_allocation,
e.label,
'mp' as system

from [mo_db].[dbo].[mo_table] as a
left join [mo_db].[dbo].[id_table] as b
on a.[userid] = b.[userid]

left join [mo_db].[dbo].[jt_table] as c
on a.mort_no = c.mort_no

left join [mo_db].[dbo].[lookup] as e
on substring(a.cust_no,5,6) = substring(e.cust_no,5,6)

where e.label in ('aaa','aab','a')

This is what I want to be able to see with ROW 3 missing from the above screenshot. Any idea how to do this please?

Answer 1

Hi @Bone_12 ，

Please have a try with below:

;with cte as (  
select  
distinct  
a.mort_no,  
case when isnumeric(substring(a.cust_no,5,6)) = 1 then substring(a.cust_no,5,6) else null end as cust_no,  
c.start_date,  
substring(a.comp_id,3,6) as comp_id,  
b.userid,  
case percentage_allocation when 0 then sum(c.val) over (partition by a.mort_no , c.start_date, e.percentage_allocation, e.label )  
else  
sum(c.val) over (partition by a.mort_no , c.start_date, e.percentage_allocation, e.label ) * (e.percentage_allocation / 100) end as compvalue,  
e.percentage_allocation,  
e.label,  
'mp' as system  
from [mo_db].[dbo].[mo_table] as a  
left join [mo_db].[dbo].[id_table] as b  
on a.[userid] = b.[userid]  
left join [mo_db].[dbo].[jt_table] as c  
on a.mort_no = c.mort_no  
left join [mo_db].[dbo].[lookup] as e  
on substring(a.cust_no,5,6) = substring(e.cust_no,5,6)  
where e.label in ('aaa','aab','a'))  
select * from cte  
except  
select * from cte  
where mort_no in (select mort_no from [mo_table] where percentage_allocation<>0) and percentage_allocation=0  

If above is not working, we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.