how to combine each record into a range?

[pemt]

how to combine each record into a range?
Table1
Day Value
1 0
2 0
3 0
4 1
5 1
6 1
7 0
8 0
9 0
10 0
11 1
12 1
13 0
14 1
15 0
16 0
17 1
18 1
19 0
20 1
.. .
.. .
.. .
1000 1

how to covert the above table into:
DayStart DayEnd Value
1 3 0
4 6 1
7 10 0
11 12 1
13 13 0
14 14 1
15 16 0
17 18 1
19 19 0
....

thanks,

pemt

 

[KARL DEWEY]

Use these two queries --
pemt_1 --
SELECT Q.Day, Q.Value, (SELECT COUNT(*) FROM [pemt] Q1
WHERE Q1.[Day] > Q.[Day]
AND Q1.[Value] <> Q.[Value] )+1 AS Rank
FROM pemt AS Q
ORDER BY Q.Day;

SELECT Min(pemt_1.Day) AS DayStart, Max(pemt_1_1.Day) AS DayEnd, pemt_1.Value
FROM pemt_1 INNER JOIN pemt_1 AS pemt_1_1 ON (pemt_1.Rank = pemt_1_1.Rank)
AND (pemt_1.Value = pemt_1_1.Value)
GROUP BY pemt_1.Value, pemt_1.Rank
ORDER BY Min(pemt_1.Day);

 

[pemt]

Karl,

Thanks for your great help always!

pemt

Use these two queries --
pemt_1 --
SELECT Q.Day, Q.Value, (SELECT COUNT(*) FROM [pemt] Q1
WHERE Q1.[Day] > Q.[Day]
AND Q1.[Value] <> Q.[Value] )+1 AS Rank
FROM pemt AS Q
ORDER BY Q.Day;

SELECT Min(pemt_1.Day) AS DayStart, Max(pemt_1_1.Day) AS DayEnd, pemt_1.Value
FROM pemt_1 INNER JOIN pemt_1 AS pemt_1_1 ON (pemt_1.Rank = pemt_1_1.Rank)
AND (pemt_1.Value = pemt_1_1.Value)
GROUP BY pemt_1.Value, pemt_1.Rank
ORDER BY Min(pemt_1.Day);

--
Build a little, test a little.

how to combine each record into a range?
Table1
Day Value
1 0
2 0
3 0
4 1
5 1
6 1
7 0
8 0
9 0
10 0
11 1
12 1
13 0
14 1
15 0
16 0
17 1
18 1
19 0
20 1
. .
. .
. .
1000 1

how to covert the above table into:
DayStart DayEnd Value
1 3 0
4 6 1
7 10 0
11 12 1
13 13 0
14 14 1
15 16 0
17 18 1
19 19 0
...

thanks,

pemt

 

[pemt]

Hi Karl,

One more question: this code run very slow when there are over 1000 ranges,
is it possible to run every 20 or 100 ranges and combine all ranges finally?
how to do that?
Thanks,

pemt

Use these two queries --
pemt_1 --
SELECT Q.Day, Q.Value, (SELECT COUNT(*) FROM [pemt] Q1
WHERE Q1.[Day] > Q.[Day]
AND Q1.[Value] <> Q.[Value] )+1 AS Rank
FROM pemt AS Q
ORDER BY Q.Day;

SELECT Min(pemt_1.Day) AS DayStart, Max(pemt_1_1.Day) AS DayEnd, pemt_1.Value
FROM pemt_1 INNER JOIN pemt_1 AS pemt_1_1 ON (pemt_1.Rank = pemt_1_1.Rank)
AND (pemt_1.Value = pemt_1_1.Value)
GROUP BY pemt_1.Value, pemt_1.Rank
ORDER BY Min(pemt_1.Day);

--
Build a little, test a little.

how to combine each record into a range?
Table1
Day Value
1 0
2 0
3 0
4 1
5 1
6 1
7 0
8 0
9 0
10 0
11 1
12 1
13 0
14 1
15 0
16 0
17 1
18 1
19 0
20 1
. .
. .
. .
1000 1

how to covert the above table into:
DayStart DayEnd Value
1 3 0
4 6 1
7 10 0
11 12 1
13 13 0
14 14 1
15 16 0
17 18 1
19 19 0
...

thanks,

pemt