Hours Calculation

Question

Post reply

Hours Calculation

waheed71

Old Hand

Points: 362
More actions
February 26, 2014 at 1:32 pm

#290357

Dear All,
I will appreciate if you can help me to write hour calculation report based on number of In and Out from site. The Problem I am facing here is I have one DateTime field and that is being updated based on Reader access.
For example I have card number 236 (Param3) and Entered into site using Reader (26) on 2014-02-24 8:00:00 and Left from Site using Reader (27) at 2014-02-24 13:00:00 for lunch break in same fashion entered into site at 2014-02-24 14:05:00 and left from site at 2014-02-24 17:10:00, the query shall calculate each time in and time out difference into hours and finally total number of hours spend at site (in above example it shall display 8:05 H).
So far I have write below mentioned query but unable to get difference.
SELECT TimeStamp,GenTime,Param3,Reader,Name,
RANK() Over (Partition by Reader,Param3 Order by GenTime) as 'CountofDay'
FROM [AccessControl].[dbo].[T_ACS]
Below is Table Structure and Sample Data available for your ready reference.
Table Structure:
USE [AccessControl]
GO
/****** Object: Table [dbo].[T_ACS] Script Date: 02/27/2014 00:29:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_ACS](
[RecordID] [int] IDENTITY(1,1) NOT NULL,
[TimeStamp] [datetime] NULL,
[GenTime] [datetime] NULL,
[Param3] [nvarchar](30) NULL,
[Reader] [int] NULL,
[Name] [nvarchar](50) NULL,
CONSTRAINT [PK_T_ACS] PRIMARY KEY CLUSTERED
(
[RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Sample Data:
//--- INSERT INTO T_ACS (TimeStamp,GenTime,Param3,Reader,Name)
INSERT INTO T_ACS VALUES ('2003-04-22 12:02:34.000','2003-04-22 12:02:34.000','235','26','Joe John')
INSERT INTO T_ACS VALUES ('2003-04-22 13:45:34.000','2003-04-22 13:45:34.000','235','26','Joe John')
INSERT INTO T_ACS VALUES ('2003-04-22 15:50:34.000','2003-04-22 15:50:34.000','235','26','Joe John')
INSERT INTO T_ACS VALUES ('2003-04-22 12:22:34.000','2003-04-22 12:22:34.000','235','27','Joe John')
INSERT INTO T_ACS VALUES ('2003-04-22 14:45:34.000','2003-04-22 14:45:34.000','235','27','Joe John')
INSERT INTO T_ACS VALUES ('2003-04-22 17:02:34.000','2003-04-22 17:02:34.000','235','27','Joe John')
INSERT INTO T_ACS VALUES ('2003-04-22 12:02:34.000','2003-04-22 12:02:34.000','237','26','Ivan Liu')
INSERT INTO T_ACS VALUES ('2003-04-23 13:45:34.000','2003-04-23 13:45:34.000','237','26','Ivan Liu')
INSERT INTO T_ACS VALUES ('2003-04-23 23:50:34.000','2003-04-23 23:50:34.000','237','26','Ivan Liu')
INSERT INTO T_ACS VALUES ('2003-04-22 12:22:34.000','2003-04-22 12:22:34.000','237','27','Ivan Liu')
INSERT INTO T_ACS VALUES ('2003-04-23 14:45:34.000','2003-04-23 14:45:34.000','237','27','Ivan Liu')
INSERT INTO T_ACS VALUES ('2003-04-24 7:02:34.000','2003-04-24 7:02:34.000','237','27','Ivan Liu')
INSERT INTO T_ACS VALUES ('2003-04-25 12:02:34.000','2003-04-25 12:02:34.000','238','26','Jet Lim')
INSERT INTO T_ACS VALUES ('2003-04-26 13:45:34.000','2003-04-26 13:45:34.000','238','26','Jet Lim')
INSERT INTO T_ACS VALUES ('2003-04-26 23:59:34.000','2003-04-26 23:59:34.000','238','26','Jet Lim')
INSERT INTO T_ACS VALUES ('2003-04-25 12:42:34.000','2003-04-25 12:42:34.000','238','27','Jet Lim')
INSERT INTO T_ACS VALUES ('2003-04-26 19:49:34.000','2003-04-26 19:49:34.000','238','27','Jet Lim')
INSERT INTO T_ACS VALUES ('2003-04-27 7:02:34.000','2003-04-27 7:02:34.000','238','27','Jet Lim')
INSERT INTO T_ACS VALUES ('2003-04-27 8:02:34.000','2003-04-27 8:02:34.000','236','26','Patrick Liw')

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

This is something that came to my mind. It won't work if you have two continuous Ins or Outs.

WITH rowCTE AS(

SELECT *,

ROW_NUMBER() OVER(PARTITION BY Name, Reader ORDER BY [TimeStamp])rn --Have something to join

FROM T_ACS

)

SELECT i.Name,

CAST( DATEADD( SS, SUM(DATEDIFF(SS, i.TimeStamp, o.TimeStamp)), 0) AS Time) --Get the difference in seconds, add it to a zero date and convert to time.

FROM (SELECT Name, TimeStamp, rn FROM rowCTE WHERE Reader = 26) i --ins

JOIN (SELECT Name, TimeStamp, rn FROM rowCTE WHERE Reader = 27) o --outs

ON i.Name = o.Name AND i.rn = o.rn

GROUP BY i.Name

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

waheed71 (2/26/2014)
Dear All,
I will appreciate if you can help me to write hour calculation report based on number of In and Out from site. The Problem I am facing here is I have one DateTime field and that is being updated based on Reader access.
For example I have card number 236 (Param3) and Entered into site using Reader (26) on 2014-02-24 8:00:00 and Left from Site using Reader (27) at 2014-02-24 13:00:00 for lunch break in same fashion entered into site at 2014-02-24 14:05:00 and left from site at 2014-02-24 17:10:00, the query shall calculate each time in and time out difference into hours and finally total number of hours spend at site (in above example it shall display 8:05 H).
So far I have write below mentioned query but unable to get difference.
SELECT TimeStamp,GenTime,Param3,Reader,Name,
RANK() Over (Partition by Reader,Param3 Order by GenTime) as 'CountofDay'
FROM [AccessControl].[dbo].[T_ACS]
Below is Table Structure and Sample Data available for your ready reference.
Table Structure:
USE [AccessControl]
GO
/****** Object: Table [dbo].[T_ACS] Script Date: 02/27/2014 00:29:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_ACS](
[RecordID] [int] IDENTITY(1,1) NOT NULL,
[TimeStamp] [datetime] NULL,
[GenTime] [datetime] NULL,
[Param3] [nvarchar](30) NULL,
[Reader] [int] NULL,
[Name] [nvarchar](50) NULL,
CONSTRAINT [PK_T_ACS] PRIMARY KEY CLUSTERED
(
[RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Sample Data:
//--- INSERT INTO T_ACS (TimeStamp,GenTime,Param3,Reader,Name)
INSERT INTO T_ACS VALUES ('2003-04-22 12:02:34.000','2003-04-22 12:02:34.000','235','26','Joe John')
INSERT INTO T_ACS VALUES ('2003-04-22 13:45:34.000','2003-04-22 13:45:34.000','235','26','Joe John')
INSERT INTO T_ACS VALUES ('2003-04-22 15:50:34.000','2003-04-22 15:50:34.000','235','26','Joe John')
INSERT INTO T_ACS VALUES ('2003-04-22 12:22:34.000','2003-04-22 12:22:34.000','235','27','Joe John')
INSERT INTO T_ACS VALUES ('2003-04-22 14:45:34.000','2003-04-22 14:45:34.000','235','27','Joe John')
INSERT INTO T_ACS VALUES ('2003-04-22 17:02:34.000','2003-04-22 17:02:34.000','235','27','Joe John')
INSERT INTO T_ACS VALUES ('2003-04-22 12:02:34.000','2003-04-22 12:02:34.000','237','26','Ivan Liu')
INSERT INTO T_ACS VALUES ('2003-04-23 13:45:34.000','2003-04-23 13:45:34.000','237','26','Ivan Liu')
INSERT INTO T_ACS VALUES ('2003-04-23 23:50:34.000','2003-04-23 23:50:34.000','237','26','Ivan Liu')
INSERT INTO T_ACS VALUES ('2003-04-22 12:22:34.000','2003-04-22 12:22:34.000','237','27','Ivan Liu')
INSERT INTO T_ACS VALUES ('2003-04-23 14:45:34.000','2003-04-23 14:45:34.000','237','27','Ivan Liu')
INSERT INTO T_ACS VALUES ('2003-04-24 7:02:34.000','2003-04-24 7:02:34.000','237','27','Ivan Liu')
INSERT INTO T_ACS VALUES ('2003-04-25 12:02:34.000','2003-04-25 12:02:34.000','238','26','Jet Lim')
INSERT INTO T_ACS VALUES ('2003-04-26 13:45:34.000','2003-04-26 13:45:34.000','238','26','Jet Lim')
INSERT INTO T_ACS VALUES ('2003-04-26 23:59:34.000','2003-04-26 23:59:34.000','238','26','Jet Lim')
INSERT INTO T_ACS VALUES ('2003-04-25 12:42:34.000','2003-04-25 12:42:34.000','238','27','Jet Lim')
INSERT INTO T_ACS VALUES ('2003-04-26 19:49:34.000','2003-04-26 19:49:34.000','238','27','Jet Lim')
INSERT INTO T_ACS VALUES ('2003-04-27 7:02:34.000','2003-04-27 7:02:34.000','238','27','Jet Lim')
INSERT INTO T_ACS VALUES ('2003-04-27 8:02:34.000','2003-04-27 8:02:34.000','236','26','Patrick Liw')

Unless there is a guarantee that reader 26 is only for punch-ins and reader 27 is only for punch-outs, this problem isn't solvable based on the data given. Is there such a guarantee?

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

waheed71 Old Hand Points: 362 More actions · Answer 3

Dear Jeff,

Yes this is for sure that Reader 26 and 27 are fixed for Ins and Out purpose.

waheed71 Old Hand Points: 362 More actions · Answer 4

Dear Luis C,

Thank you for providing me initial thought process, in this Query how to calculate hours for Mr. Patrick Liw and assumptions are every person will be on site for 8 hours.

Thank you.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

How would you calculate hours for Mr. Patrick Liw? You only have one row for him. You can't calculate the time, you could give an arbitrary value using LEFT JOIN and ISNULL.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2