create or alter view [Timesheet].[Clients Summary]
as
select	c.Id, c.[Name], 
		(
			select	count(distinct  ad.Developer)
			from	[Timesheet].[Assigned Developers] ad
			where	ad.[Client] = c.Id
		) [Number of Assigned Developers],
		(
			select	b.[Date]
			from	[Timesheet].[Bills] b
			where	b.Client = b.Client
			order by b.[Date] desc offset 0 rows fetch first 1 rows only
		) [Last Billed],
		(
			select	sum(w.[Hours]) [Number of Unbilled Hours]
			from	[Timesheet].[Work] w left outer join
					[Timesheet].[Billed Work] bw on 
						w.[Client] = bw.[Client] and w.[Project] = bw.[Project] and	w.[Developer] = bw.[Developer] and w.[Date] = bw.[Date]
			where	w.[Client] = c.Id and bw.Bill is null
		) [Number of Unbilled Hours]
from	[Timesheet].Clients c
go
create or alter view [Timesheet].[Developers Summary]
as
select	d.Id, d.[Name], d.[Bill Rate],
		(
			select	count(*)
			from	[Timesheet].[Assigned Developers] ad
			where	ad.Developer = d.Id
		) [Number of Assigned Projects],
		isnull((
			select	sum(w.[Hours])
			from	[Timesheet].[Work] w left outer join
					[Timesheet].[Billed Work] bw on 
						w.[Client] = bw.[Client] and w.[Project] = bw.[Project] and	w.[Developer] = bw.[Developer] and	w.[Date] = bw.[Date]
			where	bw.Bill is null and
					w.[Developer] = d.Id
		),0.0)	[Total Unbilled Hours]
from	[Timesheet].[Developers] d
go
create or alter view [Timesheet].[Projects Summary]
as
select	p.Client, p.Id, p.[Name],
		isnull((
			select	count(*)
			from	[Timesheet].[Assigned Developers] ad
			where	ad.[Client] = p.Client and ad.[Project] = p.Id
		),0)	[Number of Assigned Developers],
		isnull((
			select	sum(w.[Hours])
			from	[Timesheet].[Work] w
			where	w.[Client] = p.Client and w.[Project] = p.Id
		),0.0)	[Total Hours Worked],
		(
			select	sum(w.[Hours])
			from	[Timesheet].[Work] w left outer join
					[Timesheet].[Billed Work] bw on 
						w.[Client] = bw.[Client] and w.[Project] = bw.[Project] and	w.[Developer] = bw.[Developer] and w.[Date] = bw.[Date]
			where	w.[Client] = p.Client and w.Project = p.Id and bw.Bill is null
		) [Number of Unbilled Hours]
from [Timesheet].[Projects] p
go
create or alter view [Timesheet].[Assigned Developers Summary]
as
select	ad.[Client], ad.[Project], ad.[Developer], 
		isnull((
			select	sum(w.[Hours])
			from	[Timesheet].[Work] w
			where	w.[Client] = ad.[Client] and w.[Project] = ad.[Project] and w.[Developer] = ad.Developer
		),0.0)	[Total Hours Worked],
		isnull((
			select	sum(w.[Hours])
			from	[Timesheet].[Work] w left outer join
					[Timesheet].[Billed Work] bw on 
						w.[Client] = bw.[Client] and w.[Project] = bw.[Project] and	w.[Developer] = bw.[Developer] and w.[Date] = bw.[Date]
			where	bw.Bill is null and
					w.[Client] = ad.[Client] and w.[Project] = ad.[Project] and w.[Developer] = ad.Developer
		),0.0)	[Total Unbilled Hours]
from	[Timesheet].[Assigned Developers] ad
go
create or alter view [Timesheet].[Unbilled Work]
as
select	w.[Client], w.[Project], w.[Developer], w.[Date], w.[Hours]
from	[Timesheet].[Work] w 
where	not exists (
			select	1
			from	[Timesheet].[Billed Work] bw
			where	w.[Client] = bw.[Client] and
					w.[Project] = bw.[Project] and
					w.[Developer] = bw.[Developer] and
					w.[Date] = bw.[Date]
		);
go