Início / dba / Perguntas / 6668
Accepted
Peter Bridger
Asked: 2011-10-11 06:54:37 +0800 CST

Usando vários PIVOTs JOINs e GROUP BYs para geração avançada de relatórios

  • 772

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:

Resultados simples

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:

Resultados complexos

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:

Diagrama de banco de dados

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
join pivot

1 respostas

  1. Best Answer

    Consegui obter o resultado necessário descartando o segundo AttributeIdargumento PIVOT e GROUP BY e, em vez disso, usando um LEFT JOIN nos atributos.

    Esta resposta foi fornecida por visahk16 nos fóruns do SQL Team :

    SELECT
    *
    FROM
    (
    SELECT
    T.UserId,
    T.RoleId,T.GeographyId,T.CategoryId,
    TA.[-1],TA.[-2],TA.[-3],TA.[-4],TA.[-5],
    TDA.DutyActivityId AS TypeId,
    SUM(TDA.PeriodMinutes) AS Total

    FROM 
    Timesheet AS T

    LEFT JOIN 
    (SELECT TimesheetId,
     SUM(CASE WHEN AttributeId ='1' THEN -1 ELSE 0 END) AS [-1],
     SUM(CASE WHEN AttributeId ='2' THEN -1 ELSE 0 END) AS [-2],
...
     SUM(CASE WHEN AttributeId ='5' THEN -1 ELSE 0 END) AS [-5] 
     FROM TimesheetAttribute 
     GROUP BY TimesheetId 
     )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

    ORDER BY UserId ASC
    • 1

relate perguntas