Skip to content

Instantly share code, notes, and snippets.

@tinaarnoldi

tinaarnoldi/SQL Grantedge Payment amount vs Grant amount

Created July 9, 2012 16:40
Show Gist options
  • Save tinaarnoldi/3077503 to your computer and use it in GitHub Desktop.
Save tinaarnoldi/3077503 to your computer and use it in GitHub Desktop.
Download ZIP
SQL Grantedge Payment amount vs Grant amount
Raw
SQL Grantedge Payment amount vs Grant amount
SELECT DISTINCT
V_GRRPT_PAYMENTS.PayDate AS GrantDate, V_GRRPT_Grants.RecipientName, V_GRRPT_Grants.PayeeName, V_GRRPT_Grants.GrantType,
V_GRRPT_Grants.GrantStatus, V_GRRPT_Grants.PaymentAmount AS GrantAmount, V_GRRPT_PAYMENTS.PayDate AS DatePaid,
V_GRRPT_PAYMENTS.TotalAmount AS PaymentAmount, V_GRRPT_PAYMENTS.PayStatus, V_GRRPT_PAYMENTS.CheckNumber, V_GRRPT_PAYMENTS.CheckDate,
V_GRRPT_PAYMENTS.AdjustmentAmount, V_GRRPT_CCF_GrantCharacteristics_TOPLEVELPROGRAM.TopLevelProgramArea, V_GRRPT_PAYMENTS.FundNumber,
V_GRRPT_PAYMENTS.FundName, V_GRRPT_PAYMENTS.GrantId
FROM
V_GRRPT_CCF_GrantCharacteristics_TOPLEVELPROGRAM RIGHT OUTER JOIN
V_GRRPT_PAYMENTS ON V_GRRPT_CCF_GrantCharacteristics_TOPLEVELPROGRAM.GrantId = V_GRRPT_PAYMENTS.GrantId LEFT OUTER JOIN
V_GRRPT_Grants ON V_GRRPT_PAYMENTS.GrantId = V_GRRPT_Grants.GrantId
WHERE
(V_GRRPT_PAYMENTS.PayStatus = N'paid' OR
V_GRRPT_PAYMENTS.PayStatus = N'processed')
AND (V_GRRPT_PAYMENTS.CheckDate BETWEEN @StartDate AND @EndDate)
AND (V_GRRPT_PAYMENTS.FundNumber = @FundNumber)
@tinaarnoldi
Copy link
Author

Added a column with the below expression. If the grant amount is higher than the payment amount, that means it's a multi-payment grant and "Yes" will be added to the new column. If it is not higher, meaning it's only one payment for this grant, the new column will not have anything in it.

=iif ((Fields!GrantAmount.Value > Fields!PaymentAmount.Value), "Yes", "")

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment