Skip to content

Instantly share code, notes, and snippets.

@Grinderofl
Last active October 19, 2019 19:35
Show Gist options
  • Save Grinderofl/a8d09afd9b1ba96c4f1dae0e9a1c672f to your computer and use it in GitHub Desktop.
Save Grinderofl/a8d09afd9b1ba96c4f1dae0e9a1c672f to your computer and use it in GitHub Desktop.
Dashboard Summary Query
-- 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
@Grinderofl
Copy link
Author

tickettool

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment