| Author |
 Topic  |
|
|
Pthepebble
Starting Member
Kenya
5 Posts |
 Posted - 02/15/2013 : 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
India
47089 Posts |
Posted - 02/15/2013 : 03:15:37
|
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/
|
 |
|
|
Pthepebble
Starting Member
Kenya
5 Posts |
Posted - 02/15/2013 : 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'.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47089 Posts |
Posted - 02/15/2013 : 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/
|
|
|
|
Pthepebble
Starting Member
Kenya
5 Posts |
Posted - 02/15/2013 : 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47089 Posts |
Posted - 02/15/2013 : 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/
|
|
|
|
Pthepebble
Starting Member
Kenya
5 Posts |
Posted - 02/15/2013 : 04:38:22
|
| checktime has date and time. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47089 Posts |
Posted - 02/15/2013 : 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/
|
|
|
|
Pthepebble
Starting Member
Kenya
5 Posts |
Posted - 02/15/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47089 Posts |
Posted - 02/15/2013 : 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/
|
|
|
| |
Topic |
|
|
|
Rearrange data based on in and out
