Displaying the last transaction only in SQL2005

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)

Displaying the last transaction only in SQL2005

Author

Topic

omega1983
Starting Member

40 Posts

Posted - 2009-10-20 : 10:34:31

I have a query that displays gifts as follows
select sum(giftjntamt) as gifts
from gifts
where gifttype = 'y'
and giftacctdv = '20'
and giftjntamt >0
group by giftjntamt
order by gifteffdat desc

This query would give me all gifts sorted by descending order. For example

900
500
600

I want to show just the most recent gift only. For example I want to show the 900 only. How would I accomplish this?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-20 : 10:41:43

select top 1 gifts
from
(
select gifteffdat ,sum(giftjntamt) as gifts
from gifts
where gifttype = 'y'
and giftacctdv = '20'
and giftjntamt >0
group by giftjntamt
) a

Jim
order by gifteffdat desc

Everyday I learn something that somebody else already knew

omega1983
Starting Member

40 Posts

Posted - 2009-10-20 : 11:02:23

Jimf thanks, This gives me the top gift for giftacctdv 20. I want the most recent gift based on the gft effective date for each ID number. So for example if I have the following, I want to show the most recent gift for each ID
ID gift date
1 900 10/13/09
2 1000 9/23/08
3 500 8/15/09

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-20 : 11:16:01

If this isn't what you want, please give sample data and expected output. If you don't want giftacctdv = '20' as part of your query, you should remove it.

Jim

SELECT b.id
,sum(a.giftjntamt) as gifts
,b.Date
FROM
(
select id,date = max(gifteffdat)
from gifts
where gifttype = 'y'
and giftacctdv = '20'
and giftjntamt >0
) b
INNER JOIN
gifts a
ON
b.id = a.id
and b.Date = a.gufteffdat
GROUP BY
b.id,b.Date

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources