Org Percent Giving
==================

This script runs from the `OrgSearch BlueToolbar`.
It shows each organization in the search, with the percentage of
each organization's members where someone in the family gives.
This way, if either a husband or a wife gives (regardless of Joint giving option)
That family unit will be counted as a giver.
The script looks back 365 days and considers all funds.

The counts are the number of family units, not individuals.

The comment at the top of the script ensures that this report can only be run by a Finance user.

The recommended name is ``OrgPercentGiving``

.. figure:: https://i.tpsdb.com/2016-04-10_19-17-17.png
    :target: #

    Sample Report

Use the following code to Create the SQL Script.
See :doc:`../CreateSqlScript`.

.. code-block:: sql

    --Roles=Finance
    DECLARE @td DATETIME = GETDATE()
    DECLARE @fd DATETIME = DATEADD(DAY, -365, @td)
    ;WITH orgfamilies AS (
        SELECT p.FamilyId, om.OrganizationId
        FROM dbo.OrganizationMembers om
        JOIN dbo.People p ON p.PeopleId = om.PeopleId
        JOIN dbo.SplitInts(@OrgIds) i ON i.Value = om.OrganizationId
        WHERE ISNULL(om.Pending, 0) = 0
        AND om.MemberTypeId NOT IN (311)
        GROUP BY p.FamilyId, om.OrganizationId
    ), givers AS (
        SELECT DISTINCT fo.FamilyId
              ,fo.OrganizationId
              ,CAST(g.FamGive AS INT) isgiver
        FROM orgfamilies fo
        LEFT JOIN dbo.FamilyGiver(@fd, @td, NULL) g ON g.FamilyId = fo.FamilyId
    ), summary AS (
        SELECT o.OrganizationId
            ,o.OrganizationName
            ,SUM(g.isgiver) Givers
            ,SUM(1-g.isgiver) NonGivers
            ,COUNT(*) Units
        FROM givers g
        JOIN dbo.Organizations o ON o.OrganizationId = g.OrganizationId
        GROUP BY o.OrganizationId, o.OrganizationName
    )
    SELECT s.OrganizationId
        ,s.OrganizationName
        ,s.Units
        ,CONVERT(FLOAT, s.Givers) / s.Units * 100 GivePct
    FROM summary s
    ORDER BY s.OrganizationName


|
|

+--------------------+------------------+
| **Latest Update**  | **11/13/2020**   |
+--------------------+------------------+

Modify image link with secure protocol.