mercredi 25 novembre 2015

Pivoting a table with results diveded by USERNAME

I'll try to make it as simple as I can:

I have a table that registers whatever a USER (employee) is doing (it can be starting his job, registering new products, delivering products and ending his job). The most common scenario is when the user starts his job, do whatever he needs to do during the day, and then ends his job on the same day he started, so these are the info that matter to me now (start and end).

This query:

SELECT DISTINCT
DATEPART(DAY,TimeStamp) [Day],
DATEPART(MONTH,TimeStamp) [Month],
DATEPART(YEAR,TimeStamp) [Year],
login [USER],
CASE WHEN TipoOcorrencia = 'IniciarDia'     THEN 'YES' END [Started],
CASE WHEN TipoOcorrencia = 'FinalizacaoDia' THEN 'YES' END [Ended]
FROM CHAMADO ch
WHERE
    TimeStamp >= '20151001'
    AND TipoOcorrencia = 'IniciarDia' OR TipoOcorrencia = 'FinalizacaoDia'
GROUP BY
    DATEPART(DAY,TimeStamp),
    DATEPART(MONTH,TimeStamp),
    DATEPART(YEAR,TimeStamp),
    login,
    TipoOcorrencia
ORDER BY [USER], [Year],[Month],[Day]

returns something like this (but with more users):

results without pivot

You can see that this user has Started and Ended his job day on the same date in three different days (10/7, 10/8 and 10/9).

By correctly using PIVOT I believe that is possible to obtain the following expected result: expected result with pivot

Obviously my problem is that I, despite trying really hard, could not make PIVOT work. And I need help...

Thanks in advance!

Aucun commentaire:

Enregistrer un commentaire