how to combine each record into a range?

  • Thread starter Thread starter pemt
  • Start date Start date
P

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
 
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);
 
Karl,

Thanks for your great help always!

pemt

KARL DEWEY said:
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.


pemt said:
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
Click to expand...
Click to expand...
 
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

KARL DEWEY said:
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.


pemt said:
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
Click to expand...
Click to expand...
 
Back
Top