CODEWITHSUNDEEP
sql-server
ChangeWorld
ChangeWorld

Reputation: 431

Substract time based on 2 parameters

I have a table in SQL Server

Table Persona

ID   Name   Date        Timer    InOrOut
----------------------------------------
1    Seth   2020-05-26  07:00   I
2    Seth   2020-05-26  17:00   O
3    Maria  2020-05-25  06:59   I
4    Maria  2020-05-25  07:00   I
5    Maria  2020-05-25  16:49   O
6    Maria  2020-05-25  17:01   O

This is my query

SELECT Name As Name, Date AS Data,
case when InOrOut = 'I' then CONVERT(VARCHAR(5), CAST(Timer AS TIME), 108) end as In, 
case when InOrOut= 'U' then CONVERT(VARCHAR(5), CAST(Timer AS TIME), 108) end as Out
FROM Persona 
GROUP BY Date, Name, InOrOut, Timer ORDER BY Date DESC

And this is the output of the above query

Name   Date          In      Out
----------------------------------------
Seth   2020-05-26    07:00   NULL
Seth   2020-05-26    NULL    17:00
Maria  2020-05-25    06:59   NULL
Maria  2020-05-25    07:00   NULL
Maria  2020-05-25    NULL    16:49
Maria  2020-05-25    NULL    17:01

What I want to ask:

  1. Is there a way to add another column named TotalHours where I substract Out - In? So like in Seth example would be 17:00 - 07:00 giving the result 10?

Optional (I don't really care about the 2 point, but if someone could give a complete example that would be more than great)

  1. Is there a way to display only one value for Persona? So like in the example Maria In 6:59 (taking the lowest value) and Maria Out 17:01 (taking the highest value) per day?

Any help is appreciated.

Upvotes: 0

Views: 54

Answers (2)

MattM
MattM

Reputation: 384

This statement will give the same results as well...

WITH cte_DistinctNameDate AS
(
    SELECT      DISTINCT [Name], [Date]
    FROM        Persona
)
SELECT      d.[Name]
            , d.[Date]
            , CONVERT ( VARCHAR(5), MIN ( p.[Timer] ), 108 ) AS [In]
            , CONVERT ( VARCHAR(5), MAX ( p.[Timer] ), 108 ) AS [Out]
            , CAST ( DATEDIFF ( MI, MIN ( p.[Timer] ), MAX ( p.[Timer] ) ) / 60.0 AS DECIMAL (4,2) ) AS [TotalHours]
FROM        cte_DistinctNameDate AS d
INNER JOIN  Persona AS p
    ON      d.[Name] = p.[Name]
    AND     d.[Date] = p.[Date]
GROUP BY    d.[Name], d.[Date]
ORDER BY    d.[Date] DESC ;

But I think @Zhorov's is more efficient.

Or to refine it even further...

SELECT      [Name] AS [Name]
            , [Date] AS [Data]
            , CONVERT ( VARCHAR(5), MIN ( [Timer] ), 108 ) AS [In]
            , CONVERT ( VARCHAR(5), MAX ( [Timer] ), 108 ) AS [Out]
            , CAST ( DATEDIFF ( MI, MIN ( [Timer] ), MAX ( [Timer] ) ) / 60.0 AS DECIMAL (4,2) ) AS [TotalHours]
FROM        Persona
GROUP BY    [Name], [Date]
ORDER BY    [Date] DESC ;

Upvotes: 1

Zhorov
Zhorov

Reputation: 29943

A possible statement, which returns the expected results:

Table:

CREATE TABLE Persona (
   ID int,
   Name varchar(50),
   [Date] date,
   Timer time,
   InOrOut varchar(1)
)
INSERT INTO Persona (ID, Name, [Date], Timer, InOrOut)
VALUES
   (1, 'Seth',  '20200526', '07:00', 'I'),
   (2, 'Seth',  '20200526', '17:00', 'O'),
   (3, 'Maria', '20200525', '06:59', 'I'),
   (4, 'Maria', '20200525', '07:00', 'I'),
   (5, 'Maria', '20200525', '16:49', 'O'),
   (6, 'Maria', '20200525', '17:01', 'O')

Statement: 

SELECT 
   Name As Name, 
   Date AS Data,
   MIN(case when InOrOut = 'I' then CONVERT(VARCHAR(5), Timer, 108) end) AS [In], 
   MAX(case when InOrOut = 'O' then CONVERT(VARCHAR(5), Timer, 108) end) AS [Out],
   DATEDIFF(hour,
      MIN(case when InOrOut = 'I' then Timer end),
      MAX(case when InOrOut = 'O' then Timer end) 
   ) AS TotalHours
FROM Persona 
GROUP BY [Date], Name
ORDER BY [Date] DESC

Result:

Name    Data        In      Out     TotalHours
Seth    2020-05-26  07:00   17:00   10
Maria   2020-05-25  06:59   17:01   11

Upvotes: 1

Related Questions