Question

Photo of Dillan Cagnetta

0

How are Metric Values Stored per Campus

1

Hi

I cant seem to figure out how Metric Values are stored in the database according to Campus.

There doesnt seem to be any link to Campus.

Another thing, is how should the SQL work if you are partitioning according to Campus?

what should the SQL return so that it knows that this measure is for Campus A , Campus B etc?

 

Thank you very much

  • Photo of Nick Airdo

    0

    Greetings Dillan,

    The specific entity of a series partition is stored in the MetricValue table's EntityId column.  So if you are partitioning by a Campus entity, you'll find the values of your Campus IDs in the EntityId column.

    To answer your second question, when partitioning by campus, you would write a query somthing like this with the CampusId as the second item in your select.

    SELECT COUNT(1), C.[CampusId] FROM [Person] P ...

    To give you a little more of an actual example, here is a query we have that records the number of people per campus who have been baptised (a non-blank "Baptism Date" attribute value) .

    DECLARE @ActiveRecordStatusValueId int = 3
DECLARE @FamilyGroupTypeId int = 10

-- Active, Adults who are not Non-Central-Affiliates:
SELECT COUNT(1), G.[CampusId] FROM [Person] P 
INNER JOIN [GroupMember] GM ON GM.[PersonId] = P.[Id]
-- their first family to get their Campus
INNER JOIN [Group] G ON G.[GroupTypeId] = @FamilyGroupTypeId AND G.[Id] = GM.[GroupId]
    --AND G.[Id] = ( SELECT TOP 1 [Group].[Id] FROM [Group] WHERE G.[Id] = GM.[GroupId] )
WHERE [RecordStatusValueId] = @ActiveRecordStatusValueId AND [ConnectionStatusValueId] in (
    SELECT ID FROM DefinedValue WHERE DefinedTypeId = 4 AND [Value] NOT LIKE 'Non-Central%' AND [Value] <> 'Kid'
) AND [IsDeceased] = 0
AND P.[Id] IN 
(
    --- Baptism dates not blank:
    SELECT [EntityId] FROM [AttributeValue] AV WHERE AV.[AttributeId] in (
        SELECT [Id] FROM [Attribute] A WHERE A.[Name] like 'Baptism Date'
    ) and [Value] <> ''
)
AND CampusId IS NOT NULL
GROUP BY G.[CampusId] ORDER BY CampusId

     

    NOTE: This specifically excludes kids, the deceased, and people with a certain connection status ("Non-Central Affiliates"). 

    I hope this helps answer your questions.