┌─────────────┐ ┌─────────────┐ ┌─────────────┐1..n 0..1┌───────────────┐ ┌───────────────┐
│ Fund Source ├────┤ Fact ├────┤ Project ├───────────┤ Project Group ├────┤ Group List │
└─────────────┘ └───────┬─────┘ └─────────────┘ └───────────────┘ └───────────────┘
│
│
┌───────┴──────┐
│ Project List │
└──────────────┘
select coalesce(pg.GroupName, p.ProjectName) as Project
, fs.FundSourceDescription as 'Fund Source'
, sum(fact.Dollars) as Dollars
from Fact fact
inner join Project p on p.ProjectId = fact.ProjectId
inner join ProjectList pl on p.ProjectId = pl.ProjectId
inner join FundSource fs on fs.FundSourceId = fact.FundSourceId
left outer join ProjectGroup pg on pg.ProjectId = p.ProjectId
inner join GroupList gl on gl.ProjectGroupId = pg.ProjectGroupId
and gl.GroupListName = 'MyGroupList'
where pl.ListName = 'My Project List'
group by coalesce(pg.GroupName, p.ProjectName)
, fs.FundSourceDescription
Project | Fund Source | Dollars |
Group A | A | 6,000,000 |
Group A | B | 3,000,000 |
Group B | A | 400,000 |
Group B | C | 2,500,000 |
Solo Project A | B | 120,000 |
Solo Project B | B | 70,000 |
Solo Project B | C | 35,000 |
select coalesce(pg.GroupName, p.ProjectName) as Project
, fs.FundSourceDescription as 'Fund Source'
, sum(fact.Dollars) as Dollars
from Fact fact
inner join Project p on p.ProjectId = fact.ProjectId
inner join ProjectList pl on p.ProjectId = pl.ProjectId
inner join FundSource fs on fs.FundSourceId = fact.FundSourceId
left outer join ProjectGroup pg on pg.ProjectId = p.ProjectId
inner join GroupList gl on gl.ProjectGroupId = pg.ProjectGroupId
where pl.ListName = 'My Project List'
and gl.GroupListName = 'MyGroupList'
group by coalesce(pg.GroupName, p.ProjectName)
, fs.FundSourceDescription
Project | Fund Source | Dollars |
Group A | A | 6,000,000 |
Group A | B | 3,000,000 |
Group B | A | 400,000 |
Group B | C | 2,500,000 |
where pl.ListName = 'My Project List'
and (gl.GroupListName = 'MyGroupList' or gl.GroupListName is null)
select coalesce(pg.GroupName, p.ProjectName) as Project
, fs.FundSourceDescription as 'Fund Source'
, sum(fact.Dollars) as Dollars
from Fact fact
inner join Project p on p.ProjectId = fact.ProjectId
inner join ProjectList pl on p.ProjectId = pl.ProjectId
inner join FundSource fs on fs.FundSourceId = fact.FundSourceId
left outer join (
ProjectGroup pg on pg.ProjectId = p.ProjectId
inner join (
select ProjectGroupId
, GroupListName
from GroupList grouplist
) gl on gl.ProjectGroupId = pg.ProjectGroupId
where pl.ListName = 'My Project List'
and gl.GroupListName = 'MyGroupList'
group by coalesce(pg.GroupName, p.ProjectName)
, fs.FundSourceDescription
select coalesce(pg.GroupName, p.ProjectName) as Project
, fs.FundSourceDescription as 'Fund Source'
, sum(fact.Dollars) as Dollars
from Fact fact
inner join Project p on p.ProjectId = fact.ProjectId
inner join ProjectList pl on p.ProjectId = pl.ProjectId
inner join FundSource fs on fs.FundSourceId = fact.FundSourceId
left outer join (
ProjectGroup pg on pg.ProjectId = p.ProjectId
inner join (
select ProjectGroupId
, coalesce(GroupListName, 'Solo Projects') as GroupListName
from GroupList grouplist
) gl on gl.ProjectGroupId = pg.ProjectGroupId
where pl.ListName = 'My Project List'
and gl.GroupListName = 'MyGroupList'
group by coalesce(pg.GroupName, p.ProjectName)
, fs.FundSourceDescription
select coalesce(pg.GroupName, p.ProjectName) as Project
, coalesce(gl.GroupListName , 'Solo Project') as GroupListName
, fs.FundSourceDescription as 'Fund Source'
, sum(fact.Dollars) as Dollars
from Fact fact
inner join Project p on p.ProjectId = fact.ProjectId
inner join ProjectList pl on p.ProjectId = pl.ProjectId
inner join FundSource fs on fs.FundSourceId = fact.FundSourceId
left outer join (
ProjectGroup pg on pg.ProjectId = p.ProjectId
inner join (
select ProjectGroupId
, GroupListName
from GroupList grouplist
) gl on gl.ProjectGroupId = pg.ProjectGroupId
where pl.ListName = 'My Project List'
and coalesce(gl.GroupListName , 'Solo Project') in ( 'MyGroupList', 'Solo Project')
group by coalesce(pg.GroupName, p.ProjectName)
, coalesce(gl.GroupListName , 'Solo Project')
, fs.FundSourceDescription