select BG.UserGiftId, GF.UserGiftId, GF.GiftSubType, GF.ID, *
from BATCHGIFT BG
inner join GIFT GF on GF.USERGIFTID = BG.USERGIFTID
left outer join BATCHHEADER BH on BG.BatchHeaderID = BH.BATCHID
where BH.BATCHNUMBER = '40' -- specific batch number
Non-Matching Records (Gifts that exist in the Batch but not in the Gift table)
select BH.BatchNumber, BG.UserGiftId, BG.Amount, BG.ConstitName, BG.Constit_Id, BG.Constituent_Code,
WHEN 1 THEN 'Active'
WHEN 2 THEN 'Held'
WHEN 3 THEN 'Terminated'
WHEN 4 THEN 'Completed'
WHEN 5 THEN 'Cancelled'
END as 'Gift Status', convert(varchar, BG.Dte, 101) as 'Gift Date', convert(varchar, BG.Post_Date, 101) as 'Post Date',
WHEN 1 THEN 'Posted'
WHEN 2 THEN 'Not Posted'
WHEN 3 THEN 'Do No Post'
END AS 'Post Status'
from BatchGift BG
inner join BATCHHEADER BH on BG.BatchHeaderID = BH.BATCHID
where BG.UserGiftId is not NULL and
BG.UserGiftId NOT IN
FROM GIFT gf where gf.UserGiftId is not NULL)
Disclaimer: We provide programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Our Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.