Dashboard Summary Query

project-summary-query.sql

-- Outermost query for the final projection of the calculated values
SELECT
	ProjectId, -- Primary key of the project
	ProjectName, -- Name of the project
	ProjectArchived, -- Whether the project is archived
	ProjectQuotedDayRate, -- Daily rate quoted for the client
	ProjectBaseDayRate, -- Estimated day rate for the project (cost to the company)
	ProjectTotalBudget, -- Total budget (amount billed to the client) of the project
	CurrencyCode, -- Currency code (presently only 'GDP')
	ClientId, -- Primary key of the client
	ClientName, -- Name of the client
	SpaceName, -- Name of the project space
	SpaceSlug, -- Slugified name of the project space for URL purposes
	IncomeCategory, -- Income category of the ticket, currently not used for anything
	StartDateUtc, -- Starting date of the project (first day an event was recorded on a ticket)
	HoursRecorded, -- Sum of the hours recorded on the project
	HoursRequested, -- Sum of the ours requested to be worked on the project
	Cost, -- Cost of the project to the company
	(SELECT CASE WHEN ProjectArchived = 1 THEN 2 WHEN HasOpenTickets = 0 AND Tickets = 0 THEN 0 ELSE 1 END) as Status, -- Status of the project (0 is active, 1 is completed)
	HasOpenTickets -- Whether there are any open tickets left on the project
FROM
(
	SELECT
		ProjectId,
		ProjectName,
		ProjectArchived,
		ProjectQuotedDayRate,
		ProjectBaseDayRate,
		ProjectTotalBudget,
		CurrencyCode,
		ClientId,
		ClientName,
		SpaceName,
		SpaceSlug,
		IncomeCategory,
		MIN(StartDate) AS StartDateUtc,
			
		ISNULL(SUM(HoursRecorded), 0) as HoursRecorded,
		ISNULL(SUM(HoursRequested), 0) As HoursRequested,
		SUM(ISNULL(DayRate, 0) / 7 * ISNULL(HoursRecorded, 0)) as Cost,
		SUM(TicketId) as Tickets,
		CONVERT(BIT, MAX(CONVERT(INT, ISNULL(IsOpen, 0)))) AS HasOpenTickets
	FROM
	(
		SELECT
			[project].[Id] AS ProjectId,
			[project].[Name] AS ProjectName,
			[project].[Archived] AS ProjectArchived,
			[project].[QuotedDayRate] AS ProjectQuotedDayRate,
			[project].[BaseDayRate] AS ProjectBaseDayRate,
			[project].[TotalBudget] AS ProjectTotalBudget,
			[currency].[Iso3Code] AS CurrencyCode,
	        	[client].Id AS ClientId,
			[client].Name AS ClientName,
			[ic].Name AS IncomeCategory,
	
			[event].SpaceName,
			[event].SpaceSlug,
			[event].[TicketId] AS TicketId,
	        	[event].[TicketSummary] AS TicketSummary,
			[event].[EventDateUtc] AS StartDate,
			[event].[CreatedById] AS EventAuthor,
			[event].[HoursRecorded],
			[event].[HoursRequested],
			[event].[IsOpen],
			ISNULL([event].UserDayRate, (ISNULL([event].RoleDayRate, [project].BaseDayRate))) as DayRate
	
		FROM Projects AS[project] -- [Projects] table has the project for the currently worked on ticket
		LEFT OUTER JOIN Currencies AS [currency] ON [currency].[Id] = [project].[CurrencyId] -- [Currencies] denotes the currency of the daily and hourly rates
		LEFT OUTER JOIN IncomeCategories AS [ic] ON [ic].[Id] = [project].[IncomeCategoryId] -- [IncomeCategories] is not really used at this point 
		LEFT OUTER JOIN Clients AS [client] ON [client].[Id] = [project].[ClientId] -- [Clients] is for the customer the project is for
		LEFT OUTER JOIN
			(
				-- Core Query:
				-- [Event] table is used to persist changes made to tickets
				-- [TicketAggregate] table is for the domain entity for the tickets
				-- [Status] stores the state (new, accepted, fixed, invalid, or incomplete)
				-- [Space] is the category for clients, projects, and tickets of the ticket tool customer company
				
				SELECT
					[event].TicketAggregateId, -- Foreign key reference to the Ticket as globally unique identifier
					[event].EventDateUtc, -- The day (+ hour, minute, and second) the time was spent as date and time in UTC timezone for rate calculation 
					[event].CreatedById, -- Foreign key reference to the person who performed the event as 64-bit integer
					ISNULL([event].TimeWorkedChanged_NewDecimal, 0) AS HoursRecorded, -- Time added or subtracted from the time worked on ticket
				
					[ticket].ProjectId, -- Foreign key reference to the project as 64-bit integer
					[ticket].Number AS TicketId, -- Human-readable ticket number as 64-bit integer
	                		[ticket].Summary AS TicketSummary, -- Title of the ticket as string
					[ticket].TimeRequestedInHours AS HoursRequested, -- Hours requested to be worked on the ticket
									
					[status].IsOpen, -- Whether the ticket is open or closed
	
					[s].[Name] as SpaceName, -- Space name for display purposes
					[s].[Slug] as SpaceSlug, -- Slugified space name for URL purposes
	
				   -- First subquery to join the event on
				   -- Finds the hourly rate for different departments for the day the event occurred, in case the person has no day rate specified
				   (SELECT
					TOP 1 [rolerate].RatePerHour -- Return only one record of the rate per hour ...
					FROM ApplicationUsers [au] -- for the person's ...
					LEFT JOIN Roles [role] ON [role].Id = [au].DepartmentId -- ... assigned department's ...
					LEFT JOIN DepartmentRates [rolerate] ON [rolerate].DepartmentId = [role].Id -- ... set role rate ...
					WHERE [au].Id = [event].CreatedById -- ... whose user ID matches the event author's ID ...
					ORDER BY [rolerate].ValidFromUtc DESC -- ... in descending order of the rate's starting date
				   ) AS RoleDayRate, 
				   
				   -- Second subquery to join the event on
				   -- Finds the person's daily rate for the day the event occurred, in case their rate changes through the middle of the project
				   (SELECT
				   	TOP 1 [aur].DayRate -- Return only one record of the daily rate ...
					FROM ApplicationUserRate [aur] -- ... for the person's ...
					WHERE [aur].ApplicationUserId = [event].CreatedById -- ... whose user ID matches the event author's ID ... 
					ORDER BY [aur].ValidFromUtc DESC -- ... in descending order of the rate's starting date
				   ) AS UserDayRate
				FROM Events [event]
				LEFT OUTER JOIN Tickets [ticket] ON [ticket].[Id] = [event].[TicketAggregateId]
				LEFT OUTER JOIN Spaces [s] ON [s].[Id] = [ticket].[SpaceId]
				LEFT OUTER JOIN Statuses [status] ON [ticket].[StatusId] = [status].[Id]
				WHERE[event].Discriminator = 'TimeWorkedChanged'
				
			) AS [event] ON [event].ProjectId = [project].[Id]
	) as result
	GROUP BY
		ProjectId,
		ProjectName,
		ProjectArchived,
		ProjectQuotedDayRate,
		ProjectBaseDayRate,
		ProjectTotalBudget,
		CurrencyCode,
		ClientId,
		ClientName,
		IncomeCategory,
		SpaceName,
		SpaceSlug
) AS summary