Can you help me about a SQL sentence

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

Can you help me about a SQL sentence

Author

Topic

jeusdi
Starting Member

27 Posts

Posted - 2008-05-28 : 13:09:55

Hello forum. I address you in order to help me for create a little sentence.

the problem is as:
I have a table "STAMPING" that save data as:


TIMESTAMP------------USER_ID-----TERMINAL_ID----CHIP_ID
19/05/1982 15:30:31     1             1            1 
19/05/1982 15:31:29     1             1            1 
19/05/1982 15:31:55     1             1            1 
19/05/1982 16:25:46     1             1            1 
19/05/1982 16:26:23     1             1            1 
19/05/1982 16:26:34     1             1            1 
19/05/1982 22:23:56     1             1            1 
19/05/1982 22:24:23     1             1            1

and I would need to filter them as-->


TIMESTAMP------------USER_ID-----TERMINAL_ID----CHIP_ID
19/05/1982 15:30:31     1             1            1 
19/05/1982 15:31:29     1             1            1 
19/05/1982 15:31:55     1             1            1 
19/05/1982 16:25:46     1             1            1 
19/05/1982 16:26:23     1             1            1 
19/05/1982 16:26:34     1             1            1 
19/05/1982 22:23:56     1             1            1 
19/05/1982 22:24:23     1             1            1
20/05/1982 06:10:29     1             1            1 
20/05/1982 06:11:51     1             1            1


TIMESTAMP------------USER_ID-----TERMINAL_ID----CHIP_ID
19/05/1982 15:30:31     1             1            1 <<<----
                                                             less (difference) --> 00:56:03
19/05/1982 16:26:34     1             1            1 <<<----
19/05/1982 22:24:23     1             1            1 <<<----
                                                             less (difference) --> 07:45:00(+/-)
20/05/1982 06:11:51     1             1            1 <<<----

So, I need to delete the "timestamps" the an user has done, which the difference between one stamping and the following one is less than 2 minutes for the inputs for entrances and I need to remove it when the "stampings" which the difference between an one stamping and the previous one is less than 2 minutes for exits.

Can you help me please?
I will appreciate a lot your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 13:28:32

How can you distinguish entrances and exits here? do you have any other field for it?

jeusdi
Starting Member

27 Posts

Posted - 2008-05-29 : 02:37:08

No, only with these fields should be possible distingish between entraces and exits. We might stablish that the first timestamp group (difference between them is less than 2 minutes) is an entrace, the second one exit, the thirth one entrace...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 03:48:55

quote:
Originally posted by jeusdi

No, only with these fields should be possible distingish between entraces and exits. We might stablish that the first timestamp group (difference between them is less than 2 minutes) is an entrace, the second one exit, the thirth one entrace...

Ok. Even then how did you decide that 19/05/1982 16:25:46 should be deleted? What are rules for that? its diff is more than 2 minutes with previous record 19/05/1982 15:31:55

jeusdi
Starting Member

27 Posts

Posted - 2008-05-29 : 04:00:32

I think that a good beginning would be to get a table that a row with the previous timestamp and the following timestamp, eficiently:


TIMESTAMP------------USER_ID-----TERMINAL_ID----CHIP_ID----PREVIOUS-----------------NEXT
19/05/1982 15:30:31     1             1            1       NULL                     19/05/1982 15:31:29
19/05/1982 15:31:29     1             1            1       19/05/1982 15:30:31      19/05/1982 15:31:55
19/05/1982 15:31:55     1             1            1       19/05/1982 15:31:29      19/05/1982 16:25:46
19/05/1982 16:25:46     1             1            1       19/05/1982 15:31:55      19/05/1982 16:26:23
19/05/1982 16:26:23     1             1            1       19/05/1982 16:25:46      19/05/1982 16:26:34
19/05/1982 16:26:34     1             1            1       19/05/1982 16:26:23      19/05/1982 22:23:56
19/05/1982 22:23:56     1             1            1       19/05/1982 16:26:34      19/05/1982 22:24:23
19/05/1982 22:24:23     1             1            1       19/05/1982 22:23:56      NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 04:10:57

For that you can use a CTE with ROW_NUMBER() function.

E 12°55'05.25"
N 56°04'39.16"

jeusdi
Starting Member

27 Posts

Posted - 2008-05-29 : 05:42:19

quote:
Originally posted by visakh16
Ok. Even then how did you decide that 19/05/1982 16:25:46 should be deleted? What are rules for that? its diff is more than 2 minutes with previous record 19/05/1982 15:31:55

The timestamp are distinguished in entrances/exits timestamps groups:
So in the sample data are 4 groups:

In entrances group 1 are three timestamps (the diference between them are less than 2 minutes):


19/05/1982 15:30:31     1             1            1    <<<< in entrance group I have to get the first one
19/05/1982 15:31:29     1             1            1 
19/05/1982 15:31:55     1             1            1

In exits group 1 are three ones:


19/05/1982 16:25:46     1             1            1 
19/05/1982 16:26:23     1             1            1 
19/05/1982 16:26:34     1             1            1    <<<< in exit group I have to get the last one

Entrances group 2:


19/05/1982 22:23:56     1             1            1    <<<<
19/05/1982 22:24:23     1             1            1

Exit group 2:


20/05/1982 06:10:29     1             1            1 
20/05/1982 06:11:51     1             1            1    <<<<


TIMESTAMP------------USER_ID-----TERMINAL_ID----CHIP_ID
19/05/1982 15:30:31     1             1            1 <<<----
                                                             less (difference) --> 00:56:03
19/05/1982 16:26:34     1             1            1 <<<----
19/05/1982 22:23:56     1             1            1 <<<---- It was wrong in the first post!!!
                                                             less (difference) --> 07:45:00(+/-)
20/05/1982 06:11:51     1             1            1 <<<----

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 06:25:49

Are you using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 06:27:50

These records are two groups?


1982-05-19 15:30:31.000
1982-05-19 15:32:30.000  -- Less than two minutes from previous record
1982-05-19 15:34:30.000  -- Exactly two minutes from previous record
1982-05-19 15:36:31.000  -- More than two minutes from previous record, new group.

E 12°55'05.25"
N 56°04'39.16"

jeusdi
Starting Member

27 Posts

Posted - 2008-05-29 : 07:04:15

quote:
Originally posted by Peso

Are you using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

Sql Server 2000

jeusdi
Starting Member

27 Posts

Posted - 2008-05-29 : 07:07:42

quote:

Originally posted by Peso

These records are two groups?


1982-05-19 15:30:31.000
1982-05-19 15:32:30.000  -- Less than two minutes from previous record
1982-05-19 15:34:30.000  -- Exactly two minutes from previous record
1982-05-19 15:36:31.000  -- More than two minutes from previous record, new group.

E 12°55'05.25"
N 56°04'39.16"

Yes, the first group would be-->
1982-05-19 15:30:31.000
1982-05-19 15:32:30.000 -- Less than two minutes from previous record
and the second one-->
1982-05-19 15:34:30.000 -- Exactly two minutes from previous record
1982-05-19 15:36:31.000 -- More than two minutes from previous record, new group.[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 07:20:33

So they are THREE groups?


1982-05-19 15:30:31.000  -- Group 1
1982-05-19 15:32:30.000  -- Group 1
1982-05-19 15:34:30.000  -- Group 2
1982-05-19 15:36:31.000  -- Group 3

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 07:32:05

[code]-- Prepare sample data
CREATE TABLE #Sample
(
ts DATETIME,
uid INT,
tid INT,
cid INT,
grp INT
)

SET DATEFORMAT DMY

INSERT #Sample
(
ts,
uid,
tid,
cid
)
SELECT '19/05/1982 15:30:31', 1, 1, 1 UNION ALL
SELECT '19/05/1982 15:31:29', 1, 1, 1 UNION ALL
SELECT '19/05/1982 15:31:55', 1, 1, 1 UNION ALL
SELECT '19/05/1982 16:25:46', 1, 1, 1 UNION ALL
SELECT '19/05/1982 16:26:23', 1, 1, 1 UNION ALL
SELECT '19/05/1982 16:26:34', 1, 1, 1 UNION ALL
SELECT '19/05/1982 22:23:56', 1, 1, 1 UNION ALL
SELECT '19/05/1982 22:24:23', 1, 1, 1 UNION ALL
SELECT '20/05/1982 06:10:29', 1, 1, 1 UNION ALL
SELECT '20/05/1982 06:11:51', 1, 1, 1

-- Initialize staging
CREATE CLUSTERED INDEX IX_Yak ON #Sample (uid, ts)

UPDATE #Sample
SET Grp = NULL

-- Update sequencing
DECLARE @Grp INT,
@uid INT,
@ts DATETIME

SELECT TOP 1 @uid = uid,
@Grp = 0,
@ts = ts
FROM #Sample
ORDER BY uid,
ts

UPDATE #Sample
SET @Grp = Grp = CASE
WHEN uid > @uid THEN 1
WHEN ts < DATEADD(MINUTE, 2, @ts) THEN @Grp
ELSE @Grp + 1
END,
@ts = ts,
@uid = uid

-- Show the expected resultset
SELECT CASE
WHEN Grp % 2 = 0 THEN MIN(ts)
ELSE MAX(ts)
END AS TIMESTAMP
FROM #Sample
GROUP BY Grp
ORDER BY Grp

-- Clean up
DROP TABLE #Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources