how to create Pivot table

Question

0.00/5 (No votes)

See more:

my (access_event_logs) table

[USERID]    [TIMESTAMPS]             [EVENTID]

1  019      2014-03-06 07:50:48.000   IN 
2  019      2014-03-06 17:02:39.000   OUT
3  019      2014-03-09 07:43:37.000   IN  
4  019      2014-03-09 14:34:59.000   OUT 
5  019      2014-03-10 07:43:34.000   IN 
6  019      2014-03-10 14:30:24.000   OUT

i want pivot table this columns

1) userid
2) date
3) in
4) out
5) total value (in and out) by date

Posted 1-Apr-14 7:22am

akhil.krish

Updated 1-Apr-14 7:38am

Maciej Los

v2

Add a Solution

Comments

Schatak 1-Apr-14 13:25pm

can you please show your table structure ?

Maciej Los 1-Apr-14 13:43pm

What you mean by [Total]?

akhil.krish 1-Apr-14 14:15pm

hi maciej los thanks for reply...

total means user particular date in value 1, out value 1, total 2, some time only IN total value 1..

Maciej Los 1-Apr-14 14:17pm

Please, see my answer.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-04-01T07:42:00

Solution 1

Please, see my comment to the question.

Try it:

SQL

SELECT userid, [IN], [OUT]
FROM (
    SELECT *
    FROM access_event_logs
    ) AS DT
PIVOT(MAX(timestamps) FOR eventid IN ([IN],[OUT])) AS PT

If [total] means the difference in hours, try it:

SQL

SELECT userid, [IN], [OUT], DATEDIFF(hh,[IN], [OUT]) AS TotalHrs
FROM (
    SELECT userid, [IN], [OUT]
    FROM (
        SELECT *
        FROM access_event_logs
        ) AS DT
    PIVOT(MAX(timestamps) FOR eventid IN ([IN],[OUT])) AS PT
) AS FT

See: DATEDIFF (sql)[^]

[EDIT]
Another solution:

SQL

CREATE TABLE #tmp (ID INT IDENTITY(1,1), [USERID] VARCHAR(10), [TIMESTAMPS] DATETIME, [EVENTID] VARCHAR(10))
 
INSERT INTO #tmp ([USERID], [TIMESTAMPS], [EVENTID])
VALUES('019', '2014-03-06 07:50:48.000', 'IN'),
('019', '2014-03-06 17:02:39.000', 'OUT'),
('019', '2014-03-09 07:43:37.000', 'IN'),
('019', '2014-03-09 14:34:59.000', 'OUT'),
('019', '2014-03-10 07:43:34.000', 'IN'),
('019', '2014-03-10 14:30:24.000', 'OUT'),
('020', '2014-03-10 07:40:14.000', 'IN'),
('020', '2014-03-10 14:31:28.000', 'OUT'),
('021', '2014-03-10 07:45:39.000', 'IN')
 
SELECT t1.RowNo, t1.[USERID], t1.CurrDate, t1.[IN], t2.[OUT], DATEDIFF(hh, t1.[IN], t2.[OUT]) AS WorkHrs
FROM (
    SELECT ROW_NUMBER() OVER( PARTITION BY [USERID] ORDER BY [TIMESTAMPS]) AS RowNo, [USERID], CONVERT(DATETIME, CONVERT(VARCHAR(10),[TIMESTAMPS],121)) AS CurrDate, [TIMESTAMPS] AS [IN]
    FROM #tmp
    WHERE [EVENTID] = 'IN'
    ) AS t1 INNER JOIN (
        SELECT ROW_NUMBER() OVER( PARTITION BY [USERID] ORDER BY [TIMESTAMPS]) AS RowNo, [USERID], CONVERT(DATETIME, CONVERT(VARCHAR(10),[TIMESTAMPS],121)) AS CurrDate, [TIMESTAMPS] AS [OUT]
        FROM #tmp
        WHERE [EVENTID] = 'OUT'
        ) AS t2 ON t1.RowNo = t2.RowNo AND t1.USERID  = t2.USERID 
 
DROP TABLE #tmp

[/EDIT]

Posted 1-Apr-14 7:42am

Maciej Los

Updated 1-Apr-14 11:53am

v5

Comments

Maciej Los 1-Apr-14 14:21pm

--wrong input--

Maciej Los 1-Apr-14 14:22pm

OK, replace:

SELECT *
FROM access_event_logs

with

SELECT userid, timestamps, eventid
FROM access_event_logs

akhil.krish 1-Apr-14 14:25pm

thanq so much.. Maciej los...this is working fine
i need u r mail id one more doubt is there.. pls send u r mail id.....

Maciej Los 1-Apr-14 14:30pm

Sorry, but NO. There are rules. I you have any question, please post it via CP QA board.
If my answer was helpfull, please mark it as a solution (green button).

akhil.krish 1-Apr-14 14:41pm

ok thanks for rply...

some time in two time or out time..but i want select the 1st in 1st out select same date and and some forget out , forget the IN that employee abesent another column..

Maciej Los 1-Apr-14 14:54pm

Sorry, but i do not understand you ;(

akhil.krish 1-Apr-14 15:36pm

Hi Maciej los ,
PIVOT(MAX(timestamps) FOR eventid IN ([IN],[OUT])) AS PT
max means last date giving .. i want between the date.......

Maciej Los 1-Apr-14 17:10pm

Please, see my answer after update (EDIT part) ;)

akhil.krish 1-Apr-14 17:20pm

actually my table 23 users ..

after execute this query output like this

001 2014-03-24 09:09:20.000 2014-03-24 14:18:54.000 5
002 2014-03-24 09:59:36.000 2014-03-24 13:43:49.000 4
003 2014-03-20 08:33:05.000 2014-03-19 14:20:03.000 -18
004 2014-03-24 07:40:28.000 2014-03-24 14:30:55.000 7
005 2014-03-24 08:03:06.000 2014-03-24 14:23:21.000 6
006 2014-03-24 09:46:58.000 2014-03-24 14:19:52.000 5
007 2014-03-24 10:59:07.000 2014-03-24 14:13:30.000 4
008 2014-03-20 09:50:23.000 2014-03-20 14:00:34.000 5
009 2014-03-24 08:58:26.000 2014-03-24 14:19:44.000 6
010 2014-03-23 09:24:59.000 2014-03-23 13:10:39.000 4
011 2014-03-23 10:08:19.000 2014-03-23 14:17:35.000 4
012 2014-03-24 08:58:08.000 2014-03-24 14:20:52.000 6
013 2014-03-19 09:12:38.000 2014-03-19 13:47:47.000 4

Maciej Los 1-Apr-14 17:21pm

What is the exact structure of table?

akhil.krish 1-Apr-14 17:27pm

,[USERID]nvarchar(50)
,[DEPARTMENT]nvarchar(50)
,[TIMESTAMPS]datetime
,[EVENTID]nvarchar(50)
,[TERMINALSN]nvarchar(50)
,[ACCESSMETHOD]nvarchar(255)
,[REMARKS]nvarchar(255)
,[TERMINALIP]nvarchar(50)
,[PHOTO]image
,[PHOTOSIZE]int
,[RECLOGDTM]datetime
,[RECLOGFROMIP]nvarchar(50)
,[LOCALTIMESTAMP]datetime
,[PhotoPath]nvarchar(50)

akhil.krish 1-Apr-14 17:32pm

There are hundreds of records like ID: 1 and 2. but what I need for now is INTIME and OUTTIME record to be linked to one seperate query or view for the records having SAME DATE. I don't want to do this using table dump method.

akhil.krish 1-Apr-14 17:36pm

Ok let me start from the beginning. I have a table under ACCESS named as LOGS. Following is the structure of the table:

_______userid_______LOCALTIMESTAMP
======================================
______007_____________2014-01-14 08:00:31.000 AM
______007_____________2014-01-14 15:00:31.000 PM

What i want is, i need a query to run which should appear to me as:

_____________Userid_______IN TIME_____________OUT TIME
================================================== ========
____________007_____________01/02/09 07:00 AM___01/02/09 17:30 PM

Currently logs are dumped into this table each log as a row. There are maximum two logs per date and I want to merge them into one.

Means the query should return me two records in one or in simple way: It should filter the userid (007) on date (01/02/09) and return this emp minimum time (time in) and maximum time (time out) in two fields and this "view merge" should be in one row so I can use in reports.

One more thing is that what can be done if more than two logs exists for same emp on same day

Maciej Los 1-Apr-14 17:33pm

Is there any Primary Key (PK), which identify each record? I haven't see it...

akhil.krish 1-Apr-14 17:37pm

id (pk,bigint)

Maciej Los 1-Apr-14 17:53pm

Check my answer now ;)

akhil.krish 1-Apr-14 18:00pm

thanks for rply Maciej Los .....:-)

012 2014-02-17 08:14:45.000 2014-02-17 14:28:25.000 6
012 2014-02-17 18:58:41.000 2014-02-17 18:58:02.000 0
012 2014-02-18 08:00:12.000 2014-02-18 00:06:53.000 -8
012 2014-02-19 08:19:25.000 2014-02-18 14:18:22.000 -18
012 2014-02-20 08:14:40.000 2014-02-19 14:33:17.000 -18
012 2014-02-23 08:15:37.000 2014-02-20 14:26:54.000 -66
012 2014-02-24 08:25:44.000 2014-02-23 14:25:49.000 -18
012 2014-02-25 08:26:49.000 2014-02-24 14:12:58.000 -18
012 2014-02-26 08:41:24.000 2014-02-25 13:58:39.000 -19
012 2014-02-27 08:36:38.000 2014-02-26 14:09:11.000 -18
012 2014-03-02 08:47:07.000 2014-02-27 14:17:26.000 -66
012 2014-03-03 08:27:01.000 2014-03-02 14:49:57.000 -18
012 2014-03-04 08:29:43.000 2014-03-03 14:31:58.000 -18
012 2014-03-05 08:34:29.000 2014-03-04 14:32:12.000 -18
012 2014-03-06 08:16:55.000 2014-03-05 14:23:29.000 -18
012 2014-03-09 08:36:04.000 2014-03-06 14:31:12.000 -66
012 2014-03-10 08:20:11.000 2014-03-09 14:36:59.000 -18
012 2014-03-10 19:18:14.000 2014-03-11 09:15:55.000 14
012 2014-03-12 08:55:45.000 2014-03-11 14:31:35.000 -18
012 2014-03-13 08:35:52.000 2014-03-12 14:30:29.000 -18
012 2014-03-16 08:46:06.000 2014-03-13 14:38:58.000 -66
012 2014-03-17 08:45:15.000 2014-03-16 14:27:56.000 -18
012 2014-03-18 10:12:19.000 2014-03-17 14:26:25.000 -20
012 2014-03-19 05:48:03.000 2014-03-18 14:26:33.000 -15
012 2014-03-20 05:50:27.000 2014-03-19 11:11:36.000 -18
012 2014-03-23 05:51:12.000 2014-03-20 11:19:26.000 -66
012 2014-03-24 05:58:08.000 2014-03-23 11:19:10.000 -18

12 user id attendance coming worng... why

akhil.krish 2-Apr-14 16:15pm

Hi Maciej Los,

this query working fine

select distinct CE.USERID,
(select min(TIMESTAMPS) from access_event_logs As MINCE where CAST(MINCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=CE.USERID) As MinTime,
(select max(TIMESTAMPS) from access_event_logs As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=CE.USERID) As MaxTime
from access_event_logs As CE
where LOCALTIMESTAMP between '03/03/2014' And '03/06/2014' and USERID= '019'

but i need 2more columns also 1) between the date Total time calculate 2)current date

please help me.... i need very urgent

akhil.krish 2-Apr-14 1:27am

Hi Maciej Los ,

check u r solution..
same problem... output like this

18 012 2014-02-06 00:00:00.000 2014-02-06 09:08:04.000 2014-02-06 14:27:33.000 5
19 012 2014-02-09 00:00:00.000 2014-02-09 08:36:27.000 2014-02-09 14:24:46.000 6
20 012 2014-02-10 00:00:00.000 2014-02-10 08:20:08.000 2014-02-10 14:44:52.000 6
21 012 2014-02-11 00:00:00.000 2014-02-11 08:26:02.000 2014-02-11 14:31:16.000 6
22 012 2014-02-12 00:00:00.000 2014-02-12 08:02:35.000 2014-02-12 14:28:35.000 6
23 012 2014-02-13 00:00:00.000 2014-02-13 08:01:50.000 2014-02-13 14:27:37.000 6
24 012 2014-02-16 00:00:00.000 2014-02-16 08:19:07.000 2014-02-16 14:33:22.000 6
25 012 2014-02-17 00:00:00.000 2014-02-17 08:14:45.000 2014-02-17 14:28:25.000 6
26 012 2014-02-17 00:00:00.000 2014-02-17 18:58:41.000 2014-02-17 18:58:02.000 0
27 012 2014-02-18 00:00:00.000 2014-02-18 08:00:12.000 2014-02-18 00:06:53.000 -8
28 012 2014-02-19 00:00:00.000 2014-02-19 08:19:25.000 2014-02-18 14:18:22.000 -18
29 012 2014-02-20 00:00:00.000 2014-02-20 08:14:40.000 2014-02-19 14:33:17.000 -18
30 012 2014-02-23 00:00:00.000 2014-02-23 08:15:37.000 2014-02-20 14:26:54.000 -66
31 012 2014-02-24 00:00:00.000 2014-02-24 08:25:44.000 2014-02-23 14:25:49.000 -18
32 012 2014-02-25 00:00:00.000 2014-02-25 08:26:49.000 2014-02-24 14:12:58.000 -18
33 012 2014-02-26 00:00:00.000 2014-02-26 08:41:24.000 2014-02-25 13:58:39.000 -19
34 012 2014-02-27 00:00:00.000 2014-02-27 08:36:38.000 2014-02-26 14:09:11.000 -18
35 012 2014-03-02 00:00:00.000 2014-03-02 08:47:07.000 2014-02-27 14:17:26.000 -66
36 012 2014-03-03 00:00:00.000 2014-03-03 08:27:01.000 2014-03-02 14:49:57.000 -18
37 012 2014-03-04 00:00:00.000 2014-03-04 08:29:43.000 2014-03-03 14:31:58.000 -18
38 012 2014-03-05 00:00:00.000 2014-03-05 08:34:29.000 2014-03-04 14:32:12.000 -18
39 012 2014-03-06 00:00:00.000 2014-03-06 08:16:55.000 2014-03-05 14:23:29.000 -18
40 012 2014-03-09 00:00:00.000 2014-03-09 08:36:04.000 2014-03-06 14:31:12.000 -66
41 012 2014-03-10 00:00:00.000 2014-03-10 08:20:11.000 2014-03-09 14:36:59.000 -18
42 012 2014-03-10 00:00:00.000 2014-03-10 19:18:14.000 2014-03-11 09:15:55.000 14
43 012 2014-03-12 00:00:00.000 2014-03-12 08:55:45.000 2014-03-11 14:31:35.000 -18
44 012 2014-03-13 00:00:00.000 2014-03-13 08:35:52.000 2014-03-12 14:30:29.000 -18
45 012 2014-03-16 00:00:00.000 2014-03-16 08:46:06.000 2014-03-13 14:38:58.000 -66
46 012 2014-03-17 00:00:00.000 2014-03-17 08:45:15.000 2014-03-16 14:27:56.000 -18
47 012 2014-03-18 00:00:00.000 2014-03-18 10:12:19.000 2014-03-17 14:26:25.000 -20
48 012 2014-03-19 00:00:00.000 2014-03-19 05:48:03.000 2014-03-18 14:26:33.000 -15
49 012 2014-03-20 00:00:00.000 2014-03-20 05:50:27.000 2014-03-19 11:11:36.000 -18
50 012 2014-03-23 00:00:00.000 2014-03-23 05:51:12.000 2014-03-20 11:19:26.000 -66

akhil.krish 2-Apr-14 2:38am

see here 19th date in (2014-02-19 08:19:25.000) out(2014-02-18 14:18:22.000) out 18th date .. some body forget the OUT i want null value..

AND SAME DAY ONLY TAKE IN AND OUT REMAINING NOT CALCULATE...first in first out,
my office timings 7:30 am to 2:30 pm

akhil.krish 2-Apr-14 2:30am

see here........
actually my [access_event_logs] table

id USERID TIMESTAMPS EVENTID
622 012 2014-02-06 09:08:04.000 IN
642 012 2014-02-06 14:27:33.000 OUT
654 012 2014-02-09 08:36:27.000 IN
672 012 2014-02-09 14:24:46.000 OUT
687 012 2014-02-10 08:20:08.000 IN
717 012 2014-02-10 14:44:52.000 OUT
722 012 2014-02-11 08:26:02.000 IN
748 012 2014-02-11 14:31:16.000 OUT
759 012 2014-02-12 08:02:35.000 IN
780 012 2014-02-12 14:28:35.000 OUT
787 012 2014-02-13 08:01:50.000 IN
809 012 2014-02-13 14:27:37.000 OUT
821 012 2014-02-16 08:19:07.000 IN
846 012 2014-02-16 14:33:22.000 OUT
854 012 2014-02-17 08:14:45.000 IN
879 012 2014-02-17 14:28:25.000 OUT
885 012 2014-02-17 18:58:02.000 OUT
886 012 2014-02-17 18:58:41.000 IN
888 012 2014-02-18 00:06:53.000 OUT
894 012 2014-02-18 08:00:12.000 IN
914 012 2014-02-18 14:18:22.000 OUT
936 012 2014-02-19 08:19:25.000 IN
958 012 2014-02-19 14:33:17.000 OUT
966 012 2014-02-20 08:14:40.000 IN
995 012 2014-02-20 14:26:54.000 OUT
1003 012 2014-02-23 08:15:37.000 IN
1036 012 2014-02-23 14:25:49.000 OUT
1049 012 2014-02-24 08:25:44.000 IN
1072 012 2014-02-24 14:12:58.000 OUT
1090 012 2014-02-25 08:26:49.000 IN
1107 012 2014-02-25 13:58:39.000 OUT
1128 012 2014-02-26 08:41:24.000 IN
1149 012 2014-02-26 14:09:11.000 OUT
1164 012 2014-02-27 08:36:38.000 IN
1191 012 2014-02-27 14:17:26.000 OUT
1207 012 2014-03-02 08:47:07.000 IN
1238 012 2014-03-02 14:49:57.000 OUT
1246 012 2014-03-03 08:27:01.000 IN
1275 012 2014-03-03 14:31:58.000 OUT
1285 012 2014-03-04 08:29:43.000 IN
1310 012 2014-03-04 14:32:12.000 OUT
1323 012 2014-03-05 08:34:29.000 IN
1340 012 2014-03-05 14:23:29.000 OUT
1353 012 2014-03-06 08:16:55.000 IN
1371 012 2014-03-06 14:31:12.000 OUT
1386 012 2014-03-09 08:36:04.000 IN
1414 012 2014-03-09 14:36:59.000 OUT
1424 012 2014-03-10 08:20:11.000 IN
1453 012 2014-03-10 19:18:14.000 IN
1467 012 2014-03-11 09:15:55.000 OUT
1468 012 2014-03-11 09:16:00.000 F1
1487 012 2014-03-11 14:31:35.000 OUT
1504 012 2014-03-12 08:55:45.000 IN
1522 012 2014-03-12 14:30:29.000 OUT
1536 012 2014-03-13 08:35:52.000 IN
1567 012 2014-03-13 14:38:58.000 OUT
1582 012 2014-03-16 08:46:06.000 IN
1603 012 2014-03-16 14:27:56.000 OUT
1619 012 2014-03-17 08:45:15.000 IN
1645 012 2014-03-17 14:26:25.000 OUT
1667 012 2014-03-18 10:12:19.000 IN
1682 012 2014-03-18 14:26:33.000 OUT
1698 012 2014-03-19 05:48:03.000 IN
1723 012 2014-03-19 11:11:36.000 OUT
1737 012 2014-03-20 05:50:27.000 IN
1751 012 2014-03-20 11:19:26.000 OUT
1766 012 2014-03-23 05:51:12.000 IN
1784 012 2014-03-23 11:19:10.000 OUT