Getting last record in a set of records.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting last record in a set of records.

Author

Topic

mgiardino
Starting Member

11 Posts

Posted - 2008-09-12 : 15:01:03

I want to return the renewal date based on the last giftdate for each account. I'm pretty new to sql (2005). I thought using Max would get what I wanted. here's the code and results.
I'm looking for;

19 08-25-2005 10-01-2008 07-12-2008
120 12-21-2005 06-09-2008 12-12-2007
153 08-25-2005 09-10-2008 08-11-2008
164 11-29-2005 09-20-2008 08-29-2008

Using:

Select Distinct EDL.EntityId as AcctNbr, CONVERT(VARCHAR(10),EDL.AddDate, 110) as DateAdded,
CONVERT(VARCHAR(10), EDL.RenewalDate, 110) as RenewalDate, Max(CONVERT(VARCHAR(10), GHD.GDate, 110)) AS LastGiftDate

From EntityDistributionLists as EDL
inner join DistributionListCodes as DLC on EDL.DistributionListId = DLC.DistributionListCodeId
inner join GiftHeader as GHD on EDL.EntityId = GHD.EntityId

Where EDL.DistributionListStatusCodeId = '2' and EDL.RenewalDate < '11/1/2008'
and GHD.GDate > EDL.RenewalDate - 365

Group BY EDL.EntityId, EDL.Quantity, EDL.AddDate, EDL.RenewalDate, GHD.GDate

Order By EDL.EntityId

Returning this;

Acct DateAdded RenewalDate LastGiftDate
19 08-25-2005 10-01-2008 03-14-2008
19 08-25-2005 10-01-2008 05-19-2008
19 08-25-2005 10-01-2008 05-28-2008
19 08-25-2005 10-01-2008 07-12-2008
120 12-21-2005 06-09-2008 12-12-2007
153 08-25-2005 09-10-2008 01-10-2008
153 08-25-2005 09-10-2008 06-13-2008
153 08-25-2005 09-10-2008 08-11-2008
164 11-29-2005 09-20-2008 01-28-2008
164 11-29-2005 09-20-2008 02-19-2008
164 11-29-2005 09-20-2008 03-17-2008
164 11-29-2005 09-20-2008 04-14-2008
164 11-29-2005 09-20-2008 05-22-2008
164 11-29-2005 09-20-2008 06-25-2008
164 11-29-2005 09-20-2008 07-31-2008
164 11-29-2005 09-20-2008 08-29-2008
164 11-29-2005 09-20-2008 10-22-2007
164 11-29-2005 09-20-2008 11-28-2007
164 11-29-2005 09-20-2008 12-28-2007
301 08-25-2005 07-02-2008 06-05-2008
301 08-25-2005 07-02-2008 07-08-2008
301 08-25-2005 07-02-2008 12-31-2007

pommguest99
Starting Member

16 Posts

Posted - 2008-09-12 : 15:10:19

Try the following -

select a.AcctNbr, a.Dateadd, a.RenewalDate, max(a.lastgiftdate) as [ColumnThatYouWantToSee] from
(Select Distinct EDL.EntityId as AcctNbr, CONVERT(VARCHAR(10),EDL.AddDate, 110) as DateAdded,
CONVERT(VARCHAR(10), EDL.RenewalDate, 110) as RenewalDate, Max(CONVERT(VARCHAR(10), GHD.GDate, 110)) AS LastGiftDate
From EntityDistributionLists as EDL
inner join DistributionListCodes as DLC on EDL.DistributionListId = DLC.DistributionListCodeId
inner join GiftHeader as GHD on EDL.EntityId = GHD.EntityId
Where EDL.DistributionListStatusCodeId = '2' and EDL.RenewalDate < '11/1/2008'
and GHD.GDate > EDL.RenewalDate - 365
Group BY EDL.EntityId, EDL.Quantity, EDL.AddDate, EDL.RenewalDate, GHD.GDate)A
group by a.AcctNbr, a.Dateadd, a.RenewalDate
Order by a.AcctNbr

regards,
Anil.

mgiardino
Starting Member

11 Posts

Posted - 2008-09-12 : 15:31:09

Worked well, thanks for your help, I appreciate the quick response.

pommguest99
Starting Member

16 Posts

Posted - 2008-09-12 : 15:34:16

You are quite welcome !

regards,
Anil

mgiardino
Starting Member

11 Posts

Posted - 2008-09-12 : 16:41:22

I Spoke too soon.

select a.AcctNbr, a.Dateadded, a.RenewalDate, max(a.lastgiftdate) as [LastGiftDate] from
(Select Distinct EDL.EntityId as AcctNbr, CONVERT(VARCHAR(10),EDL.AddDate, 110) as DateAdded,
CONVERT(VARCHAR(10), EDL.RenewalDate, 110) as RenewalDate, Max(CONVERT(VARCHAR(10), GHD.GDate, 110)) AS LastGiftDate
From EntityDistributionLists as EDL
inner join DistributionListCodes as DLC on EDL.DistributionListId = DLC.DistributionListCodeId
inner join GiftHeader as GHD on EDL.EntityId = GHD.EntityId
Where EDL.DistributionListStatusCodeId = '2' and EDL.RenewalDate < @MyRenewalDate
and GHD.GDate < EDL.RenewalDate - 365 --StatusCodeId = '2' = ACTIVE
Group BY EDL.EntityId, EDL.Quantity, EDL.AddDate, EDL.RenewalDate, GHD.GDate)A
group by a.AcctNbr, a.Dateadded, a.RenewalDate
Order by a.AcctNbr

is returning;

19 08-25-2005 10-01-2008 12-15-2005
120 12-21-2005 06-09-2008 12-21-2005
153 08-25-2005 09-10-2008 12-14-2005
164 11-29-2005 09-20-2008 12-31-2006
301 08-25-2005 07-02-2008 12-31-2005

Not;

19 08-25-2005 10-01-2008 07-12-2008
120 12-21-2005 06-09-2008 12-12-2007
153 08-25-2005 09-10-2008 08-11-2008
164 11-29-2005 09-20-2008 08-29-2008

max(a.lastgiftdate) is not finding the last gift date.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-12 : 16:59:00

Is this what you are looking for?

Format the dates in the presentation layer


select   d.AcctNbr
         ,d.DateAdded
         ,d.RenewalDate
         ,d.LastGiftDate
from   (
       Select 
              EDL.EntityId as AcctNbr
              ,EDL.AddDate as DateAdded
              ,EDL.RenewalDate
              ,GHD.GDate AS LastGiftDate
              ,row_number() over (partition by EDL.EntityId order by GHD.GDate desc) as rn

       From   EntityDistributionLists as EDL 
       inner join DistributionListCodes as DLC on EDL.DistributionListId = DLC.DistributionListCodeId 
       inner join GiftHeader as GHD on EDL.EntityId = GHD.EntityId

       Where EDL.DistributionListStatusCodeId = '2' 
       and EDL.RenewalDate < '11/1/2008' 
       and GHD.GDate > EDL.RenewalDate - 365
       ) d
where  rn = 1

Be One with the Optimizer
TG

pommguest99
Starting Member

16 Posts

Posted - 2008-09-12 : 17:25:29

Im really not sure as to why it wouldnt fetch the maximum value of the giftdate using the max function.

regards,
Anil

mgiardino
Starting Member

11 Posts

Posted - 2008-09-12 : 17:41:46

Thanks TG.

Now that i have the correct results I'll be working on updating the RenewalDate to RenewalDate = LastGiftDate + 365

M

pommguest99
Starting Member

16 Posts

Posted - 2008-09-12 : 17:49:56

create table #tablec
(AcctNbr int, Dateadded datetime, RenewalDate datetime, lastgiftdate datetime)

insert into #tablec
values (112, '1/2/2007 11:59:59.99 PM', '6/4/2007 11:59:59.99 PM', '11/3/2007 11:59:59.99 PM')
insert into #tablec
values (112, '1/2/2007 11:59:59.99 PM', '6/4/2007 11:59:59.99 PM', '12/3/2007 11:59:59.99 PM')
insert into #tablec
values (112, '1/2/2007 11:59:59.99 PM', '6/4/2007 11:59:59.99 PM', '11/3/2007 11:59:59.99 PM')
insert into #tablec
values (122, '3/2/2007 11:59:59.99 PM', '8/4/2007 11:59:59.99 PM', '1/3/2007 11:59:59.99 PM')
insert into #tablec
values (122, '3/2/2007 11:59:59.99 PM', '8/4/2007 11:59:59.99 PM', '2/3/2007 11:59:59.99 PM')
insert into #tablec
values (122, '3/2/2007 11:59:59.99 PM', '8/4/2007 11:59:59.99 PM', '4/3/2007 11:59:59.99 PM')
insert into #tablec
values (1442, '4/2/2007 11:59:59.99 PM', '9/4/2007 11:59:59.99 PM', '1/3/2007 11:59:59.99 PM')
insert into #tablec
values (1442, '4/2/2007 11:59:59.99 PM', '9/4/2007 11:59:59.99 PM', '2/3/2007 11:59:59.99 PM')
insert into #tablec
values (1442, '4/2/2007 11:59:59.99 PM', '9/4/2007 11:59:59.99 PM', '2/13/2007 11:59:59.99 PM')
insert into #tablec
values (11266, '5/2/2007 11:59:59.99 PM', '10/4/2007 11:59:59.99 PM', '2/3/2007 11:59:59.99 PM')
insert into #tablec
values (11266, '5/2/2007 11:59:59.99 PM', '10/4/2007 11:59:59.99 PM', '2/5/2007 11:59:59.99 PM')

select * from #tablec order by 1, 4 desc

select a.AcctNbr, a.Dateadded, a.RenewalDate, max(a.lastgiftdate) as [LastGiftDate] from #tablec a
group by a.AcctNbr, a.Dateadded, a.RenewalDate

regards,
Anil

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-12 : 18:00:54

Anil,
I think the OP's issue is that they want (for each AcctNbr) the one row with the latest LastGiftDate.
It was a wild herring that the orig code grouped by the other columns and coincidental that the sample code had the same values for them.

Be One with the Optimizer
TG

pommguest99
Starting Member

16 Posts

Posted - 2008-09-12 : 18:06:20

Now, I Gotcha !!! Thanks a bunch TG.

regards,
Anil.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources