Query Help

SQL Apprentice Question
I have the following table and data. What is the best way to get the
following results via query?


Brian 19991002 20000201
Brian 20000301 20000501
Charles 19961031 19990501


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[quiz1]([name] [varchar](50) NOT NULL, [start_dt]
[char](8) NOT NULL,
[end_dt] [char](8) NOT NULL, [rownum] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_quiz1] PRIMARY KEY CLUSTERED
( [name] ASC, [start_dt] ASC, [end_dt] ASC )
) ON [PRIMARY]


GO
SET ANSI_PADDING OFF
go


set nocount on
insert into quiz1(name, start_dt, end_dt) values('Brian', '19961001',
'19981230')
insert into quiz1(name, start_dt, end_dt) values('Brian', '19981230',
'19990605')
insert into quiz1(name, start_dt, end_dt) values('Brian', '19990605',
'19991002')
insert into quiz1(name, start_dt, end_dt) values('Brian', '20000201',
'20000301')
insert into quiz1(name, start_dt, end_dt) values('Brian', '20000501',
'99991231')
insert into quiz1(name, start_dt, end_dt) values('Charles', '19910106',
'19910731')
insert into quiz1(name, start_dt, end_dt) values('Charles', '19910731',
'19940201')
insert into quiz1(name, start_dt, end_dt) values('Charles', '19940201',
'19941021')
insert into quiz1(name, start_dt, end_dt) values('Charles', '19941021',
'19961031')
insert into quiz1(name, start_dt, end_dt) values('Charles', '19990501',
'20000331')
insert into quiz1(name, start_dt, end_dt) values('Charles', '20000331',
'99991231')
insert into quiz1(name, start_dt, end_dt) values('John', '19980103',
'19980727')
insert into quiz1(name, start_dt, end_dt) values('John', '19980727',
'20000103')
insert into quiz1(name, start_dt, end_dt) values('John', '20000103',
'20000601')
insert into quiz1(name, start_dt, end_dt) values('John', '20000601',
'99991231')
set nocount off
go


Celko Answers
First. we need to fix that REALLLLLLLLY bad schema:

IDENTITY cannot ever, ever, ever be a relational key!! You missed the
first day of RDBMS class. You never read the book! Duh!


CREATE TABLE Quiz1 -- an industry standard name?
(vague_over_sized_name VARCHAR(50) NOT NULL PRIMARY KEY, --lots of
Poles and Greeks?
start_dt DATETIME NOT NULL,
end_dt DATETIME NOT NULL,
CHECK (start_dt < end_dt)); -- true? You never stopped it!!



>> What is the best way to get the following results via query? <<


Do you work from specs this vague? If so, how????? Do you speak English
or a language that uses Latin alphabet? Posting DLL is good but not
enough; we need clear specs!!

My first guess, based on your vague posted, would be:


SELECT vague_over_sized_name, (MIN(start_dt) + INTERVAL 1 DAY) AS
start_dt, MAX(end_dt)
FROM Quiz1
GROUP BY vague_over_sized_name;

But we have no such specs. After a decade or so of doing
homework or someone's job for free,I would like the morons to learn to
write clear specs. That will get them into management or analysis.

If this is what she meant then we want to model the events , then we
need go back to SQL FOR SMARTIES and Snodgrass's work for more help.