Largest Transaction(avoiding other transactions)

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)

Largest Transaction(avoiding other transactions)

Author

Topic

omega1983
Starting Member

40 Posts

Posted - 2010-06-17 : 10:49:28

SELECT giftid, Max(giftjntamt) AS MaxOfgiftjntamt,gifteffdat
FROM gifts_VIEW
WHERE (((gifteffdat)>= '7/1/2005')
AND ((giftacctdv)='5' Or (giftacctdv)='15'))
and giftid = '0000054529'
GROUP BY giftid,gifteffdat
ORDER BY giftid

I am trying to get the largest gift and the gifteffective date associated with that gift. I am okay when I leave out the gifteffective date. When I add the gifteffective date I get all dates in which the person gave. I only want one row, the largest amount and the gifteffective date associated with it. Any ideas on how I can rewrite my formula

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-17 : 11:06:55

[code]select a.giftid,a.MaxOfgiftjntamt,b.gifteffdat
from
(
select giftid, Max(giftjntamt) AS MaxOfgiftjntamt
FROM gifts_VIEW
WHERE gifteffdat > '20050701'
AND giftacctdv in ('5','15')
and giftid = '0000054529'
) a
inner join gifts_VIEW b on a.giftid = b.giftid and a.MaxOfgiftjntamt = b.giftjntamt[/code]

omega1983
Starting Member

40 Posts

Posted - 2010-06-17 : 11:47:44

When I run the query I get an error giftid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I inserted group by giftid and still get the error

quote:

Originally posted by vijayisonly

select a.giftid,a.MaxOfgiftjntamt,b.gifteffdat
from
(
select giftid, Max(giftjntamt) AS MaxOfgiftjntamt
FROM gifts_VIEW
WHERE gifteffdat > '20050701'
AND giftacctdv  in ('5','15')
and giftid = '0000054529'
) a
inner join gifts_VIEW b on a.giftid = b.giftid and a.MaxOfgiftjntamt = b.giftjntamt

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-17 : 13:36:27

quote:
Originally posted by omega1983

When I run the query I get an error giftid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I inserted group by giftid and still get the error

quote:
Originally posted by vijayisonly

select a.giftid,a.MaxOfgiftjntamt,b.gifteffdat
from
(
select giftid, Max(giftjntamt) AS MaxOfgiftjntamt
FROM gifts_VIEW
WHERE gifteffdat > '20050701'
AND giftacctdv in ('5','15')
and giftid = '0000054529'
) a
inner join gifts_VIEW b on a.giftid = b.giftid and a.MaxOfgiftjntamt = b.giftjntamt

Oops..it should be

select a.giftid,a.MaxOfgiftjntamt,b.gifteffdat
from
(
select giftid, Max(giftjntamt) AS MaxOfgiftjntamt
FROM gifts_VIEW
WHERE gifteffdat > '20050701'
AND giftacctdv  in ('5','15')
and giftid = '0000054529'
GROUP BY giftid
) a
inner join gifts_VIEW b on a.giftid = b.giftid and a.MaxOfgiftjntamt = b.giftjntamt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources