need assistance in setting up a cursor
-
Hi,
I need assistance in setting up a cursor to run in SQL Server 2000. The objective is fairly straight forward and, unfortunately, I'm new to writing cursors.
OBJECTIVE: I have two tables I've generated. The first (table1) lists the cumulative donations made by members from 10/1/2005 through 9/30/2006 along with two datetime columns that are currently NULL. Once column FY500 will display the date in which a member donated over $500 during the time period and the second column will display the date in which the member donated over $1000 during the same time period. The second table lists each time a member made a contribution during the same time period, which may have only been once or up to eight times. Note, the same members (memberIDs) are listed in both tables as a means of creating a join.
The cursor needs to read through the second table (table2) one record at a time performing a cumulative calculation of each donation made for each member. As soon as the cumulative amount for a member reaches $500 with a specific donation, then it needs to insert the data associated with the donation into the FY500 field for that member. It also needs to do the same thing as soon as a donatioin amount equals or exceeds $1000 for the FY1000 field. It is possible that a member may have donated $1000 or more all at once, which will then populate both the FY500 and FY1000 fields at the same time.
Any help or example you can provide would be greatly appreciated.
Thanks,
Grant
-
NO CURSORS!!!
UPDATE Table1
SET FY500 = DT.FY500 ,
FY1000 = DT.FY1000
FROM (
SELECT T1.memberID, T1.PeriodStart, T1.PeriodEnd,
case when SUM(T2.DonatedAmount) >= 500 then 1 else 0 end as FY500 ,
case when SUM(T2.DonatedAmount) >= 1000 then 1 else 0 end as FY1000
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.memberID = T2.memberID
AND T1.Period = T2.Period -- change it according to the way you handle the periods
-- e.g. T2.DonationDoneOnTime BETWEEN T1.PeriodStart AND T1.PeriodEnd
) DT
WHERE DT.memberID = Table1.memberID
AND DT.PeriodStart = Table1.PeriodStart AND DT.PeriodEnd = Table1.PeriodEnd
-- change it according to the way you handle the periods
_____________
Code for TallyGenerator -
I seem to read that you not only want the amounts but the dates these amounts were reached by each donor and I believe you're correct in the fact this is not easy to accomplish as a set operation.
Cursors have their place where nothing else will do, that's why they're included in Sql. That being said try the following general outline. P.S. You'll need to add the code that updates the first donor amount when the condition 'It is possible that a member may have donated $1000 or more all at once, which will then populate both the FY500 and FY1000 fields at the same time.' you spoke of occurs but tht should be trival.
Good Hunting!
declare @DonorID int, @DonationDate smalldatetime, @Donation money
declare DonationList cursor
for
select
DonorID,DonationDate,donation
from
DonationTbl
where
DonationDate
Between
@StartDonationDate
and
@EndDonationDate
order by
DonorID,DonationDate
Open DonationList
Fetch DonationList
into
@DonorID, @DonationDate, @Donation
While @@Fetch_Status=0
begin
if @DonorID<>@LastDonorID
begin
@Sum=@Donation
@LastDonorID=@DonorID
end
else
begin
@Sum=@Sum+@Donation
end
if @Sum >= @LevelOne
begin
Update Tbl set <A href="mailto
onationLevel1=@Sum">DonationLevel1 = @Sum, @DonationDateOne=@DoantionDatewhere
<A href="mailto
onorID=@DonorID">DonorID =@DonorIDend
if @Sum >= @LevelTwo
begin
Update Tbl set <A href="mailto
onationLevel2=@Sum">DonationLevel2=@Sum, @DonationDateTwo=@DoantionDatewhere
<A href="mailto
onorID=@DonorID">DonorID=@DonorIDend
Fetch DonationList
into
@DonorID, @DonationDate, @Donation
end
close DonationList
deacllocate DonationList
-
I appreciate everybody's very valuable help. The cursor is now working. I would have preferred to avoid using a cursor, but it became necessary to use one in order to prevent the date for either field from being updated as additional donations were made that exceeded $1000 and/or $500. For this reason, I had to use the cursor very similar to the one you provided Dan (thanks!)but with a nested check in each of the last two IF statements comparing the Levels. Otherwise, they would continue to update the dates and amounts as the same member continued to make donations.
Thanks again,
Grant
-
Sergiy do you have a cursorless solution for this one?
-
I would probably have a solution if somebody could explain me in plain words ("For dummies"
) what does it mean:"order to prevent the date for either field from being updated as additional donations were made that exceeded $1000 and/or $500"
_____________
Code for TallyGenerator -
{insert theme song from Jeopordy here}
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Probably with those dates you mean something like this:
SELECT T1.DonorId, T1.PeriodId, T1.[Date] as DateLimitReached
FROM Table1 T1
INNER JOIN (select DonorId, PeriodId, MAX(Date) as NotYet
from Table2 T
where T.Date < T1.Date
group by DonorId, PeriodId
HAVING SUM(DonationAmount) < 500
) T2 ON T2.DonorId = T1.DonorId and T2.PeriodId = T1.PeriodId
This will return list of periods for every donor when he/she reached the limit of $500.
For every donor only those periods when the limit was reached will be listed.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply