Time differences between different intervals

Question

Time differences between different intervals

ram.vaddiparthi

Grasshopper

Points: 13
More actions
April 30, 2015 at 3:03 pm

#296545

Hi There,
I am using google form which will help me to capture the login,break and other off work related activities. Now, the challenge is how can I get the time differences between them.
Let's say , a person who click on break @ 12:00 PM and he is back and select I am back option @ 12:15 the total break time is 15 minutes. However, can I display this difference of break time.
Attached sample file

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004696 More actions · Answer 1

ram.vaddiparthi (4/30/2015)
Hi There,
I am using google form which will help me to capture the login,break and other off work related activities. Now, the challenge is how can I get the time differences between them.
Let's say , a person who click on break @ 12:00 PM and he is back and select I am back option @ 12:15 the total break time is 15 minutes. However, can I display this difference of break time.
Attached sample file

Is there no standard column to identify the "INs" from the "OUTs"?

Also, how do you want to handle missing punches and spans through midnight?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182951 More actions · Answer 2

Simple solution

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.TBL_SAMPLE_STATUS') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_STATUS;

CREATE TABLE dbo.TBL_SAMPLE_STATUS

(

Timestamp DATETIME NOT NULL CONSTRAINT DFLT_DBO_TBL_SAMPLE_STATUS_TIMESTAMP DEFAULT(GETDATE())

,Username VARCHAR(50) NOT NULL

,Your_status_please VARCHAR(64) NOT NULL

);

INSERT INTO dbo.TBL_SAMPLE_STATUS (Timestamp,Username,Your_status_please)

VALUES

('3/6/2015 22:05:57','C100344','Login' )

,('3/6/2015 22:06:06','C100344','Short Break' )

,('3/6/2015 22:06:15','C100344','I''m Back :D' )

,('3/6/2015 22:17:55','C100344','Lunch' )

,('3/6/2015 23:10:34','C100344','I''m Back :D' )

,('3/6/2015 23:22:42','C23456' ,'Login' )

,('3/6/2015 0:10:22' ,'C23456' ,'Team-Meetings')

,('3/6/2015 0:31:59' ,'C23456' ,'I''m Back :D' );

;WITH BASE_DATA AS

(

SELECT

SS.Timestamp

,ROW_NUMBER() OVER

(

PARTITION BY SS.Username

ORDER BY SS.Timestamp ASC

) AS SSU_RID

,SS.Username

,SS.Your_status_please

FROM dbo.TBL_SAMPLE_STATUS SS

)

SELECT

BD.Username AS USERNAME

,BD.Your_status_please AS ACTIVITY

,BD.Timestamp AS START_TIME

,B2.Timestamp AS END_TIME

,DATEDIFF(MINUTE,BD.Timestamp,B2.Timestamp) AS DURATION

FROM BASE_DATA BD

LEFT OUTER JOIN BASE_DATA B2

ON BD.Username = B2.Username

AND BD.SSU_RID = B2.SSU_RID - 1;

Results

USERNAME ACTIVITY START_TIME END_TIME DURATION

---------- --------------- ----------------------- ----------------------- -----------

C100344 Login 2015-03-06 22:05:57.000 2015-03-06 22:06:06.000 1

C100344 Short Break 2015-03-06 22:06:06.000 2015-03-06 22:06:15.000 0

C100344 I'm Back :D 2015-03-06 22:06:15.000 2015-03-06 22:17:55.000 11

C100344 Lunch 2015-03-06 22:17:55.000 2015-03-06 23:10:34.000 53

C100344 I'm Back :D 2015-03-06 23:10:34.000 NULL NULL

C23456 Team-Meetings 2015-03-06 00:10:22.000 2015-03-06 00:31:59.000 21

C23456 I'm Back :D 2015-03-06 00:31:59.000 2015-03-06 23:22:42.000 1371

C23456 Login 2015-03-06 23:22:42.000 NULL NULL

Jeff Moden SSC Guru Points: 1004696 More actions · Answer 3

Heh... I cringe every time I see something like this where the human isn't compelled to take an action such a log out. The simple solution above is great but I sure wouldn't use it for something like payroll time keeping.

Also, if someone tried to enforce something like this on me, especially since I'm salaried and work a fair bit of overtime, I'd be looking for a new job.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182951 More actions · Answer 4

Jeff Moden (5/1/2015)
Heh... I cringe every time I see something like this where the human isn't compelled to take an action such a log out. The simple solution above is great but I sure wouldn't use it for something like payroll time keeping.
Also, if someone tried to enforce something like this on me, especially since I'm salaried and work a fair bit of overtime, I'd be looking for a new job.

Totally agree, specially for us that only sleep couple of hours, don't think I would like to get paid for two hours a day:-D

😎