SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Rearrange data based on in and out
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pthepebble
Starting Member

Kenya
5 Posts

Posted - 02/15/2013 :  02:32:52  Show Profile  Reply with Quote
Hi All,

I have users clockings and i need to rearrange them to each in match an out. You have in without out and also out without in.

User can clock for one day as early as 7:00 to the next day not later than 04:00. all this relates to one day.

My raw data and expected result is as below. Please assist

Raw Data
UserID Checktime ChekType
1 06/02/2013 08:03:19 IN
1 06/02/2013 14:07:43 OUT
1 06/02/2013 15:30:24 IN
1 07/02/2013 02:37:22 OUT
8 06/02/2013 08:25:15 IN
8 06/02/2013 14:12:21 OUT
8 06/02/2013 15:31:24 IN
8 07/02/2013 02:41:23 OUT

Expected Result

UserID Date IN OUT Hours
1 06/02/2013 08:03:19 14:07:43 6
1 06/02/2013 15:30:24 02:37:22 11
8 06/02/2013 08:25:15 14:12:21 6
8 06/02/2013 15:31:24 02:41:23 11

or
UserID Date IN OUT Hours
1 06/02/2013 06/02/2013 08:03:19 06/02/2013 14:07:43 6
1 06/02/2013 06/02/2013 15:30:24 07/02/2013 02:37:22 11
8 06/02/2013 06/02/2013 08:25:15 06/02/2013 14:12:21 6
8 06/02/2013 06/02/2013 15:31:24 07/02/2013 02:41:23 11


visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/15/2013 :  03:15:37  Show Profile  Reply with Quote

SELECT UserID,
DATEADD(dd,DATEDIFF(dd,0,MAX(CASE WHEN ChekType = 'IN' THEN Checktime END)),0) AS DateVal,
MAX(CASE WHEN ChekType = 'IN' THEN Checktime END) AS IN,
MAX(CASE WHEN ChekType = 'OUT' THEN Checktime END) AS OUT,
DATEDIFF(hh,MAX(CASE WHEN ChekType = 'IN' THEN Checktime END),MAX(CASE WHEN ChekType = 'OUT' THEN Checktime END)) AS hours
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY UserID,ChekType ORDER BY Checktime) AS Seq,*
FROM Table
)t
GROUP BY UserID,Seq


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Pthepebble
Starting Member

Kenya
5 Posts

Posted - 02/15/2013 :  03:34:41  Show Profile  Reply with Quote
Thanks Visakh16. i have tried to run the script but the below error appears

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'IN'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 't'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/15/2013 :  03:51:01  Show Profile  Reply with Quote
ok..IN is a reserved word. try like



SELECT UserID,
DATEADD(dd,DATEDIFF(dd,0,MAX(CASE WHEN ChekType = 'IN' THEN Checktime END)),0) AS DateVal,
MAX(CASE WHEN ChekType = 'IN' THEN Checktime END) AS [IN],
MAX(CASE WHEN ChekType = 'OUT' THEN Checktime END) AS [OUT],
DATEDIFF(hh,MAX(CASE WHEN ChekType = 'IN' THEN Checktime END),MAX(CASE WHEN ChekType = 'OUT' THEN Checktime END)) AS [hours]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY UserID,ChekType ORDER BY Checktime) AS Seq,*
FROM Table
)t
GROUP BY UserID,Seq


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Pthepebble
Starting Member

Kenya
5 Posts

Posted - 02/15/2013 :  04:09:46  Show Profile  Reply with Quote
the script can now run but it doesn't run as expected. see the sample result below

1 2012-12-30 00:00:00.000 2012-12-30 08:20:35.000 NULL NULL
1 2012-12-31 00:00:00.000 2012-12-31 15:17:57.000 NULL NULL
1 2012-12-31 00:00:00.000 2012-12-31 10:00:43.000 NULL NULL
1 2013-01-03 00:00:00.000 2013-01-03 09:15:08.000 NULL NULL
1 2013-01-04 00:00:00.000 2013-01-04 08:21:17.000 NULL NULL
1 2013-01-04 00:00:00.000 2013-01-04 14:29:01.000 NULL NULL
1 2013-01-06 00:00:00.000 2013-01-06 15:21:26.000 NULL NULL
1 2013-01-06 00:00:00.000 2013-01-06 08:22:48.000 NULL NULL
1 2013-01-07 00:00:00.000 2013-01-07 15:18:51.000 NULL NULL
1 2013-01-07 00:00:00.000 2013-01-07 08:18:43.000 NULL NULL
1 2013-01-08 00:00:00.000 2013-01-08 08:15:50.000 NULL NULL

it doesn't take into the account the date range. I am assuming a day like 2013-01-06 should rearrange data only using a date range like data from 2013-01-06 05:00:00 to 2013-01-07 04:00:00

i guess if you take into consideration this it should work fine.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/15/2013 :  04:13:16  Show Profile  Reply with Quote
where's this date range defined? is there a table for that?

You didnt specify this rule in your first post so how do you think we can guess this out?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Pthepebble
Starting Member

Kenya
5 Posts

Posted - 02/15/2013 :  04:38:22  Show Profile  Reply with Quote
checktime has date and time.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/15/2013 :  04:39:35  Show Profile  Reply with Quote
so? how do you determine what all date values will fall in a particular range?
I didnt really understand the date range you're talking about here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Pthepebble
Starting Member

Kenya
5 Posts

Posted - 02/15/2013 :  04:44:44  Show Profile  Reply with Quote
sorry for i mention that each date starts from 05:00:00 to 04:00:00 the next day example a day like 2013-01-06 will have a date range from 2013-01-06 05:00:00 to 2013-01-07 04:00:00

Hop this is clear
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/15/2013 :  04:48:45  Show Profile  Reply with Quote
this is full illustration of solution for your sample data
didnt understand what problem you're facing


set dateformat dmy
go
declare @test table
(
UserID int,
Checktime	datetime,
ChekType	varchar(3)
)
insert @test
values(1,	'06/02/2013 08:03:19','IN'), 	
(1,	'06/02/2013 14:07:43','OUT'),
(1,	'06/02/2013 15:30:24','IN'),
(1,	'07/02/2013 02:37:22','OUT'),
(8,	'06/02/2013 08:25:15','IN'),
(8,	'06/02/2013 14:12:21','OUT'),
(8,	'06/02/2013 15:31:24','IN'),
(8,	'07/02/2013 02:41:23','OUT')


SELECT UserID,
DATEADD(dd,DATEDIFF(dd,0,MAX(CASE WHEN ChekType = 'IN' THEN Checktime END)),0) AS DateVal,
MAX(CASE WHEN ChekType = 'IN' THEN Checktime END) AS [IN],
MAX(CASE WHEN ChekType = 'OUT' THEN Checktime END) AS [OUT],
DATEDIFF(hh,MAX(CASE WHEN ChekType = 'IN' THEN Checktime END),MAX(CASE WHEN ChekType = 'OUT' THEN Checktime END)) AS [hours]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY UserID,ChekType ORDER BY Checktime) AS Seq,*
FROM @test
)t
GROUP BY UserID,Seq
ORDER BY UserID,Seq


UserID	DateVal	                IN	                OUT	                hours
----------------------------------------------------------------------------------------
1	2013-02-06 00:00:00.000	2013-02-06 08:03:19.000	2013-02-06 14:07:43.000	6
1	2013-02-06 00:00:00.000	2013-02-06 15:30:24.000	2013-02-07 02:37:22.000	11
8	2013-02-06 00:00:00.000	2013-02-06 08:25:15.000	2013-02-06 14:12:21.000	6
8	2013-02-06 00:00:00.000	2013-02-06 15:31:24.000	2013-02-07 02:41:23.000	11



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000