Hello all.
I have a table defined in sql server as follows:
ROW_ID (identity)
DEPTH_FROM Number (8,3)
DEPTH_TO Number (8,3)
COLOUR Char(10)
With typical data like:
ROW_ID DEPTH_FROM DEPTH_TO COLOUR
---------------------------------------------------------------
1 0 5
BLUE
2 5 8
BLUE
3 8 10
RED
4 10 12
GREEN
5 12 16
GREEN
I want to create a view that will 'compress/roll up' the data so
it appears like:
DEPTH_FROM DEPTH_TO COLOUR
---------------------------------------------------------
0 8 BLUE
8 10 RED
10 16 GREEN
I have been working on this for several days, with no luck,
any help would be appreciated. BTW: there are no overlaps
allowed in the depth_from, depth_to values.
Thanks in advance. 6 1553
I'll assume that the colours don't always occur at consecutive depths
otherwise you could just do this:
SELECT MIN(depth_from), MAX(depth_to), colour
FROM ColDepths
GROUP BY colour
Here's the (assumed) DDL and sample data. It helps if you include this with
posts.
CREATE TABLE ColDepths (row_id INTEGER NOT NULL UNIQUE, depth_from INTEGER
NOT NULL, depth_to INTEGER NOT NULL, colour CHAR(10) NOT NULL,
CHECK(depth_from<depth_to), PRIMARY KEY (depth_from,depth_to))
INSERT INTO ColDepths VALUES (1,0,5, 'BLUE')
INSERT INTO ColDepths VALUES (2,5,8, 'BLUE')
INSERT INTO ColDepths VALUES (3,8,10, 'RED')
INSERT INTO ColDepths VALUES (4,10,12, 'GREEN')
INSERT INTO ColDepths VALUES (5,12,16, 'GREEN')
Here's my query.
SELECT MIN(A.depth_from) AS depth_from,
MAX(A.depth_to) AS depth_to, A.colour
FROM ColDepths AS A
JOIN
(SELECT c1.row_id, MIN(C2.depth_to) AS next_depth
FROM ColDepths AS C1
LEFT JOIN ColDepths AS C2
ON C1.colour <> C2.colour
AND (C1.depth_from < C2.depth_from
OR (C1.depth_from = C2.depth_from)
AND C1.depth_to <= C2.depth_to)
GROUP BY c1.row_id) AS B
ON A.row_id = B.row_id
GROUP BY A.colour, B.next_depth
--
David Portas
------------
Please reply only to the newsgroup
--
Is this what you have in mind?
create table foo
(ROW_ID int, /* my datatypes vary from yours for my convenience
*/
DEPTH_FROM int,
DEPTH_TO int,
COLOUR Char(10))
go
insert foo values (1,0,5,'blue')
insert foo values (2,5,8,'blue')
insert foo values (3,8,10,'red')
insert foo values (4,10,12,'green')
insert foo values (5,12,16,'green')
go
select min(depth_from) as depth_from, max(depth_to) as depth_to, colour
from foo
group by colour
depth_from depth_to colour
----------- ----------- ----------
0 8 blue
10 16 green
8 10 red
(3 row(s) affected)
You may want an ORDER BY clause, too. Order of rows returned with GROUP BY
is not guaranteed/predictable.
"Dave Pylatuk" <da***@centurysystems.net> wrote in message
news:pZ*******************@news20.bellglobal.com.. . Hello all.
I have a table defined in sql server as follows:
ROW_ID (identity) DEPTH_FROM Number (8,3) DEPTH_TO Number (8,3) COLOUR Char(10)
With typical data like: ROW_ID DEPTH_FROM DEPTH_TO COLOUR --------------------------------------------------------------- 1 0 5 BLUE 2 5 8 BLUE 3 8 10 RED 4 10 12 GREEN 5 12 16 GREEN
I want to create a view that will 'compress/roll up' the data so it appears like:
DEPTH_FROM DEPTH_TO COLOUR --------------------------------------------------------- 0 8 BLUE 8 10 RED 10 16 GREEN
I have been working on this for several days, with no luck, any help would be appreciated. BTW: there are no overlaps allowed in the depth_from, depth_to values.
Thanks in advance.
"Dave Pylatuk" <da***@centurysystems.net> wrote... I want to create a view that will 'compress/roll up' the data so it appears like:
DEPTH_FROM DEPTH_TO COLOUR --------------------------------------------------------- 0 8 BLUE 8 10 RED 10 16 GREEN
I don't use SqlServer but... wouldn't this work?
select min(depth_from), max(depth_to), colour from <tablename> group by
colour
Testing these suggestions right now, thanks to all
"Dave Pylatuk" <da***@centurysystems.net> wrote in message
news:pZ*******************@news20.bellglobal.com.. . Hello all.
I have a table defined in sql server as follows:
ROW_ID (identity) DEPTH_FROM Number (8,3) DEPTH_TO Number (8,3) COLOUR Char(10)
With typical data like: ROW_ID DEPTH_FROM DEPTH_TO COLOUR --------------------------------------------------------------- 1 0 5 BLUE 2 5 8 BLUE 3 8 10 RED 4 10 12 GREEN 5 12 16 GREEN
I want to create a view that will 'compress/roll up' the data so it appears like:
DEPTH_FROM DEPTH_TO COLOUR --------------------------------------------------------- 0 8 BLUE 8 10 RED 10 16 GREEN
I have been working on this for several days, with no luck, any help would be appreciated. BTW: there are no overlaps allowed in the depth_from, depth_to values.
Thanks in advance.
Hi Dave,
If there is no overlap but there can be gaps between intervals, this is the
query you want:
select DEPTH_FROM,
DEPTH_TO = (select min(DEPTH_TO)
from T T3
where DEPTH_TO not in (select DEPTH_FROM
from T T4
where T3.COLOUR = T4.COLOUR
and T3.DEPTH_FROM <>
T4.DEPTH_FROM
)
and T3.DEPTH_TO > T1.DEPTH_FROM
),
COLOUR
from T T1
where DEPTH_FROM not in (select DEPTH_TO
from T T2
where T1.COLOUR = T2.COLOUR
and T1.DEPTH_FROM <> T2.DEPTH_FROM
)
order by DEPTH_FROM
Good Luck,
Shervin
"Dave Pylatuk" <da***@centurysystems.net> wrote in message
news:pZ*******************@news20.bellglobal.com.. . Hello all.
I have a table defined in sql server as follows:
ROW_ID (identity) DEPTH_FROM Number (8,3) DEPTH_TO Number (8,3) COLOUR Char(10)
With typical data like: ROW_ID DEPTH_FROM DEPTH_TO COLOUR --------------------------------------------------------------- 1 0 5 BLUE 2 5 8 BLUE 3 8 10 RED 4 10 12 GREEN 5 12 16 GREEN
I want to create a view that will 'compress/roll up' the data so it appears like:
DEPTH_FROM DEPTH_TO COLOUR --------------------------------------------------------- 0 8 BLUE 8 10 RED 10 16 GREEN
I have been working on this for several days, with no luck, any help would be appreciated. BTW: there are no overlaps allowed in the depth_from, depth_to values.
Thanks in advance.
"Dave Pylatuk" <da***@centurysystems.net> wrote in message
news:pZ*******************@news20.bellglobal.com.. . Hello all.
I have a table defined in sql server as follows:
ROW_ID (identity) DEPTH_FROM Number (8,3) DEPTH_TO Number (8,3) COLOUR Char(10)
With typical data like: ROW_ID DEPTH_FROM DEPTH_TO COLOUR --------------------------------------------------------------- 1 0 5 BLUE 2 5 8 BLUE 3 8 10 RED 4 10 12 GREEN 5 12 16 GREEN
I want to create a view that will 'compress/roll up' the data so it appears like:
DEPTH_FROM DEPTH_TO COLOUR --------------------------------------------------------- 0 8 BLUE 8 10 RED 10 16 GREEN
I have been working on this for several days, with no luck, any help would be appreciated. BTW: there are no overlaps allowed in the depth_from, depth_to values.
Thanks in advance.
This will also handle gaps between consecutive depth intervals.
CREATE TABLE ColorDepths
(
depth_from INT NOT NULL PRIMARY KEY,
depth_to INT NOT NULL,
color CHAR(10) NOT NULL,
CHECK (depth_from <= depth_to)
)
-- Your sample data augmented to better exercise code
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (0,5, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (5,8, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (8,10, 'RED')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (11,12, 'GREEN')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (12,15, 'GREEN')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (16, 18, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (18, 24, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (26, 30, 'BLUE')
-- Associate consecutive depth intervals with natural numbers
CREATE VIEW OrderedColorDepths (depth_from, depth_to, color, seq)
AS
SELECT D1.depth_from, D1.depth_to, D1.color, COUNT(*)
FROM ColorDepths AS D1
INNER JOIN
ColorDepths AS D2
ON D2.depth_from <= D1.depth_from
GROUP BY D1.depth_from, D1.depth_to, D1.color
-- Using above natural numbers, find endpoints
CREATE VIEW ColorDepthEnds (color, seq)
AS
SELECT OD1.color, OD1.seq
FROM OrderedColorDepths AS OD1
LEFT OUTER JOIN
OrderedColorDepths AS OD2
ON OD2.seq = OD1.seq + 1
WHERE OD2.color <> OD1.color OR -- consecutive depths w/ diff. colors
OD2.color IS NULL OR -- last (greatest) depth
OD2.depth_from > OD1.depth_to -- gap between consecutive depths
SELECT color,
MIN(depth_from) AS depth_from , MAX(depth_to) AS depth_to
FROM (SELECT OD.color, OD.depth_from, OD.depth_to,
MIN(DE.seq) AS seq
FROM OrderedColorDepths AS OD
INNER JOIN
ColorDepthEnds AS DE
ON DE.seq >= OD.seq AND
DE.color = OD.color
GROUP BY OD.depth_from, OD.depth_to, OD.color) AS R
GROUP BY seq, color
ORDER BY depth_from
color depth_from depth_to
BLUE 0 8
RED 8 10
GREEN 11 15
BLUE 16 24
BLUE 26 30
Regards,
jag This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Michele Simionato |
last post by:
I have read with interest the recent thread about closures. The funny
thing is that the authors are arguing one against the other but I
actually agree with all of them and I have a proposal that...
|
by: margraft |
last post by:
Hello,
I'm somewhat of a newbie and I need to create a view with a column
that is not derived from any other tables. What I want is for this
field to be an auto-increment field or some kind of...
|
by: B McDonald |
last post by:
http://www.galtsvalley.com
Hi all. I've recently made some major stylistic changes to my site and now
it is essentially a new design with some new CSS plumbing. I am hoping that
a few hardy...
|
by: Sunny |
last post by:
Hi,
I have cr8ed a javascript function in the head section of a jsp
page.
<!--
function go(theURL) {
alert(theURL);
if (theURL!=null){
|
by: Neil |
last post by:
I have an Access 2000 MDB with ODBC linked tables to a SQL Server 7 back
end. I currently have a selections table in the front end file which the
users use to make selections of records. The table...
| |
by: Chris Travers |
last post by:
Hi all;
I have been looking into how to ensure that synchronous replication, etc.
could best be implimented. To date, I see only two options: incorporate
the replication code into the database...
|
by: Marco Martin |
last post by:
Good Morning Group,
I've created an app that "runs in the background "(the window state is
minimized, the showintaskbar is false, and it displays a notify Icon.).
This program will be running on...
|
by: Sharon |
last post by:
I wish to build a framework for our developers that will include a singleton
pattern.
But it can not be a base class because it has a private constructor and
therefore can be inherit.
I thought...
|
by: senfo |
last post by:
I'm in the process of converting an ASP.NET 1.1 application to ASP.NET
2.0 and one of the things I did was to add a MasterPage. The original
1.1 application relies heavily on the Page.Request...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |