Cross Tab/Pivot

Question

Cross Tab/Pivot

tanehome

Ten Centuries

Points: 1167
More actions
November 10, 2014 at 2:18 am

#291795

Good Day for everybody,
here is a table shows the dates and times of entry and exit of staff throughout the day, where each record shows that either employee's entry or exit (check in or out)
based on (first in/last out) rule, i need to calculate the working hours (difference between first and last timing per day) per each employee.
would any body assist me to do it using cross tab/pivot?

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

tamer.h (11/10/2014)
Good Day for everybody,
here is a table shows the dates and times of entry and exit of staff throughout the day, where each record shows that either employee's entry or exit (check in or out)
based on (first in/last out) rule, i need to calculate the working hours (difference between first and last timing per day) per each employee.
would any body assist me to do it using cross tab/pivot?

First, thanks for posting the data in a readily consumable format. If you would, please, provide the full CREATE TABLE code, including the creation of any and all indexes for that table so we don't have to guess on the data types or make recommendations for indexes that already exist.

Heh... whomever designed the output of the reader didn't do you any favors. The inOutDate and inOutTime columns should have been in a single column and the reader_ID should have been in two columns.

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

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

Never mind. I believe the following does what you ask. You'll need to change the table name in the FROM clause to whatever your tablename actually is. '1753' and '9999' are dates (the first of January for the given year) and are present to complete each CASE statement so you don't get a warning about NULL eliminations.

SELECT userID

,inOutDate

,DailyHours =

CAST(

MAX(CASE WHEN reader_id LIKE 'Out%'

THEN CAST(inOutDate AS DATETIME)+CAST(inOutTime AS DATETIME)

ELSE '1753'

END)

- MIN(CASE WHEN reader_id LIKE 'IN%'

THEN CAST(inOutDate AS DATETIME)+CAST(inOutTime AS DATETIME)

ELSE '9999'

END)

AS DECIMAL(9,7))*24

FROM dbo.YourTable

GROUP BY userID, inOutDate

;

Now, let me ask... how do you want to handle people that work 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)

tanehome Ten Centuries Points: 1167 More actions · Answer 3

Thank you very much for you reply.

first you will find below the CREATE TABLE code showing data types, and actually we can't useing reader_id with CASE ELSE

as it's just a device name means the same reader can be used for entrance or exit at the same time.

Regarding the people that work through midnight, i realy didn't think about them yet:hehe:

CREATE TABLE [dbo].[EVENT_LOG](

[nEventLogIdn] [int] IDENTITY(1,1) NOT NULL,

[inOutDate] [date] NULL,

[inOutTime] [time](7) NULL,

[ReaderId] [int] NOT NULL,

[UserID] [int] NOT NULL,

[nIsLog] [smallint] NOT NULL

) ON [PRIMARY]