There's an invoice table, with the person who has created the invoice. A person can belong to multiple offices, only one main office per person but same person can have multiple roles per office.
declare @person table (personid int)
declare @office table (officeid int, officename varchar(10))
declare @personoffice table (personid int, officeid int, mainoffice bit, personrole varchar(10))
declare @invoice table (personid int)
insert into @person values (1), (2), (3), (4)
insert into @office values (1, 'office1'), (2, 'office2'), (3, 'office3'), (4, 'office4')
insert into @personoffice values (1, 1, 1, 'role1'), (1, 1, 1, 'role2'), (1, 2, 0, 'role1'), (1, 3, 0, 'rolex'), (2, 2, 1, 'role1'), (2, 2, 1, 'role2'), (2, 3, 0, 'rolex'), (3, 3, 1, 'role1'), (3, 4, 0, 'role2')
insert into @invoice values (1), (1), (1), (2), (2), (3), (3), (3), (3), (3)
So for this example we have 3 persons, they belong to multiple offices but only one main office each but some persons have multiple roles per office. They have each created multiple invoices.
I can get the number of invoices per person with:
select
i.personid,
count(*) InvoiceCountByPerson
from
@invoice i
inner join
@person p on p.personid = i.personid
group by
i.personid
which returns:
personid InvoiceCountByPerson
--------------------------------
1 3
2 2
3 5
I need to get number of invoices by main office name. Person1 whose main office is office1 created 3 invoices, Person2 whose main office is office2 created 2 invoices, and Person3 whose main office is office3 created 5 invoices so expected result:
officename InvoiceCountByOfficeName
------------------------------------
office1 3
office2 2
office3 5
This doesn't work:
select
o.officename,
count(*) InvoiceCountByOfficeName
from
@invoice i
inner join
@person p on p.personid = i.personid
inner join
@personoffice po on po.personid = p.personid AND po.mainoffice = 1
inner join
@office o on o.officeid = po.officeid
group by
o.officename
as it returns:
officename InvoiceCountByOfficeName
-------------------------------------
office1 6
office2 4
office3 5
As the same person has multiple mainoffice = 1 records with different roles, I need to have some sort of distinct on the @personoffice join. Millions of invoices too so need to take performance into consideration.
You are so close... All you had to do is use a derived table instead of using the @personoffice table directly:
select
o.officename,
count(*) InvoiceCountByOfficeName
from
@invoice i
inner join
@person p on p.personid = i.personid
inner join
(
select distinct personid, officeid
from @personoffice
where mainoffice = 1
)
po on po.personid = p.personid
inner join
@office o on o.officeid = po.officeid
group by
o.officename
Results:
officename InvoiceCountByOfficeName
---------- ------------------------
office1 3
office2 2
office3 5
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments