Присоединиться к дополнительной таблице без агрегатов, суммирующих повторяющиеся значения

У меня есть следующий код.

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

Могу ли я добавить следующее

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 и Salesperson_Invoiced - это поле, к которому можно присоединиться :)

так что я могу сделать так, чтобы это выглядело так?

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

Каждый раз, когда я пытаюсь присоединиться к таблицам, данные сходят с ума и ошибаются!

Примите вашу помощь!

Вот пример выводимых данных, которые я получаю:

Таблица первая:

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

Таблица 2

Salesperson_Invoiced BO_AUD
James 500000
Harry 600000
Potter 700000

Вот чего я пытаюсь достичь:

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

Код, который я пытался использовать, был

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

Но когда я пытаюсь использовать это A) Реальные данные одного из MTD идут от 6586.00 до 111962.00 B) BO_AUD для того же человека выдувается до 10907652.210, где должно быть 119374.310


sql sql-server-2014
person James Smith    schedule 22.03.2021    source источник
comment
данные сходят с ума и очень неверны. Предоставьте конкретную информацию об ошибке.   -  person OldProgrammer    schedule 23.03.2021
comment
Пожалуйста, предоставьте образцы данных и краткое объяснение того, что вам нужно.   -  person Stu    schedule 23.03.2021
comment
привет Оба, я добавил еще к своему вопросу, надеюсь, это поможет :)   -  person James Smith    schedule 23.03.2021
comment
Предположительно, у продавца может быть много продаж в таблице истории. Точно так же у них может быть много открытых ордеров. Таким образом, у вас есть отношения "многие ко многим", и ваши строки умножаются при объединении. Чтобы исправить это, выполните группировку в подзапросе.   -  person HoneyBadger    schedule 23.03.2021
comment
@HoneyBadger прав, вы не можете просто использовать GROUP BY по всему набору, удалите предложение группы, чтобы просмотреть все строки, над которыми он работает. Здесь вам могут помочь подзапрос, перекрестное применение и / или оконные функции.   -  person Chris Schaller    schedule 23.03.2021

Ответы (2)


arrow_upward
1
arrow_downward

Суммируйте перед присоединением:

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
person Gordon Linoff    schedule 22.03.2021
comment
Привет, Гордон, это именно то, что мне нужно !! Большое спасибо ! - person James Smith; 23.03.2021

arrow_upward
0
arrow_downward

Похоже на случай APPLY, хотя это также можно сделать как соединение в CTE / производной таблице.

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;

Примечания:

  • Результат APPLY попадает в GROUP BY, поскольку он уже сгруппирован для каждого Salesperson_Invoiced
  • Предлагаю вам найти лучший метод фильтрации по дате. Чтобы использовать индекс, у вас не может быть функции для столбца. Поэтому вместо month(t.TranDate) = Month(getdate())... используйте t.TranDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AND t.TranDate < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()) + 1, 1)
person Charlieface    schedule 23.03.2021
comment
Привет, спасибо за решение, но похоже, что оно приводит к неверным данным :( - person James Smith; 23.03.2021