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
 General SQL Server Forums
 New to SQL Server Programming
 Rearrange data based on in and out

Author  Topic 

Pthepebble
Starting Member

5 Posts

Posted - 2013-02-15 : 02:32:52
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

52326 Posts

Posted - 2013-02-15 : 03:15:37
[code]
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
[/code]

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

Go to Top of Page

Pthepebble
Starting Member

5 Posts

Posted - 2013-02-15 : 03:34:41
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

52326 Posts

Posted - 2013-02-15 : 03:51:01
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

5 Posts

Posted - 2013-02-15 : 04:09:46
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

52326 Posts

Posted - 2013-02-15 : 04:13:16
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

5 Posts

Posted - 2013-02-15 : 04:38:22
checktime has date and time.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-15 : 04:39:35
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

5 Posts

Posted - 2013-02-15 : 04:44:44
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

52326 Posts

Posted - 2013-02-15 : 04:48:45
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
   

- Advertisement -