Stored proceedure for giving issue 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)

Stored proceedure for giving issue SQL2005

Author

Topic

omega1983
Starting Member

40 Posts

Posted - 2009-10-31 : 16:37:34

I have the following code that shows a date range for giving
ALter Proceedure cash
@begindate
@enddate

as
select giftamount, giftid, gifteffdate
from cash.gifts
where gifttype = g
and (gifteffdate >= @begindate and gifteffdate <= @enddate)

I know want to show an indicator of 'Y', preferably in the same stored proceedure if this is a first time gift. Essentially for this indicator if the person never made a gift prior to the @begindate I want to show a 'Y'. Can I do this this through a union statement or must I use a separate proceedure or view and link it to the enclosed proceedure?
Sample OutPut. In this case ID#2 never gave money prior to 2/15/2009
FirstTime ID Giftamount Gifteffdat
Y 2 55 2/15/2009
6 155 2/15/2009

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-31 : 18:49:34

select g.giftamount, g.giftid, g.gifteffdate
,[FirstGift] = CASE
WHEN t1.FirstGift >= @begindate
THEN 'Y' ELSE 'N'
END
from

(select giftid,[FirstGift] = min(gifteffdate)
from gifts
group by giftid) t1
INNER JOIN
cash.gifts g
ON
t1.giftid = g.giftid
where g.gifttype = g
and (g.gifteffdate >= @begindate and g.gifteffdate <= @enddate)

Jim

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