Max Date only per month 1

[jgurgen]

i have a table where i need to select the Status from the MAX(DateAdded) for each month for each loan.

Table [AdditionalStatus] Structure
LoanID
DateAdded
Status

EXAMPLE
001 2/15/06 ABC
001 2/17/06 GHI
002 2/18/06 ABC

in the above example the first one would not be selected because the max date for loan 001 is on 2/17/06. I hope this is clear enough.

My current SQL statement.
Select * from AdditionalStatus order by LoanID, DateAdded

 

[ptheriault]

Select max(dateadded)
FROM
GROUP BY month(dateadded)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Here is my example:

DECLARE @tbl Table(id char(3)
                   ,date datetime)

INSERT INTO @tbl VALUES('001', '2/15/2006')
INSERT INTO @tbl VALUES('001', '2/17/2006')
INSERT INTO @tbl VALUES('002', '4/15/2007')
INSERT INTO @tbl VALUES('003', '2/15/2005')

select id,
       max(date)
from @tbl
group by id,month(date)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[bborissov]

SELECT AdditionalStatus.LoanID,
       AdditionalStatus.DateAdded,
       AdditionalStatus.Status
FROM AdditionalStatus
INNER JOIN (SELECT LoanID,
                   MAX(DateAdded) AS DateAdded
            FROM AdditionalStatus
            GROUP BY LoanId,
                     YEAR(DateAdded),
                     MONTH(DateAdded)) Tbl1
ON AdditionalStatus.LoanId    = Tbl1.LoanID AND
   AdditionalStatus.DateAdded = Tbl1.DateAdded

not tested


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[tran008]

declare @tbl Table (LoanID int, DateAdded datetime,Status varchar(10))

insert into @tbl select 001, '2/15/06', 'ABC' union all
select 001, '2/17/06', 'GHI' union all
select 001, '12/15/06', 'ABC' union all
select 001, '5/17/06', 'GHI' union all
select 002, '12/18/06', 'ABC'


select loanid, dateadded, status from @tbl where
dateadded in (select max(dateadded) from @tbl group by month(dateadded))

 

[ESquared]

tran008, that won't work at all. Running your query as is returns only 3 rows when the correct answer is four rows. It leaves out 001,12/15/06 because another loanid has a higher day value in december.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]