Time Logs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Time Logs

Author

Topic

chriztoph
Posting Yak Master

184 Posts

Posted - 2014-03-28 : 00:58:38

Hi guys,

How do I combine these records.

I have this records.
EVENT: 0 = Time-In and 1 = Time-Out


USERID  TANSDATEDATE             EVENT
70201	2014-03-27 08:56:09.000  0
70201	2014-03-27 18:14:54.000  1
70201	2014-03-25 08:54:12.000  0
70201	2014-03-25 18:14:35.000  1

So far i have this:


SELECT USERID, CONVERT(NVARCHAR(10), TANSDATEDATE, 120),
			 CASE WHEN EVENT = 0 THEN MIN(TANSDATEDATE) END AS [TIME-IN],
			 CASE WHEN EVENT = 1 THEN MAX(TANSDATEDATE) END AS [TIME-OUT]
FROM ATTENDANCE
WHERE USERID = '70201'
GROUP BY USERID, EVENT, CONVERT(NVARCHAR(10), TANSDATEDATE, 120)
ORDER BY CONVERT(NVARCHAR(10), TANSDATEDATE, 120) DESC

Current result:


USERID  TANSDATEDATE    TIME-IN                 TIME-OUT
70201	2014-03-27	2014-03-27 08:56:09.000	NULL
70201	2014-03-27	NULL	                2014-03-27 18:14:54.000
70201	2014-03-25	2014-03-25 08:54:12.000	NULL
70201	2014-03-25	NULL	                2014-03-25 18:14:35.000

Expected Result:


USERID  TANSDATEDATE    TIME-IN                 TIME-OUT
70201	2014-03-27	2014-03-27 08:56:09.000 2014-03-27 18:14:54.000
70201	2014-03-25	2014-03-25 08:54:12.000 2014-03-25 18:14:35.000

Thanks in advance guys.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-28 : 02:04:05

[code]
;with currentResult
AS
(select 70201 USERID,'2014-03-27' TANSDATEDATE, '2014-03-27 08:56:09.000' [TIME-IN],NULL [TIME-OUT] union all
select 70201,'2014-03-27',NULL,'2014-03-27 18:14:54.000' union all
select 70201,'2014-03-25','2014-03-25 08:54:12.000',NULL union all
select 70201,'2014-03-25',NULL,'2014-03-25 18:14:35.000')

select
COALESCE([IN].UserID,OUT.UserID) as UserID
,COALESCE([IN].TANSDATEDATE,OUT.TANSDATEDATE) as TransDateDATE
,[IN].[TIme-IN] as [Time-IN]
,[OUT].[Time-OUT] as [Time-OUT]
from

(select UserID,TANSDATEDATE,[Time-IN]
from currentResult
where [Time-In] is not null) as [IN]
full join

(select UserID,TANSDATEDATE,[Time-Out]
from currentResult
where [Time-Out] is not null) as [OUT]
ON [IN].UserID=OUT.UserID
AND [IN].TANSDATEDATE=OUT.TANSDATEDATE

[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-28 : 02:04:22

output


UserID	TransDateDATE	Time-IN	Time-OUT
70201	2014-03-27	2014-03-27 08:56:09.000	2014-03-27 18:14:54.000
70201	2014-03-25	2014-03-25 08:54:12.000	2014-03-25 18:14:35.000

sabinWeb MCP

chriztoph
Posting Yak Master

184 Posts

Posted - 2014-03-28 : 03:21:11

Thank you so much @stepson.

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-03-28 : 04:14:32

This will also give you the same result.......

CREATE TABLE ATTENDANCE([USERID] INT ,[TANSDATEDATE] DATETIME,[EVENT] INT)
INSERT INTO ATTENDANCE VALUES (70201,'2014-03-27 08:56:09.000',0 ),
(70201,'2014-03-27 18:14:54.000',1),
(70201,'2014-03-25 08:54:12.000',0),
(70201,'2014-03-25 18:14:35.000',1)

SELECT A.USERID
, CONVERT(NVARCHAR(10), A.TANSDATEDATE, 120) AS TANSDATEDATE
, A.[TIME-IN]
, B.[TIME-OUT]
FROM ( SELECT USERID
, CONVERT(NVARCHAR(10), TANSDATEDATE, 120) AS TANSDATEDATE,
CASE WHEN EVENT = 0 THEN CAST(TANSDATEDATE AS TIME) END AS [TIME-IN]
FROM ATTENDANCE ) A
INNER JOIN (SELECT CONVERT(NVARCHAR(10), TANSDATEDATE, 120) AS TANSDATEDATE,
CASE WHEN EVENT = 1 THEN CAST(TANSDATEDATE AS TIME) END AS [TIME-OUT]
FROM ATTENDANCE ) B
ON A.TANSDATEDATE = B.TANSDATEDATE
WHERE A.[TIME-IN] IS NOT NULL
AND B.[TIME-OUT] IS NOT NULL

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-28 : 16:21:54

Since you're counting on the day to be unique, you can just do this:


-- test data
;with attendance
AS
(select 70201 AS USERID, CAST('20140327 08:56:09.000' AS datetime) AS TANSDATEDATE, 0 AS EVENT UNION ALL
 select 70201, '20140327 18:14:54.000', 1 UNION ALL
 select 70201, '20140325 08:54:12.000', 0 UNION ALL
 select 70201, '20140325 18:14:35.000', 1
)


--main query
SELECT USERID, CONVERT(NVARCHAR(10), TANSDATEDATE, 120),
			 MAX(CASE WHEN EVENT = 0 THEN TANSDATEDATE END) AS [TIME-IN],
			 MAX(CASE WHEN EVENT = 1 THEN TANSDATEDATE END) AS [TIME-OUT]
FROM ATTENDANCE
WHERE USERID = '70201'
GROUP BY USERID, CONVERT(NVARCHAR(10), TANSDATEDATE, 120)
ORDER BY USERID, CONVERT(NVARCHAR(10), TANSDATEDATE, 120) DESC

chriztoph
Posting Yak Master

184 Posts

Posted - 2014-04-03 : 04:15:11

Thanks @ScottPletcher.

What if i have a record like this


USERID  TANSDATEDATE             EVENT
70201	2014-03-27 08:56:09.000  0
70201	2014-03-27 18:14:54.000  1
70201	2014-03-25 08:54:12.000  0
70201   2014-03-25 18:14:35.000  1
70201	2014-03-26 04:10:35.000  1

and the result should be like this


USERID  TANSDATEDATE    TIME-IN                 TIME-OUT
70201	2014-03-27	2014-03-27 08:56:09.000 2014-03-27 18:14:54.000
70201	2014-03-25	2014-03-25 08:54:12.000 2014-03-26 04:10:35.000

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-03 : 16:31:23

If you know that you'll always have matching timein and timeout rows, it's easy, you just do this:



-- test data
;with attendance AS (
    select 70201 AS USERID, CAST('20140327 08:56:09.000' AS datetime) AS TANSDATEDATE, 0 AS EVENT UNION ALL
    select 70201, '20140327 18:14:54.000', 1 UNION ALL
    select 70201, '20140325 18:14:35.000', 0 UNION ALL
    select 70201, '20140326 04:10:35.000', 1
),

--main query
--;with
attendance_sequenced AS (
    SELECT *, (ROW_NUMBER() OVER (PARTITION BY USERID ORDER BY TANSDATEDATE) - 1) / 2 AS row_num
    FROM attendance
)
SELECT USERID, 
			 MAX(CASE WHEN EVENT = 0 THEN TANSDATEDATE END) AS [TIME-IN],
			 MAX(CASE WHEN EVENT = 1 THEN TANSDATEDATE END) AS [TIME-OUT]
FROM attendance_sequenced
WHERE USERID = '70201'
GROUP BY USERID, row_num
ORDER BY USERID, row_num DESC

chriztoph
Posting Yak Master

184 Posts

Posted - 2014-04-03 : 23:42:55

@ScottPletcher, here is the result i got.


USERID  TIME-IN                 TIME-OUT
70201	2014-02-10 08:50:31.000	2014-02-07 18:56:57.000
70201	2014-02-07 08:52:06.000	2014-02-06 18:26:36.000
70201	2014-02-06 08:57:52.000	2014-02-05 19:32:21.000
70201	2014-02-05 08:54:56.000	2014-02-04 18:31:43.000
70201	2014-02-04 08:38:20.000	2014-02-03 18:41:37.000
70201	2014-02-03 08:44:16.000	2014-01-30 18:32:32.000
70201	2014-01-30 08:55:11.000	2014-01-29 19:03:50.000
70201	2014-01-29 09:00:24.000	NULL
70201	2014-01-28 09:13:48.000	2014-01-28 18:19:00.000
70201	2014-01-27 08:35:47.000	2014-01-27 18:35:19.000
70201	2014-01-24 09:25:53.000	2014-01-24 19:24:25.000

if idon't have this date 2014-01-30 18:32:32.000
the result would be this.


USERID  TIME-IN                 TIME-OUT
70201	2014-02-10 08:50:31.000	2014-02-10 19:05:01.000
70201	2014-02-07 08:52:06.000	2014-02-07 18:56:57.000
70201	2014-02-06 08:57:52.000	2014-02-06 18:26:36.000
70201	2014-02-05 08:54:56.000	2014-02-05 19:32:21.000
70201	2014-02-04 08:38:20.000	2014-02-04 18:31:43.000
70201	2014-01-30 08:55:11.000	2014-02-03 18:41:37.000
70201	2014-01-29 09:00:24.000	2014-01-29 19:03:50.000
70201	2014-01-28 09:13:48.000	2014-01-28 18:19:00.000
70201	2014-01-27 08:35:47.000	2014-01-27 18:35:19.000
70201	2014-01-24 09:25:53.000	2014-01-24 19:24:25.000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources