latest date - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

latest date

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2010-02-24 : 07:14:35

i have this sql


SELECT DISTINCT MAX(Date_Invoice_Issued) 
  AS LastInvoice, 
  --Harvest_Fees, 
  RelationshipID
  FROM  Fee_Info_post_January_2002
  WHERE   (typeid= 95)AND (Cancelled = 2)
  GROUP BY 
  --Harvest_Fees, 
  RelationshipID

what i want to happen is that it shows fee for the lastest date for a regid but its pulling them all. any ideas on this

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-24 : 07:46:21

I tried with below sample data and i am getting only 1 output for each RelationshipId.

Declare @Test table
(RelationShipId int,
Date_Invoice_Issued datetime
)

Insert into @Test
Select 1 , '2009-12-01 14:55.30'

Insert into @Test
Select 1 , '2009-12-12 18:30.30'

Insert into @Test
Select 2 , '2009-11-01 14:40.30'

Insert into @Test
Select 2 , '2009-12-01 14:30.30'

select RelationShipId, max(Date_Invoice_Issued)
from @Test
group by RelationShipId

Can you post your table structure and some sample data ?

Regards,
Bohra

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2010-02-24 : 07:52:33

ya but i dont want to put the date in just want it to be pulled from table.

is there a way to do that
date structure is not great in this db. there isnt really one.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 07:55:54

quote:
Originally posted by rjhe22

ya but i dont want to put the date in just want it to be pulled from table.

is there a way to do that
date structure is not great in this db. there isnt really one.

What do you mean by that?
Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2010-02-24 : 08:00:09

when i use a bove code i get this


2007-07-24 00:00:00.000	2000.00	14232
2007-12-18 00:00:00.000	823.00	14252
2008-12-22 00:00:00.000	849.40	14252
2009-12-21 00:00:00.000	858.09	14252
2006-12-08 00:00:00.000	2000.00	14252

so for say regid 14252 i just want to get the figure for the newest date
2009-12-21 00:00:00.000 858.09 14252
and not the other 3

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 08:03:49

select * from your_table as t1
where Date_Invoice_Issued=(select max(Date_Invoice_Issued) from your_table where
RelationShipId=t1.RelationShipId)

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-24 : 09:08:28

The other way,please try


select RelationShipId,Date_Invoice_Issued from 
(
 select RelationshipID,Date_Invoice_Issued,seq=ROW_NUMBER()OVER(PARTITION BY RelationshipID ORDER BY [Date_Invoice_Issued] DESC) FROM Fee_Info_post_January_2002
  WHERE   (typeid= 95) AND (Cancelled = 2)

)t where t.seq=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources