Update records used in Group By

Question

Update records used in Group By

tnpich

Ten Centuries

Points: 1223
More actions
February 22, 2022 at 10:27 pm

#3987130
I have this query to create a summary of donations
```
SELECT [DonorId], SUM([DonationAmount]) AS TotalDonation
FROM [dbo].[Donation] 
GROUP BY [DonorId], [DonationYear]
```
What I need to do is then create a single receipt for those records. The receipt creation process can be run at any time (not just at year end)
What method should be used so that the records in [dbo].[Donation] that were summed in that receipt get marked as receipted (Receipt # added to them?) I know there would need to be a WHERE clause added to exclude processed donations.
I know I could handle this in a stored procedure with cursors but is there a more elegant way?
Thanks in advance for all suggestions.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

Why are you grouping by DonationYear when you're not selecting it? You could get multiple summary rows w/o the context of DonationYear.

What are the business rules/algorithm for ReceiptNumber? Just an integer from a Sequence?

Insert summaries into a temp table (or perhaps a permanent Summary/Receipt table w/ update time for reporting) w/ Receipt Number.
Update dbo.Donation w/ ReceiptNumber from the summary table if needed (though this seems unnecessary if you use permanent Receipt/Summary table).
Select from the summary table if needed for receipt generation.

jmetape Right there with Babe Points: 731 More actions · Answer 2

perhaps this will help...

DROP TABLE if exists #batch 
create table #batch (batchID int IDENTITY(1,1), 
                    batchDate datetime2 Default(getDate()),                     
)

DROP TABLE if exists #batch_donation_match 
create table #batch_donation_match (batchID int, 
                    donationID int
)

DROP TABLE if exists ##receipt_detail
create table #receipt_detail (batchID int, donorID int, donationYear int, donationAmount decimal(18,2), receiptNum varchar(100)
)

DROP TABLE if exists #donation 
create table #donation (donationID int IDENTITY(1,1), donorID int, donationYear int, amount decimal(18,2), batchID int)


---INsert some data:
INSERT INTO #donation SELECT 456, 2022, 2000.50, -1
INSERT INTO #donation SELECT 789, 2022, 1000.50, -1
INSERT INTO #donation SELECT 789, 2022, 200.50, -1
INSERT INTO #donation SELECT 456, 2022, 125.50, -1


----HERE BEGINS THE SPROC 
declare @batchID int 
INSERT INTO #batch (batchDate) SELECT GEtDate()
SET @batchID = Scope_identity()

INSERT INTO #batch_donation_match
SELECT @batchID, donationID FROM #donation WHERE batchID = -1

INSERT into #receipt_detail
SELECT max(m.batchID), donorID, donationYear, Sum(amount), concat(donorID, ';', MAX(m.batchID)) /*THis is the receipt number--adjust as needed */
    FROM #donation d
    JOIN #batch_donation_match m on d.donationID = m.donationID and m.batchID = @batchID
GROUP BY donorID, donationYear

update d set batchID = @batchID
--SELECT *
FROM #donation d
    JOIN #batch_donation_match m on m.donationID = d.donationID and m.batchID = @batchID

---END THE SPROC

--Take a look at the data
SELECT * FROM  #donation
SELECT d.*, b.batchDate as receiptDate
 FROM #receipt_detail d 
    JOIN #batch b on b.batchID = d.batchID

--Input more records:
INSERT INTO #donation SELECT 345, 2022, 90.50, -1
INSERT INTO #donation SELECT 789, 2022, 6.50, -1
INSERT INTO #donation SELECT 789, 2022, 1.50, -1
INSERT INTO #donation SELECT 456, 2022, 125.50, -1 


--run the "sproc" again, look 
SELECT * FROM  #donation
SELECT d.*, b.batchDate as receiptDate
 FROM #receipt_detail d 
    JOIN #batch b on b.batchID = d.batchID

tnpich Ten Centuries Points: 1223 More actions · Answer 3

Ratbak,

I should be selecting DonationYear, just didn't think of it when I was doing some preliminary testing.

The Receipt # can be any sequence although likely will be DonationYear and a sequence number.

tnpich Ten Centuries Points: 1223 More actions · Answer 4

tnpich

Ten Centuries

Points: 1223

February 23, 2022 at 2:37 pm

#3987484

jmetape,

I think that will work very well.

Thanks

jmetape Right there with Babe Points: 731 More actions · Answer 5

Cool, glad I could help. I’ve certainly received quite a bit of help from this forum.

🤙🏼