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