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.
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 assistRaw 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 Hours1 06/02/2013 08:03:19 14:07:43 61 06/02/2013 15:30:24 02:37:22 118 06/02/2013 08:25:15 14:12:21 68 06/02/2013 15:31:24 02:41:23 11 or UserID Date IN OUT Hours1 06/02/2013 06/02/2013 08:03:19 06/02/2013 14:07:43 61 06/02/2013 06/02/2013 15:30:24 07/02/2013 02:37:22 118 06/02/2013 06/02/2013 08:25:15 06/02/2013 14:12:21 68 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 hoursFROM(SELECT ROW_NUMBER() OVER (PARTITION BY UserID,ChekType ORDER BY Checktime) AS Seq,*FROM Table)tGROUP BY UserID,Seq[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 appearsMsg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'IN'.Msg 102, Level 15, State 1, Line 10Incorrect syntax near 't'. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-15 : 03:51:01
|
ok..IN is a reserved word. try likeSELECT 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)tGROUP BY UserID,Seq ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 below1 2012-12-30 00:00:00.000 2012-12-30 08:20:35.000 NULL NULL1 2012-12-31 00:00:00.000 2012-12-31 15:17:57.000 NULL NULL1 2012-12-31 00:00:00.000 2012-12-31 10:00:43.000 NULL NULL1 2013-01-03 00:00:00.000 2013-01-03 09:15:08.000 NULL NULL1 2013-01-04 00:00:00.000 2013-01-04 08:21:17.000 NULL NULL1 2013-01-04 00:00:00.000 2013-01-04 14:29:01.000 NULL NULL1 2013-01-06 00:00:00.000 2013-01-06 15:21:26.000 NULL NULL1 2013-01-06 00:00:00.000 2013-01-06 08:22:48.000 NULL NULL1 2013-01-07 00:00:00.000 2013-01-07 15:18:51.000 NULL NULL1 2013-01-07 00:00:00.000 2013-01-07 08:18:43.000 NULL NULL1 2013-01-08 00:00:00.000 2013-01-08 08:15:50.000 NULL NULLit 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:00i guess if you take into consideration this it should work fine. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
Pthepebble
Starting Member
5 Posts |
Posted - 2013-02-15 : 04:38:22
|
checktime has date and time. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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:00Hop this is clear |
|
|
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 datadidnt understand what problem you're facingset dateformat dmygodeclare @test table(UserID int,Checktime datetime,ChekType varchar(3))insert @testvalues(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)tGROUP BY UserID,SeqORDER BY UserID,SeqUserID 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 61 2013-02-06 00:00:00.000 2013-02-06 15:30:24.000 2013-02-07 02:37:22.000 118 2013-02-06 00:00:00.000 2013-02-06 08:25:15.000 2013-02-06 14:12:21.000 68 2013-02-06 00:00:00.000 2013-02-06 15:31:24.000 2013-02-07 02:41:23.000 11 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|