ALTER TABLE [Timesheet].[Assigned Developers] ADD [Bill Rate Override] decimal(10,4);
GO
ALTER TABLE [Timesheet].[Bills] ADD [Amount] decimal(10,4) not null;
GO
ALTER TABLE [Timesheet].[Billed Work] ADD [Hours] decimal(10,4) not null;
GO
ALTER TABLE [Timesheet].[Billed Work] ADD [Bill Rate] decimal(10,4) not null;
GO
ALTER TABLE [Timesheet].[Billed Work] ADD [Bill Amount] AS ([Hours] * [Bill Rate]);
GO
create or alter procedure [Timesheet].[Bill Client]
@Client int,
@Bill int out,
@BillAmount decimal(10,4) out
as
begin
	set nocount on;

	begin transaction;

	begin try
		insert into [Timesheet].[Bills]([Client], [Date], [Amount])
		values (@Client, getutcdate(), 0.0);

		select @Bill = scope_identity();

		insert into [Timesheet].[Billed Work]([Client], [Project], [Developer], [Date], [Bill], [Hours], [Bill Rate])
		select	w.Client, w.Project, w.Developer, w.[Date], @Bill, w.[Hours], isnull(ad.[Bill Rate Override], d.[Bill Rate])
		from	[Timesheet].Work w inner join
				[Timesheet].Developers d on w.Developer = d.Id inner join
				[Timesheet].[Assigned Developers] ad on w.Client = ad.Client and w.Project = ad.Project and w.Developer = ad.Developer 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 = @Client and bw.Bill is null;

		select @BillAmount = sum(bw.[Bill Amount])
		from	[Timesheet].[Billed Work] bw
		where	bw.Client = @Client and bw.Bill = @Bill;

		update [Timesheet].Bills
		set [Amount] = @BillAmount
		where [Client] = @Client and [Id] = @Bill;

		commit transaction;
	end try
	begin catch
		rollback transaction;
		throw;
	end catch
end
GO
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 = c.Id
			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