Created
July 9, 2012 16:34
-
-
Save tinaarnoldi/3077485 to your computer and use it in GitHub Desktop.
SQL Grantedge Grants by Geo Area for previous week
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT DISTINCT | |
| V_GRRPT_Grants.GrantId, V_GRRPT_Grants.PayDate AS GrantDate, V_GRRPT_Grants.FundNumber, V_GRRPT_Grants.FundName, V_GRRPT_Grants.RecipientName, | |
| 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_GRQRY_GrantCharacteristics.Name, | |
| V_GRQRY_GrantCharacteristics.CodeValue AS Expr2 | |
| FROM | |
| V_GRRPT_Grants INNER JOIN | |
| V_GRRPT_PAYMENTS ON V_GRRPT_PAYMENTS.GrantId = V_GRRPT_Grants.GrantId LEFT OUTER JOIN | |
| V_GRQRY_GrantCharacteristics ON V_GRQRY_GrantCharacteristics.GrantId = V_GRRPT_PAYMENTS.GrantId LEFT OUTER JOIN | |
| V_GRRPT_CCF_GrantCharacteristics_TOPLEVELPROGRAM ON | |
| V_GRQRY_GrantCharacteristics.GrantId = V_GRRPT_CCF_GrantCharacteristics_TOPLEVELPROGRAM.GrantId | |
| WHERE (V_GRRPT_Grants.GrantType <> 't') AND (V_GRRPT_PAYMENTS.PayStatus = N'paid' OR V_GRRPT_PAYMENTS.PayStatus = N'processed') | |
| AND | |
| (V_GRRPT_Grants.GrantStatus = 'processed' OR | |
| V_GRRPT_Grants.GrantStatus = 'paid') | |
| AND (V_GRQRY_GrantCharacteristics.Name LIKE '%geo%') AND (V_GRRPT_PAYMENTS.CheckDate >= @Start) | |
| ORDER BY Expr2, V_GRRPT_CCF_GrantCharacteristics_TOPLEVELPROGRAM.TopLevelProgramArea, V_GRRPT_Grants.FundNumber, V_GRRPT_Grants.RecipientName, | |
| V_GRRPT_PAYMENTS.CheckNumber |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Specify paid or processed (or exclude voids with "<> void")
LIKE %geo% - like parameter to pull characteristic "Geographical Area"