Join to additional table without aggregates summing the duplicated values

1
votes

I have the following Code.

Select  t.Salesperson_Invoiced,        
        Sum(Case When month(t.TranDate) = Month(getdate()) Then t.NetNet_Revenue_Func End) MTD_REV,
        Sum(Case When month(t.TranDate) = Month(getdate()) Then t.GM_Func_Net End) MTD_GM,
        SUM (Case When t.Year = Year(getdate()) Then t.NetNet_Revenue_Func End) YTD_REV,
        SUM (Case When t.Year = Year(getdate()) Then t.GM_Func_Net End) YTD_GM
From    Sales_History t
Where   t.PG1    = 'Lighting' 
And     t.Office = 'AU' 
And     t.Year   = Year(getdate())
Group By t.Salesperson_Invoiced

is it possible for me to add the following

SELECT Salesperson_1,sum(Value_Func) as BO_AUD
FROM Datawarehouse.dbo.Open_Orders
where Office = 'AU' and PG1 = 'Lighting'
group by Salesperson_1

Salesperson_1 and Salesperson_Invoiced is the joinable field :)

so I can have it look like this ?

Salesperson_Invoiced NetNet_Revenue_Func MTD NetNet_Revenue_Func YTD GM_Func_Net MTD GM_Func_Net YTD BO_AUD
James 500 100
John 600 200
Peter 700 300
Harry 800 400
Potter 900 1

Every time I try and join the tables the data goes crazy and is very wrong !

Apricate your help!

Here is an example outputted data I get :

Table One:

Salesperson_Invoiced NetNet_Revenue_Func MTD NetNet_Revenue_Func YTD GM_Func_Net MTD GM_Func_Net YTD
James 500 1000 250 500
Harry 600 1200 300 600
Potter 700 1400 350 700

Table 2

Salesperson_Invoiced BO_AUD
James 500000
Harry 600000
Potter 700000

This is what i am trying to achieve :

Salesperson_Invoiced NetNet_Revenue_Func MTD NetNet_Revenue_Func YTD GM_Func_Net MTD GM_Func_Net YTD BO_AUD
James 500 1000 250 500 500000
Harry 600 1200 300 600 600000
Potter 700 1400 350 700 700000

The code i was trying to use was

Select distinct  t.Salesperson_Invoiced,        
        Sum(Case When month(t.TranDate) = Month(getdate()) Then t.NetNet_Revenue_Func End) MTD_REV,
        Sum(Case When month(t.TranDate) = Month(getdate()) Then t.GM_Func_Net End) MTD_GM,
        SUM (Case When t.Year = Year(getdate()) Then t.NetNet_Revenue_Func End) YTD_REV,
        SUM (Case When t.Year = Year(getdate()) Then t.GM_Func_Net End) YTD_GM,
        sum(Value_Func) as BO_AUD
From    Sales_History t
inner join Open_Orders on
t.Salesperson_Invoiced = Open_Orders.Salesperson_1
Where   t.PG1    = 'Lighting' 
And     t.Office = 'AU' 
And     t.Year   = Year(getdate())
Group By t.Salesperson_Invoiced,Salesperson_1

But when I try and use that A) Real data from one of the MTD goes from 6586.00 to 111962.00 B) BO_AUD for the same person blow out to 10907652.210 where it should be 119374.310

2
sqlsql-server-2014
"the data goes crazy and is very wrong". Please provide specific details on the error - OldProgrammer
Please supply sample data and concise explanation of what you need. - Stu
hello Both, I have added more to my question hopefully this helps :) - James Smith
Presumably, a salesperson can have many sales in the history table. Likewise, they can have many open orders. So you have a many to many relationship, and your rows multiplicate in the join. To remedy this, do the grouping in a subquery. - HoneyBadger
@HoneyBadger is right, you can't just use GROUP BY over the whole set, remove the group clause to review all the rows that it is operating over. Sub-query, Cross Apply and or window functions may assist you here. - Chris Schaller

2 Answers

1
votes

Aggregate before joining:

select sh.*, oo.*
from (Select sh.Salesperson_Invoiced,        
              Sum(Case When month(sh.TranDate) = Month(getdate()) Then sh.NetNet_Revenue_Func End) MTD_REV,
              Sum(Case When month(sh.TranDate) = Month(getdate()) Then sh.GM_Func_Net End) MTD_GM,
              SUM (Case When sh.Year = Year(getdate()) Then sh.NetNet_Revenue_Func End) YTD_REV,
              SUM (Case When sh.Year = Year(getdate()) Then sh.GM_Func_Net End) YTD_GM
      From Sales_History sh
      Where sh.PG1    = 'Lighting' AND
            sh.Office = 'AU' AND
            sh.Year   = Year(getdate())
      Group By sh.Salesperson_Invoiced
     ) sh left join
     (SELECT Salesperson_1, sum(Value_Func) as BO_AUD
      FROM Datawarehouse.dbo.Open_Orders
      where Office = 'AU' and PG1 = 'Lighting'
      group by Salesperson_1
     ) oo
     on sh.Salesperson_Invoiced = oo.Salesperson_1
0
votes

Looks like a case for an APPLY, although it can also be done as a join on a CTE/derived table.

Select  t.Salesperson_Invoiced,        
        Sum(Case When month(t.TranDate) = Month(getdate()) Then t.NetNet_Revenue_Func End) MTD_REV,
        Sum(Case When month(t.TranDate) = Month(getdate()) Then t.GM_Func_Net End) MTD_GM,
        SUM (Case When t.Year = Year(getdate()) Then t.NetNet_Revenue_Func End) YTD_REV,
        SUM (Case When t.Year = Year(getdate()) Then t.GM_Func_Net End) YTD_GM,
        o.BO_AUD
From    Sales_History t
OUTER APPLY (
    SELECT SUM(o.Value_Func) as BO_AUD
    FROM Open_Orders o
    WHERE t.Salesperson_Invoiced = o.Salesperson_1
) o
Where   t.PG1    = 'Lighting' 
And     t.Office = 'AU' 
And     t.Year   = Year(getdate())
Group By t.Salesperson_Invoiced, o.BO_AUD;

Notes:

  • The result of the APPLY goes into the GROUP BY as it is already grouped up against each Salesperson_Invoiced
  • I suggest you find a better method of filtering by date. In order to use an index, you can't have a function on the column. So instead of month(t.TranDate) = Month(getdate())... use t.TranDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AND t.TranDate < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()) + 1, 1)