Click here to Skip to main content
14,668,709 members
Rate this:
Please Sign up or sign in to vote.
See more:
HI,

iam using thumb device.they give data base some tables... iam using one table by attendace. event_log table..
this is the table

my table [access_event_logs ]
  ,[USERID]nvarchar(50)
 ,[TIMESTAMPS]datetime
 ,[EVENTID]nvarchar(50)
 ,[LOCALTIMESTAMP]datetime


table like this

[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


all ready employee [in] and [out] check for this query...

select distinct
E.USERID,
Convert(date,LOCALTIMESTAMP) as ATTDate,
(select min(Convert(TIME,TIMESTAMPS)) from access_event_logs As MINCE where CAST(MINCE.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=E.USERID) as InTime,
(select max(Convert(TIME,TIMESTAMPS)) from access_event_logs <a href=""></a>As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=E.USERID) as OutTime
,(select DATEDIFF(MINUTE, '8:30:00', (select min(Convert(TIME,TIMESTAMPS)) from access_event_logs As MINCE2 where CAST(MINCE2.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MINCE2.EVENTID ='In' and MINCE2.USERID=E.USERID))) as LateTime
,(select DATEDIFF(MINUTE, (select max(Convert(TIME,TIMESTAMPS)) from access_event_logs As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=E.USERID), '14:30')) as EarlyTime
,(select DATEDIFF(MINUTE, '8:30:00', (select min(Convert(TIME,TIMESTAMPS)) from access_event_logs As MINCE2 where CAST(MINCE2.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MINCE2.EVENTID ='In' and MINCE2.USERID=E.USERID))) +
(select DATEDIFF(MINUTE, (select max(Convert(TIME,TIMESTAMPS)) from access_event_logs As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=E.USERID), '14:30')) as TotalLate
from access_event_logs As E
WHERE E.USERID='012' AND Convert(date,LOCALTIMESTAMP) between '03/2/2014' and '03/16/2014'



Userid      Date       InTime     OutTime  LateTime  EarilyOutTime TotalLateTime
012       2014-03-02  08:47:07   14:49:57     17      -19             -2
012       2014-03-03  08:27:01   14:31:58     -3       -1             -4
012       2014-03-04  08:29:43   14:32:12     -1       -2             -3
012       2014-03-05  08:34:29   14:23:29      4        7             11
012       2014-03-06  08:16:55   14:31:12    -14       -1            -15
012       2014-03-09  08:36:04   14:36:59      6       -6              0
012       2014-03-10  08:20:11    NULL        -1      NULL          NULL
012       2014-03-11  NULL       14:31:35   NULL      -1            NULL
012       2014-03-12  08:55:45   14:30:29     25        0             25
012       2014-03-13  08:35:52   14:38:58      5       -8             -3
012       2014-03-16  08:46:06   14:27:56     16        3             19




out put like this...

so i want dynamic table like this ... based on event_log table,

all ready every event they give update data.. this table based on that table ...

how create dynamic table...

please help me.... please i need this table....
Posted
Updated 10-Apr-14 0:14am
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Try this:
DECLARE @tmp TABLE([USERID] VARCHAR(5), [TIMESTAMPS] DATETIME, [EVENTID] VARCHAR(5))

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

SELECT t1.RowNo, t1.[USERID], t1.[IN], t2.[OUT]
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY TIMESTAMPS) AS RowNo, [USERID], [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], [TIMESTAMPS] AS [OUT]
        FROM @tmp
        WHERE [EVENTID] = 'OUT'
        ) AS t2 ON t1.USERID  = t2.USERID AND t1.RowNo = t2.RowNo


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



[EDIT]
How to "add" next table?
Using Join's.

SELECT t1.Field1, t2.Field1
FROM Table1 As t1 INNER JOIN Table2 AS t2 ON t1.PK = t2.FK


Have a look here: Visual Representation of SQL Joins[^] for better understanding how Join's work.
[/EDIT]
   
v3
Comments
akhil.krish 8-Apr-14 2:32am
   
thanks for rply Maciej Los...

i want create another table base on event log table sir....dynamic update the data...abother table based on event_log
Maciej Los 8-Apr-14 5:40am
   
?
Maciej Los 8-Apr-14 6:07am
   
Using Join's. Please check my answer for 10 minutes.
akhil.krish 8-Apr-14 6:41am
   
it is very urgent sir please help me.....
Maciej Los 8-Apr-14 7:23am
   
I can't see your screen...
akhil.krish 8-Apr-14 7:30am
   
http://postimg.org/image/v0axnzt7x/

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100