Desenvolvi um sistema de registro de horas, que permite aos usuários registrar:
- A Período de tempo (9:00 às 12:00)
- O que eles fizeram durante esse tempo (DutyActivity: 1 hora de redação de relatório, 2 horas de treinamento)
Quando eles enviam uma planilha de horas, o sistema também registra seu GeographyId, RoleId, CategoryId (ids únicos) e AttributeIds (ids múltiplos) - que é um instantâneo do usuário naquele momento.
Essas informações de quadro de horários registradas podem ser consultadas para relatórios. Em sua forma mais simples, o sistema retornará uma lista de UserIds com uma SOMA de PeriodMinutespara cada arquivo DutyActivityId. Que se pareceria com o seguinte:
Porém o que eu também quero fazer é agrupar por e retornar o registrado AttributeIdsgravado com cada timesheet (na TimesheetAttributetabela). No entanto, como há vários AttributeIdsregistrados para cada quadro de horários, é um pouco mais complexo.
Minha tentativa de fazer isso cria o seguinte relatório:
No entanto, o problema com isso é que o SUM(PeriodMinutes)para cada DutyCategoryIdretornado agora é muito alto. Parece que meu SQL para incluir AttributeIdspara cada quadro de horários está fazendo com que o SUMcálculo seja feito incorretamente.
Eu criei um ZIP contendo SQL para o esquema de banco de dados mais dados, junto com o SQL para ambas as consultas que eu capturei aqui: Banco de dados de exemplo de quadro de horários ZIP
As informações abaixo estão incluídas no ZIP, mas estou linkando-as aqui para facilitar o acesso:
Relatório em planilhas de horas, sem agrupamento de atributos
SELECT
*
FROM
(
SELECT
T.UserId,
T.RoleId,T.GeographyId,T.CategoryId,
TDA.DutyActivityId AS TypeId,
SUM(TDA.PeriodMinutes) AS Total
FROM
Timesheet AS T
LEFT JOIN
TimesheetDutyActivity AS TDA ON
TDA.TimesheetId = T.TimesheetId
GROUP BY
T.UserId,
T.RoleId,T.GeographyId,T.CategoryId,
TDA.DutyActivityId
) AS SourceTable
/* PIVOT against the known DutyActivityIds */
PIVOT
(
SUM(Total)
FOR TypeId IN ([1],[2],[3],[4],[5])
)
AS PivotType
ORDER BY UserId ASC
Relatório em planilhas de horas, com tentativa de agrupamento de atributos
SELECT
*
FROM
(
SELECT
T.UserId,
T.RoleId,T.GeographyId,T.CategoryId,
TA.AttributeId * -1 AS AttributeId, /* Multiply AttributeId by -1 in order to create negative Ids, so that two PIVOT operations can be used */
TDA.DutyActivityId AS TypeId,
SUM(TDA.PeriodMinutes) AS Total
FROM
Timesheet AS T
LEFT JOIN
TimesheetAttribute AS TA ON
TA.TimesheetId = T.TimesheetId
LEFT JOIN
TimesheetDutyActivity AS TDA ON
TDA.TimesheetId = T.TimesheetId
GROUP BY
T.UserId,
AttributeId,
T.RoleId,T.GeographyId,T.CategoryId,
TDA.DutyActivityId
) AS SourceTable
/* PIVOT against the known DutyActivityIds */
PIVOT
(
SUM(Total)
FOR TypeId IN ([1],[2],[3],[4],[5])
)
AS PivotType
/* Also PIVOT against the known AttributeIds */
PIVOT
(
SUM(AttributeId)
FOR AttributeId IN ([-1],[-2],[-3],[-4],[-5])
)
AS PivotAttribute
ORDER BY UserId ASC