probably-bad-sql.sql

DECLARE @FundId int = 180;
DECLARE @today AS DATE = GETDATE();
DECLARE @reportEndDate AS DATE = CONVERT(DATE, DATEADD(DAY, 1 - DATEPART(WEEKDAY, @today), @today));
DECLARE @reportStartDate AS DATE = DATEADD(YEAR, -4, @reportEndDate);

IF ISDATE(@StartDate) = 1 AND ISDATE(@EndDate) = 1
BEGIN
    SELECT @reportEndDate = @EndDate;
    SELECT @reportStartDate = @StartDate;
END

DECLARE @familyType AS INT = (SELECT Id FROM [GroupType] WHERE Name = 'Family' AND IsSystem = 1);
DECLARE @adultRole AS INT = (SELECT Id FROM GroupTypeRole WHERE GroupTypeId = @familyType AND Name = 'Adult');

SELECT
    Family.Id as Id,
    Family.Name as Family,
    Family.CampusId as CampusId,
    SUM(Pledge.TotalAmount) as PledgeAmount
INTO #Pledges
FROM [Group] AS Family
    JOIN [GroupMember] as GM ON Family.Id = GM.GroupId
    JOIN [Person] AS P ON P.Id = GM.PersonId
    JOIN [PersonAlias] AS PA ON PA.PersonId = P.Id
    JOIN [FinancialPledge] AS Pledge ON Pledge.PersonAliasId = PA.Id
  WHERE
      Family.GroupTypeId = @familyType AND 
      Pledge.AccountId IN (
        SELECT
            Account.Id AS Id
        FROM [FinancialAccount] AS Account
        WHERE Account.Id = @FundId OR Account.ParentAccountId = @FundId
      )
GROUP BY
    Family.Id,
    Family.Name,
    Family.CampusId

SELECT
    gg.Id as Id,
    Leader.Id as PersonId,
    DV.Value as LeaderStatus,
    SUM(Details.Amount) as AmountGiven
INTO #Transactions
FROM [FinancialTransactionDetail] AS Details
    JOIN [FinancialTransaction] AS Fts ON Fts.Id = Details.TransactionId
    JOIN [PersonAlias] AS PA ON Fts.AuthorizedPersonAliasId = PA.Id
    JOIN [GroupMember] AS GM ON GM.PersonId = PA.PersonId
    JOIN [Group] AS Family ON Family.Id = GM.GroupId
    JOIN [Person] AS P ON PA.PersonId = P.Id
    JOIN (
        SELECT
            DISTINCT p.GivingGroupId AS Id
        FROM
            Person AS p
            JOIN GroupMember AS gm ON gm.PersonId = p.Id
    ) AS gg ON gg.Id = P.GivingGroupId
    JOIN [Person] AS Leader ON Leader.GivingGroupId = gg.Id AND Leader.GivingLeaderId = Leader.Id
    JOIN [DefinedValue] AS DV ON Leader.ConnectionStatusValueId = DV.Id AND DV.DefinedTypeId = 4
WHERE
    Fts.TransactionDateTime >= @reportStartDate AND
    Fts.TransactionDateTime <= @reportEndDate AND
    Family.GroupTypeId = @familyType AND
    Details.AccountId IN (
        SELECT
            Account.Id AS Id
        FROM [FinancialAccount] AS Account
        WHERE Account.Id = @FundId OR Account.ParentAccountId = @FundId
      )
GROUP BY
    gg.Id,
    Leader.Id,
    Leader.LastName,
    DV.Value

SELECT
  T.PersonId as LeaderId,
  P.Id as 'Household ID',
  P.Family as 'Household Name',
  T.LeaderStatus as 'Head Status',
  C.Name as 'Head Substatus',
  CONCAT(House.Street1, CHAR(13) + CHAR(10), House.City, ', ', House.State, ' ', House.PostalCode) as 'Full Address',
  P.PledgeAmount as 'Pledge Amount',
  T.AmountGiven as 'Contributed Amount'
FROM #Pledges as P
    JOIN #Transactions as T ON T.Id = P.Id
    JOIN [Campus] as C ON P.CampusId = C.Id
    JOIN [GroupLocation] as GL ON GL.GroupId = P.Id AND GL.GroupLocationTypeValueId = 19
    JOIN [Location] as House ON House.Id = GL.LocationId