quote:

---

Originally posted by Sachin.Nand

Will the name values be repeated ?

PBUH

---

quote:

---

Originally posted by thenhi

Hi all,

I have a table

ID Name Value
1 A 20
2 B 39
3 C 56
4 D 10
5 E 23
6 F 47
7 G 67
8 H 6
9 E 67
10 G 87

I want to make a query to get max and min ID in group which has value greater than 30.
Result:
ID Name Value
2 B 39
3 C 56
6 F 47
7 G 67
9 E 67
10 G 87

Thanks,

---

quote:

---

Originally posted by thenhi

Dear Sachin.Nand

Let me explain more cleary.

I am developing a website GPS Live Tracking. I want to make a report the time speeding vehicle.

EX:

ID Lat Lon DateTime Speed
1 - - 01:02:01 28
2 - - 01:02:05 60
3 - - 01:02:12 68
4 - - 01:02:24 65
5 - - 01:02:35 30
6 - - 01:02:45 35
7 - - 01:02:50 40
8 - - 01:02:55 70
9 - - 01:03:00 75
11 - - 01:03:05 73
12 - - 01:03:10 80
13 - - 01:03:15 50
14 - - 01:03:20 49
15 - - 01:03:25 69
16 - - 01:03:30 69

Output save as new table:
LatMin LonMin LatMax LonMax TimeMin TimeMax
- - - - 01:02:05 01:02:24
- - - - 01:02:55 01:03:10
- - - - 01:03:25 01:03:30

Thanks,

---

DECLARE	@Sample TABLE
	(
		ID INT,
		Lat DECIMAL(9, 6),
		Lon DECIMAL(9, 6),
		[DATETIME] TIME(0),
		Speed TINYINT
	)

INSERT	@Sample
VALUES	( 1, NULL, NULL, '01:02:01', 28),
	( 2, NULL, NULL, '01:02:05', 60),
	( 3, NULL, NULL, '01:02:12', 68),
	( 4, NULL, NULL, '01:02:24', 65),
	( 5, NULL, NULL, '01:02:35', 30),
	( 6, NULL, NULL, '01:02:45', 35),
	( 7, NULL, NULL, '01:02:50', 40),
	( 8, NULL, NULL, '01:02:55', 70),
	( 9, NULL, NULL, '01:03:00', 75),
	(11, NULL, NULL, '01:03:05', 73),
	(12, NULL, NULL, '01:03:10', 80),
	(13, NULL, NULL, '01:03:15', 50),
	(14, NULL, NULL, '01:03:20', 49),
	(15, NULL, NULL, '01:03:25', 69),
	(16, NULL, NULL, '01:03:30', 69)

;WITH cte
AS (
	SELECT	ID,
		Lat,
		Lon,
		[DATETIME],
		Speed,
		ROW_NUMBER() OVER (ORDER BY ID) AS SeqID,
		ROW_NUMBER() OVER (PARTITION BY CASE WHEN Speed >= 50 THEN 1 ELSE 0 END ORDER BY ID) AS GrpID
	FROM	@Sample
)
SELECT		MIN([DATETIME]),
		MAX([DATETIME])
FROM		cte
GROUP BY	SeqID - GrpID
HAVING		MAX(Speed) >= 50
ORDER BY	SeqID - GrpID

SELECT		*,
		SeqID - GrpID AS Peso
FROM		cte
ORDER BY	ID