Help on grouping

• Mohan Mariyappa

  Old Hand

  Points: 327

  More actions

  September 19, 2006 at 5:34 pm

  #170279

  Hi,

   

  I have table ABC with following values in it.

  RecID - Identity: Record ID

  MemNo - Char(20): Membership Number

  DateCalled-DateTime: Dated called

  Here, for a given MemNo, there can be more than one record for a given date. A typical days record will look like below.

  1,00001,10/09/2006:10:00AM

  2,00001,10/09/2006:10:30AM

  3,00002,10/09/2006:10:01AM

  4,00001,10/09/2006:10:45AM

   

  Now, what I need to select is, for every MemNo, only the last called record. In other words, I need to select every Membership number for a given day of the only the last called record.

   

  Please help me. Thanks in advance.

• Gopi Muluka

  SSCarpal Tunnel

  Points: 4594

  More actions

  September 19, 2006 at 5:53 pm

  #661387

  try this

  select a.RecID,a.MemNo,a.DateCalled from ABC a inner join

  (select MemNo,max(DateCalled) as maxdate from ABC

   Group by MemNo) b

  on a.MemNo=b.MemNo and a.DateCalled=b.maxdate

• Gopi Muluka

  SSCarpal Tunnel

  Points: 4594

  More actions

  September 19, 2006 at 6:05 pm

  #661390

  you can add Givendate to where clause like this

  Declare @Givendate datetime

  set @GivenDate='10/09/2006'

  select a.RecID,a.MemNo,a.DateCalled from grTest a inner join

  (select MemNo,max(DateCalled) as maxdate from grTest

   where convert(varchar,DateCalled,101)=convert(varchar,@GivenDate,101)

   Group by MemNo) b

  on a.MemNo=b.MemNo and a.DateCalled=b.maxdate

• Mohan Mariyappa

  Old Hand

  Points: 327

  More actions

  September 19, 2006 at 6:11 pm

  #661391

  Thanks a lot Gopi

   

  Its working perfect.