Query Woes!

Query Help - Please
I have a table:
CREATE TABLE [dbo].[tblStudentOffers] (
[stud_no] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[crse_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[subj_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[adms_offer_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[adms_elmnt_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[crse_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[subj_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[start_study_date] [datetime] NULL ,
[comp_study_date] [datetime] NULL
) ON [PRIMARY]
GO
With Data:
stud_no,crse_offer_code,subj_offer_code,
adms_offer_code,adms_elmnt_code,crse
_code,subj_code,start_study_date,comp_st
udy_date
1,10,40,40,ELEMENT1,COURSE1,ELEMENT1,200
4-10-01,2004-12-31
1,10,41,41,ELEMENT2,COURSE1,ELEMENT2,200
4-10-01,2004-12-31
1,10,42,42,ELEMENT3,COURSE1,ELEMENT3,200
5-02-01,2005-03-31
1,30,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
1,30,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
1,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
2,40,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
2,40,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
2,40,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
3,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-02-01,2005-03-31
I need to provide a list of new course enrolments for the month of March? If
providing new student date range of 2005-03-01 to 2005-03-31 the query would
need to return the following:
stud_no,crse_offer_code,crse_code,start_
study_date
1,30,COURSE3,2005-03-01
2,40,COURSE3,2005-03-01
Have the following query which eliminates student 1 and only return student
2. Am now really stumped how to achieve this. HEEEEEEELPP
select distinct stud_no,crse_offer_code,crse_code,start_
study_date
from tblStudentOffers
where start_study_date BETWEEN CONVERT(DATETIME, '2005-03-01', 102) AND
CONVERT(DATETIME, '2005-03-31', 102)
AND stud_no not in
(SELECT stud_no FROM tblStudentOffers
WHERE start_study_date < CONVERT(DATETIME, '2005-03-01', 102))
ORDER BY crse_code, stud_family_name
Thanks
RosscoMy best guess is this:
select stud_no, crse_offer_code, crse_code, start_study_date
from tblStudentOffers as SO1
where not exists (
select * from tblStudentOffers as SO2
where SO2.stud_no = SO1.stud_no
and SO2.start_study_date <= SO1.start_study_date
and SO2.crse_offer_code = SO1.crse_offer_code
and SO2.adms_elmnt_code < SO1.adms_elmnt_code
)
where start_study_date >= '20050301'
and start_study_date <= '20050401'
The parts of the criteria that get the specific single row you want
for a particular student probably have to be adjusted.
Steve Kass
Drew University
Rossco wrote:

>Query Help - Please
>I have a table:
>CREATE TABLE [dbo].[tblStudentOffers] (
> [stud_no] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [crse_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
> [subj_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
> [adms_offer_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [adms_elmnt_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [crse_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [subj_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [start_study_date] [datetime] NULL ,
> [comp_study_date] [datetime] NULL
> ) ON [PRIMARY]
>GO
>
>With Data:
> stud_no,crse_offer_code,subj_offer_code,
adms_offer_code,adms_elmnt_code,crs
e_code,subj_code,start_study_date,comp_s
tudy_date
> 1,10,40,40,ELEMENT1,COURSE1,ELEMENT1,200
4-10-01,2004-12-31
> 1,10,41,41,ELEMENT2,COURSE1,ELEMENT2,200
4-10-01,2004-12-31
> 1,10,42,42,ELEMENT3,COURSE1,ELEMENT3,200
5-02-01,2005-03-31
> 1,30,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
> 1,30,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
> 1,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
> 2,40,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
> 2,40,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
> 2,40,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
> 3,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-02-01,2005-03-31
>I need to provide a list of new course enrolments for the month of March? I
f
>providing new student date range of 2005-03-01 to 2005-03-31 the query woul
d
>need to return the following:
> stud_no,crse_offer_code,crse_code,start_
study_date
>1,30,COURSE3,2005-03-01
>2,40,COURSE3,2005-03-01
>Have the following query which eliminates student 1 and only return student
>2. Am now really stumped how to achieve this. HEEEEEEELPP
>select distinct stud_no,crse_offer_code,crse_code,start_
study_date
>from tblStudentOffers
>where start_study_date BETWEEN CONVERT(DATETIME, '2005-03-01', 102) AND
>CONVERT(DATETIME, '2005-03-31', 102)
>AND stud_no not in
>(SELECT stud_no FROM tblStudentOffers
>WHERE start_study_date < CONVERT(DATETIME, '2005-03-01', 102))
>ORDER BY crse_code, stud_family_name
>Thanks
>Rossco
>|||Your blood is worth bottling! Thank you it works perfectly!
Cheers
"Steve Kass" wrote:

> My best guess is this:
> select stud_no, crse_offer_code, crse_code, start_study_date
> from tblStudentOffers as SO1
> where not exists (
> select * from tblStudentOffers as SO2
> where SO2.stud_no = SO1.stud_no
> and SO2.start_study_date <= SO1.start_study_date
> and SO2.crse_offer_code = SO1.crse_offer_code
> and SO2.adms_elmnt_code < SO1.adms_elmnt_code
> )
> where start_study_date >= '20050301'
> and start_study_date <= '20050401'
> The parts of the criteria that get the specific single row you want
> for a particular student probably have to be adjusted.
> Steve Kass
> Drew University
> Rossco wrote:
>
>|||Rossco wrote:
> Your blood is worth bottling! Thank you it works perfectly!
>
I think Steve is realy smart. Maybe even groovy. But I don't want his
blood. Angelina and Billy Bob ruined the whole bottled blood thing for
me.
David Gugick
Imceda Software
www.imceda.com|||Black pudding is quite nice.
http://www.g4cio.demon.co.uk/bpudding/pudding.htm
"David Gugick" wrote:

> Rossco wrote:
> I think Steve is realy smart. Maybe even groovy. But I don't want his
> blood. Angelina and Billy Bob ruined the whole bottled blood thing for
> me.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||In my neck of the woods it's called "boudin noir".
Although I find this sounds better than "blood pouding",
I still wouldn't touch it with... well, with anything.
"Peter 'Not Peter The Spate' Nolan"
<PeterNotPeterTheSpateNolan@.discussions.microsoft.com> wrote in message
news:2CC7355D-0E84-46A5-854E-6A5D34499C9B@.microsoft.com...
> Black pudding is quite nice.
> http://www.g4cio.demon.co.uk/bpudding/pudding.htm
>
> "David Gugick" wrote:
>sql

Query Syntax help

Hello All,

I have the following table:

CREATE TABLE [dbo].[TBL_NAME] (
[NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STANDARD_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

With values:

insert into tbl_name
values('DAN', 'DANIEL')
insert into tbl_name
values('DANNY', 'DANIEL')
insert into tbl_name
values('DANYY', 'DANIEL')

Question is:

I need want to construct a query which returns all names for a standard
name plus the standard name itself.
e.g.
if name = 'DAN' then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'
ff name = 'DANIEL', then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'

i have the following sql:

declare @.name varchar(50)
select @.name = 'DANIEL'
select standard_name from tbl_name where name = @.name
union
select name from tbl_name where standard_name = (select standard_name
from tbl_name where name = @.name)
union
select name from tbl_name where standard_name = @.name
union
select standard_name from tbl_name where standard_name = @.name

--

declare @.name varchar(50)
select @.name = 'DANNY'
select standard_name from tbl_name where name = @.name
union
select name from tbl_name where standard_name = (select standard_name
from tbl_name where name = @.name)
union
select name from tbl_name where standard_name = @.name
union
select standard_name from tbl_name where standard_name = @.name

--

Both appear to work fine..can anyone see a fault or suggest a cleaner
way to achieve the above ?

Suggestions/pointers appreciated
Thanks in advance1) How many people do you know or have ever heard of that have a name
that need to have CHAR(50)? The USPS allows CHAR(35)

2) Why did you violate common sense and ISO-11179 Standards with the
"tbI-" prefix?

3) Why don't you have a key? Why did you prevent having a key with
NULL_able? Why are you smarter than Dr. Codd?

4) If you knew SQL would this look like this:

CREATE TABLE FirstNames
(first_name VARCHAR (35) NOT NULL
CHECK (first_name = RTRIM(LTRIM(first_name))),
alternate_first_name VARCHAR (35) NOT NULL
CHECK (alternate_first_name = RTRIM(LTRIM(alternate_first_name))),
PRIMARY KEY (first_name, alternate_first_name)
);

>> I need want to construct a query which returns all names for a standard name plus the standard name itself. <<

SELECT first_name, alternate_first_name
FROM FirstNames
WHERE first_name = @.my_guy;|||hharry (paulquigley@.nyc.com) writes:
> CREATE TABLE [dbo].[TBL_NAME] (
> [NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [STANDARD_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> With values:
> insert into tbl_name
> values('DAN', 'DANIEL')
> insert into tbl_name
> values('DANNY', 'DANIEL')
> insert into tbl_name
> values('DANYY', 'DANIEL')
> Question is:
> I need want to construct a query which returns all names for a standard
> name plus the standard name itself.
> e.g.
> if name = 'DAN' then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'
> ff name = 'DANIEL', then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'
>...

If you add a row with (DANIEL, DANIEL), you can write a much simpler query:

insert into tbl_name
values('DAN', 'DANIEL')
insert into tbl_name
values('DANNY', 'DANIEL')
insert into tbl_name
values('DANYY', 'DANIEL')
insert into tbl_name
values('DANIEL', 'DANIEL')
go
DECLARE @.name varchar(50)
SELECT @.name = 'DANIEL'
SELECT name
FROM tbl_name t1
WHERE EXISTS (SELECT name, standard_name
FROM tbl_name t2
WHERE t2.standard_name = t1.standard_name
AND t2.name = @.name)
go

If this change is not feasible or possible, you could write:

SELECT t1.name
FROM (SELECT name, standard_name
FROM tbl_name
UNION
SELECT standard_name, standard_name
FROM tbl_name) t1
WHERE EXISTS (SELECT name, standard_name
FROM (SELECT name, standard_name
FROM tbl_name
UNION
SELECT standard_name, standard_name
FROM tbl_name) t2
WHERE t2.standard_name = t1.standard_name
AND t2.name = @.name)

But that's certainly a little more complex, and whether it's cleaner
your current query is a matter of taste.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||--CELKO-- (jcelko212@.earthlink.net) writes:
> 1) How many people do you know or have ever heard of that have a name
> that need to have CHAR(50)? The USPS allows CHAR(35)
> 2) Why did you violate common sense and ISO-11179 Standards with the
> "tbI-" prefix?
> 3) Why don't you have a key? Why did you prevent having a key with
> NULL_able? Why are you smarter than Dr. Codd?
> 4) If you knew SQL would this look like this:
> CREATE TABLE FirstNames
> (first_name VARCHAR (35) NOT NULL
> CHECK (first_name = RTRIM(LTRIM(first_name))),
> alternate_first_name VARCHAR (35) NOT NULL
> CHECK (alternate_first_name = RTRIM(LTRIM(alternate_first_name))),
> PRIMARY KEY (first_name, alternate_first_name)
> );
>>> I need want to construct a query which returns all names for a standard
name plus the standard name itself. <<
> SELECT first_name, alternate_first_name
> FROM FirstNames
> WHERE first_name = @.my_guy;

I don't know don't if "hharry" is smarter than Codd, but he is
obviously smarter than you. After all, he was able to write a query
that solved his problem - you weren't. (Since hharry supplied tables
and insert statements, you could have tested.)

As for your points 1-3, they are completely irrelevant and not the least
helpful. Just impolite and unfriendly. My guess is that hharry's real
business problem is different, and the table he posted he just a
throwaway table to demonstrate the SQL problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp