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):
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:
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