Help woth stored procedure!

Hi,
Below is my stored procedure, for some reason it is not bringing back the
max contract start date although I have sopecified this. Any ideas would be
greatly appreciated. Will also include some data to show what I am trying
to do.
SELECT TOP 100 PERCENT dbo.tbl_referral_name.rn_id,
dbo.tbl_referral_name.rn_forename, dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_add.ra_add1,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_info.ri_closed, dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name,
MAX(dbo.tbl_support.s_contract_startdate) AS
Start_date, dbo.tbl_support_provider.sp_company
FROM dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id INNER JOIN
dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
dbo.tbl_officer.off_id INNER JOIN
dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
dbo.tbl_support_provider.sp_id
GROUP BY dbo.tbl_referral_info.ri_closed, dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name, dbo.tbl_support_provider.sp_company,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_add.ra_add1, dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_id
ORDER BY dbo.tbl_referral_name.rn_surname
Damon Smith, 41 Seven Oaks, Caerau, 0, , Alan Jones, 10/03/2003,
Homeless Team
Damon Smith, 41 Seven Oaks, Caerau, 0, CA, Alan Jones, 06/10/2003, Tai
Troth
There are also other records included in the list but I am trying to get the
maximum contract start date for any where there are more than one instance
like the example above.
Appreciate the help
Thanks
DamonWhat does "not bringing back the max contract start date" mean? The
wrong date? No date? An error message? It's hard to help you unless you
post some code that will actually reproduce the problem and explain
what result you expect. See the following article which explains the
best way to post your problem for the group:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hi,
Sorry about that. Basically when I include MAX on the contract_startdate
field it is having no effect on my results. What it should be doing is
bringing back the most recent contract_startdates if there is more than once
instance of a person and address but it is bringing back everything. i.e.
Damon Smith, 41 Seven Oaks, Caerau, 0, , Alan Jones, 10/03/2003,
Homeless Team
Damon Smith, 41 Seven Oaks, Caerau, 0, CA, Alan Jones, 06/10/2003, Tai
Troth - I want it to just bring this one back (most recent date) in all
cases where there is more than one instance of a person and address like
above.
Any ideas why this may be?
Damon
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108634350.485310.14970@.l41g2000cwc.googlegroups.com...
> What does "not bringing back the max contract start date" mean? The
> wrong date? No date? An error message? It's hard to help you unless you
> post some code that will actually reproduce the problem and explain
> what result you expect. See the following article which explains the
> best way to post your problem for the group:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||You need something like the following. PLease bear in mind I typed this
and have not tested it. You need to do a subselect in the where clause
of the main query to determine the ID and maximum start date then limit
the records in the main criteria based on the subselect.
You do not need a Group By clause in the main query. I have also
forgotten whether MS SQL Server allows multiple fields for subselects
as I have shown. If it does not then concatenate the two fields into
one. eg dbo.tbl_referral_name.rn_id +
dbo.tbl_support.s_contract_startdate
SELECT dbo.tbl_referral_name.rn_id,
dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_add.ra_add1,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_info.ri_closed,
dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name,
dbo.tbl_support.s_contract_startdate AS Start_date,
dbo.tbl_support_provider.sp_company
FROM dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id INNER JOIN
dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
dbo.tbl_officer.off_id INNER JOIN
dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
dbo.tbl_support_provider.sp_id
WHERE dbo.tbl_referral_name.rn_id, dbo.tbl_support.s_contract_startdate
in ( select dbo.tbl_referral_name.rn_id,
max(dbo.tbl_support.s_contract_startdate)
from dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id
group by dbo.tbl_referral_name.rn_id)
ORDER BY dbo.tbl_referral_name.rn_surname
Celtic Kiwi