Displaying First Time transaction

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 First Time transaction

Author

Topic

omega1983
Starting Member

40 Posts

Posted - 2009-10-20 : 12:29:50

I have a stored procedure as follows:
ALTER PROCEDURE [Cash_Export]
-- Add the parameters for the stored procedure here
@BeginDate datetime,
@EndDate datetime,
@Division varchar(50)
AS
SELECT
case when gifteffdat <@BeginDate then 'Y'
else 'N' end as FIRST_TIME_DONOR,
giftid,gifteffdat,divsion
from gifts
where
(gifteffdat >= @BeginDate and gifteffdat <= @EndDate)
and giftdv = @Division

I am trying to insert a 'Y' if the the individual is giving for the first time. Here is some sample data
FIRSTTIMEDONOR,giftid,gifteffdat,divsion
N 1 1/1/2009 Business
N 2 5/15/2009 Engineering
Lets say ID#2 Engineering is giving for the first time, I want a 'Y' to appear under FIRSTTIMEDONOR. Since I am pulling only the @BeginDate and @EndDate, I would miss any first time gifts. Is there a way to show first time gifts in this example I have given

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-20 : 13:02:05

[code]
ALTER PROCEDURE [Cash_Export]
-- Add the parameters for the stored procedure here
@BeginDate datetime,
@EndDate datetime,
@Division varchar(50)
AS
SELECT
case when giftbefore=0 then 'Y'
else 'N' end as FIRST_TIME_DONOR,
giftid,gifteffdat,divsion
from gifts g
cross apply (select count(*) as giftbefore
from gifts
where division= g.division
and gifteffdat< g.gifteffdat) g1
where
(gifteffdat >= @BeginDate and gifteffdat <= @EndDate)
and giftdv = @Division
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources