Блог

Главная - Вопросы по программированию - Функция суммы с предложением WHERE для каждой суммы?

Функция суммы с предложением WHERE для каждой суммы?

#sql #sql-server #tsql #sum #pivot

#sql #sql-сервер #tsql #сумма #сводная

Вопрос:

У меня есть две таблицы: персонал и часы.

Штат (более 200 строк) с:

Staff_ID, имя, должность

каждый сотрудник появляется в этой таблице только один раз

Часы (более 1000 строк) с:

Staff_ID, часы, Сумма, оплачиваемый флаг, Офис, дата-период

каждый сотрудник появляется в этой таблице много раз

Мне нужен запрос, который извлекает весь персонал из таблицы Staff с position = ‘Assistant’, а столбцы Hours и Amount из таблицы Hours суммируются дважды, каждый с разной логикой для каждой суммы.

Что-то вроде этого (это запрос на простом английском языке, чтобы помочь с объяснением):

 select s.name,
sum(h.hours) as 'Hours_Not_Billable' --[NEED IT TO SUM ONLY HOURS WHERE h.BILLABLEFLAG <> 'Y', h.office = '2', h.period = 'Q3' AND ONLY DO IT FOR EACH STAFF MEMBER],
sum(h.amount) as 'Amount_Not_Billable' --[NEED IT TO SUM ONLY AMOUNT WHERE h.BILLABLEFLAG <> 'Y', h.office = '2', h.period = 'Q3' AND ONLY DO IT FOR EACH STAFF MEMBER],
sum(h.hours) as 'Hours_Billable' --[NEED IT TO SUM ONLY HOURS WHERE h.BILLABLEFLAG = 'Y', h.office = '2', h.period = 'Q3' AND ONLY DO IT FOR EACH STAFF MEMBER],
sum(h.amount) as 'Amount_Billable --[NEED IT TO SUM ONLY HOURS WHERE h.BILLABLEFLAG = 'Y', h.office = '2', h.period = 'Q3' AND ONLY DO IT FOR EACH STAFF MEMBER]'
from Staff S 
left join Hours H on S.Staff_ID = H.Staff_ID

where s.position = 'Assistant'
group by s.name

Помощь очень ценится!

Спасибо

Комментарии:

1. Попробуйте выполнить поиск [tsql] conditional aggregation . case Вставляя выражение внутри каждого sum , вы можете контролировать, какие значения добавляются.

Ответ №1:

Используйте условную агрегацию. Идея состоит в том, чтобы поместить case внутри выражение sum() , которое отфильтровывается с учетом значений.

Ваш псевдокод будет переводиться как:

 select 
    s.name,
    sum(case when h.billableflag <> 'Y' then h.hours  else 0 end) hours_not_billable,
    sum(case when h.billableflag <> 'Y' then h.amount else 0 end) amount_not_billable,
    sum(case when h.billableflag =  'Y' then h.hours  else 0 end) hours_billable,
    sum(case when h.billableflag =  'Y' then h.amount else 0 end) amount_billable
from staff s 
left join hours h 
    on s.staff_id = h.staff_id and h.office = 2 and h.period = 'Q3'
where s.position = 'Assistant'
group by s.name

Примечание: большинство условий являются общими для всех выражений, поэтому я перенес их в on предложение the left join .

Комментарии:

1. Я думаю, что лучше переместить другое условие ( h.office = 2 and h.period = 'Q3' ) в WHERE предложение и оставить соединение с индексированным столбцом для лучшей производительности. Вы согласны?

2. @SaeedEsmaeelinejad: это не будет делать то же самое. У нас есть a left join : включение условий в where предложение превратит его в an inner join .

Ответ №2:

Вы можете использовать СЛУЧАЙ, когда внутри функции агрегирования SUM() следующим образом

 select 
s.name,
sum(case when h.BILLABLEFLAG <> 'Y' and h.office = '2' and h.DatePeriod = 'Q3' then h.hours else null end) as 'Hours_Not_Billable', 
sum(case when h.BILLABLEFLAG <> 'Y' and h.office = '2' and h.DatePeriod = 'Q3' then h.amount else null end) as 'Amount_Not_Billable', 
sum(case when h.BILLABLEFLAG = 'Y' and h.office = '2' and h.DatePeriod = 'Q3' then h.hours else null end) as 'Hours_Billable',
sum(case when h.BILLABLEFLAG = 'Y' and h.office = '2' and h.DatePeriod = 'Q3' then h.amount else null end ) as 'Amount_Billable'
from Staff S 
left join Hours H on S.Staff_ID = H.Staff_ID
where s.position = 'Assistant'
group by s.name