sql to combine data from a table 1

[747576]

Hi,
I would like to know how to amalgamate interval data using t-sql. The code below creates a temp table and I would like a query to amalgamate the intervals grouping by the field CODE,

I can use the sql

select id,min(INVFROM),max(INVto),code from #temptable group by id,code

but this doesn't take into account the fact that the code value can be repeated down the interval sequence.

for example the result should be:

A1 0 2 A
A1 2 5 B
A1 5 8 C
A1 8 9 B

Thank you

DROP TABLE #TEMPTABLE

CREATE TABLE #TEMPTABLE (ID VARCHAR(10), INVFROM FLOAT, INVTO FLOAT, CODE VARCHAR(10))

INSERT INTO #TEMPTABLE VALUES('A1',0,1,'A')
INSERT INTO #TEMPTABLE VALUES('A1',1,2,'A')
INSERT INTO #TEMPTABLE VALUES('A1',2,3,'B')
INSERT INTO #TEMPTABLE VALUES('A1',3,4,'B')
INSERT INTO #TEMPTABLE VALUES('A1',4,5,'B')
INSERT INTO #TEMPTABLE VALUES('A1',5,6,'C')
INSERT INTO #TEMPTABLE VALUES('A1',6,7,'C')
INSERT INTO #TEMPTABLE VALUES('A1',7,8,'C')
INSERT INTO #TEMPTABLE VALUES('A1',8,9,'B')

SELECT ID, MIN(INVFROM), MAX(INVTO), CODE FROM #TEMPTABLE
GROUP BY ID,CODE

 

[simian336]

How is it supposed to distinguish between the fist B grouping from the second?

Simi

 

[RiverGuy]

SQL Server doesn't care about the order of rows in a table. So you have to let it know what constitutes your groups. Here's an example. Note that if you have many rows, it could be slow, and using while loops is normally a bad idea:

CREATE TABLE #TEMPTABLE (ID VARCHAR(10), INVFROM FLOAT, INVTO FLOAT, CODE VARCHAR(10))

INSERT INTO #TEMPTABLE VALUES('A1',0,1,'A')
INSERT INTO #TEMPTABLE VALUES('A1',1,2,'A')
INSERT INTO #TEMPTABLE VALUES('A1',2,3,'B')
INSERT INTO #TEMPTABLE VALUES('A1',3,4,'B')
INSERT INTO #TEMPTABLE VALUES('A1',4,5,'B')
INSERT INTO #TEMPTABLE VALUES('A1',5,6,'C')
INSERT INTO #TEMPTABLE VALUES('A1',6,7,'C')
INSERT INTO #TEMPTABLE VALUES('A1',7,8,'C')
INSERT INTO #TEMPTABLE VALUES('A1',8,9,'B')

ALTER TABLE #TEMPTABLE ADD BatchID INT
ALTER TABLE #TEMPTABLE ADD IDCol INT IDENTITY

DECLARE @TotalCount INT
SELECT @TotalCount = COUNT(*) FROM #TEMPTABLE 
DECLARE @Counter INT
SELECT @Counter = 1
DECLARE @BatchID INT
SELECT @BatchID = 0

DECLARE @OldCode CHAR(1)
DECLARE @CurrentCode CHAR(1)
SELECT @OldCode = ''
WHILE @Counter <= @TotalCount
BEGIN
	SELECT @CurrentCode = CODE FROM #TEMPTABLE WHERE IDCol = @Counter
	IF @CurrentCode <> @OldCode SELECT @BatchID = @BatchID + 1
	UPDATE #TEMPTABLE SET BatchID = @BatchID WHERE IDCol = @Counter
	SELECT @OldCode = CODE FROM #TEMPTABLE WHERE IDCol = @Counter
	SELECT @Counter = @Counter + 1
END




SELECT ID, MIN(INVFROM), MAX(INVTO), CODE, BatchID FROM #TEMPTABLE
GROUP BY ID,CODE, BatchID
ORDER BY BatchID

DROP TABLE #TEMPTABLE