Modify SQL query to obtain expected results

Question

Modify SQL query to obtain expected results

Navigation

#1 by (2 votes)
#2 by (-1 votes)

1

Background

I am generating a series of reports based on information contained in a database in SQL Server.

For purposes of the question, I will call this database: Test .

From the Test database, I get the time at which certain users have used a device Example: PC .

The date values are in UNIX time format and, thanks to this answer I could adapt in this question to improve the understanding of the data.

Disadvantage

When executing the following query that has a date range:

09/05/2016 00:00:00 - 05/09/2016 23:59:59

-- Variables globales.
DECLARE @dt DATETIME = '1970-01-01' -- epoch start

SELECT ID, 
       (CASE Lector
        WHEN 539560940 THEN FORMAT(DATEADD(SECOND, Fecha, @dt),'HH:mm')
        ELSE '0' END) AS 'Hora entrada',
       (CASE Lector
        WHEN 539560944 THEN FORMAT(DATEADD(SECOND, Fecha, @dt),'HH:mm')
        ELSE '0' END) AS 'Hora salida',        
       (CASE Lector
        WHEN 539560940 THEN 'Entrada'
        WHEN 539560944 THEN 'Salida'
        ELSE '0' END) AS Lector,
       Evento,
       UsuarioID
FROM LogEventos
WHERE UsuarioID = 52698067
AND Fecha >= 1473033600 AND Fecha <= 1473119999
AND Evento = 55
ORDER BY Fecha

I get the columns Time inbound and Time out in different lines:

---------------------------------------------
|   Hora de entrada     |   Hora de salida  |
---------------------------------------------
|       07:54           |          0        |
---------------------------------------------
|         0             |       09:01       |
---------------------------------------------
|       09:04           |          0        |
---------------------------------------------
|         0             |       10:21       |
---------------------------------------------
|       10:25           |          0        |
---------------------------------------------
|          0            |       12:27       |
---------------------------------------------
|       15:00           |          0        |
---------------------------------------------
|          0            |       16:28       |
---------------------------------------------
|       16:33           |          0        |
---------------------------------------------
|          0            |       18:51       |
---------------------------------------------

Click on the image to enlarge it

When what I want to obtain are the following results:

---------------------------------------------
|   Hora de entrada     |   Hora de salida  |
---------------------------------------------
|       07:54           |       09:01       |
---------------------------------------------
|       09:04           |       10:21       |
---------------------------------------------
|       10:25           |       12:27       |
---------------------------------------------
|       15:00           |       16:28       |
---------------------------------------------
|       16:33           |       18:51       |
---------------------------------------------

How can I modify the query to get the expected values?

I generated in the following sqlfiddle a structure with sample data, which correspond to the database.

sql sql-server

asked by Mauricio Arias Olave 29.09.2016 в 19:08

source

2 answers

-1

Resolve it with a join, separate the queries to get the time of entry and time of departure.

It should be very simple, you already have the data.

answered by 30.09.2016 в 01:27

redirect when downloading pdf in FPDF output Order and ways to group elements in linked lists

score 2 · Accepted Answer

Ok, what you need to do is a JOIN between the subset of rows representing the entries, with the subset of rows representing the outputs. In addition, you must have a column that identifies the chronological order of each of these rows to be able to do JOIN (this according to your comment, ideally there would be a direct way to relate the inputs to the outputs).

You can try this code:

DECLARE @dt DATETIME = '19700101';

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY UsuarioID, Evento
                                   ORDER BY Fecha)
    FROM LogEventos
)
SELECT  Ent.ID,
        FORMAT(DATEADD(SECOND, Ent.Fecha, @dt),'HH:mm') [Hora entrada],
        FORMAT(DATEADD(SECOND, Sal.Fecha, @dt),'HH:mm') [Hora salida],
        Ent.Evento,
        Ent.UsuarioID

FROM (  SELECT *
        FROM CTE
        WHERE Lector = 539560940) Ent
LEFT JOIN ( SELECT *
            FROM CTE
            WHERE Lector = 539560944) Sal
    ON Ent.UsuarioID = Sal.UsuarioID
    AND Ent.Evento = Sal.Evento
    AND Ent.RN = Sal.RN - 1;