RockRMS - Metric SQL and Lava

Adult Connect Groups Count.lava

{% assign previousSunday = 'Now' | SundayDate | DateAdd:-7 | Date:'M/dd/yyyy' %}
Metric Date: {{ previousSunday }}
<br />
{% group where:'ParentGroupId == 49 || ParentGroupId == 10603  && IsActive == true' %}
    {% for group in groupItems %}
        {% attendance where:'DidAttend == true && GroupId == {{ group.Id }} && SundayDate == "{{ previousSunday }}"' count:'true' %}
            {% if count > 0 %}
                Metric Partition: {{ group.Name }}
                <br />
                Metric Value: {{ count }}
                <br /><br />
            {% endif %}
        {% endattendance %}
    {% endfor %}
{% endgroup %}

Adult_ConnectGroups.sql

Adult Connect Group Attendance
{% sql %}
    SELECT SUM([YValue]) FROM [MetricValue] WHERE [MetricId] = 1089;
{% endsql %}
{% for item in results %}
    <h3>{{ item.Column1 | Format:'#,##0' }}</h3>
{% endfor %}

Lava for Metric - Sum of Group Attendances.lava

Not working on metric for some reason but is working everywhere else

{%- assign previousSunday = 'Now' | SundayDate | DateAdd:-7 | Date:'MM-dd-yyyy' -%}
{%- assign adultConnectGroupCount = 0 -%}
{%- group where:'ParentGroupId == 49 || ParentGroupId == 10603  && IsActive == true' -%}
    {%- for group in groupItems -%}
        {%- attendance where:'DidAttend == true && GroupId == {{ group.Id }} && SundayDate == "{{ previousSunday }}"' count:'true' -%}
            {%- if count > 0 %}
                {%- assign adultConnectGroupCount = adultConnectGroupCount | Plus:count -%}
            {%- endif -%}
        {%- endattendance -%}
    {%- endfor -%}
{%- endgroup -%}
{{ adultConnectGroupCount }},{{ previousSunday }}

Lava SQL for Metric - Sum of Group Attendances.lava

{% assign previousSunday = 'Now' | SundayDate | DateAdd:-7 | Date:'MM-dd-yyyy' %}
{% capture mygroupids %}
    {% group where:'ParentGroupId == 49 || ParentGroupId == 10603  && IsActive == true' %}
        {% for group in groupItems %}
            {% attendance where:'DidAttend == true && GroupId == {{ group.Id }} && SundayDate == "{{ previousSunday }}"' count:'true' %}
                    {{ group.Id }}{% if forloop.last == false %},{% endif %}
            {% endattendance %}
        {% endfor %}
    {% endgroup %}
{% endcapture %}

{% sql %}
    SELECT
        COUNT(*) as Attendance
    FROM
        [Attendance]
    WHERE DidAttend = 1
        AND GroupId IN ( {{ mygroupids }} )
        AND StartDateTime >= dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-1,'17530101')
{% endsql %}

{% for item in results %}
    {{ item.Attendance }}
{% endfor %}

Only get metrics from this year.lava

{% assign metricYear = metricvalue.MetricValueDateTime | Date:'yyyy' %}
{% assign currentYear = 'Now' | Date:'yyyy' %}
{%if metricYear != currentYear %}
    {% continue %}
{% endif %}
// do what you want here to show the metric value

SQL for Metric - Sum of Group Attendances - WORKING.sql

{% assign previousSunday = 'Now' | SundayDate | DateAdd:-7 | Date:'MM-dd-yyyy' %}

SELECT
   COUNT(*), '{{ previousSunday }}' [MetricValueDateTime]
FROM
   [Attendance]
WHERE DidAttend = 1
   AND GroupId IN (SELECT Id FROM [Group] WHERE ParentGroupId IN (49, 10603) AND IsActive = 1)
   AND StartDateTime >= dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-1,'17530101')

Staff Birthdays - Next 30 days.lava

{% assign currentDate = 'Now' | Date:'M-d' %}

{% groupmember where:'GroupId == 3' %}
    Upcoming Staff Birthdays:
    <ul>
        {% for groupmember in groupmemberItems %}
            {% assign StaffBirthDay = groupmember.Person.BirthDate | Date:'M-d' %}
            {% assign DaysUntil = currentDate | DateDiff:StaffBirthDay,'d' %}
            
            {%if DaysUntil < 30 and DaysUntil > 0 %}
                <li>{{ groupmember.Person.FullName }} - {{ groupmember.Person.BirthDate | Date:'MMMM d' }}</li>
            {% endif %}
        {% endfor %}
    </ul>
{% endgroupmember %}

Staff Birthdays - This Month.lava

{% assign currentMonth = 'Now' | Date:'MMMM' %}
{% groupmember where:'GroupId == 3' %}
    Staff Birthdays:
    <ul>
        {% for groupmember in groupmemberItems %}
            {% assign StaffBirthMonth = groupmember.Person.BirthDate | Date:'MMMM' %}
            {%if StaffBirthMonth == currentMonth %}
                <li>{{ groupmember.Person.FullName }} - {{ groupmember.Person.BirthDate | Date:'MMMM d' }}</li>
            {% endif %}
            
        {% endfor %}
    </ul>
{% endgroupmember %}

Total Given.sql

Total Given
{% sql %}
    SELECT
        SUM([Amount]) as TotalGiven
    FROM
        [AnalyticsSourceFinancialTransaction]
    WHERE
        [TransactionDateTime] >= dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-8,'17530101')
    AND
        [TransactionDateTime] < dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-1,'17530101')
{% endsql %}

{% for item in results %}
    <h3>{{ item.TotalGiven | Format:'$#,##0.00' }}</h3>
{% endfor %}

Worship.sql

Worship Attendance
{% sql %}
    SELECT TOP 1 [YValue],[MetricValueDateTime] FROM [MetricValue] WHERE [MetricId] = 292 ORDER BY [MetricValueDateTime] DESC;
{% endsql %}
{% for item in results %}
    <h3>{{ item.YValue | Format:'#,##0' }}</h3>
{% endfor %}
<br /><br /><br />
This week
{%metricvalue where:'MetricId == 292' sort:'MetricValueDateTime desc' limit:'1'%}
    {% for metricvalue in metricvalueItems %}
        {% capture currentWorshipAttendance %}
            {{ metricvalue.YValue | Format:'#,##0' }}
        {% endcapture %}
        <h3>{{ currentWorshipAttendance }}</h3>
    {% endfor %}
{% endmetricvalue %}
<!-- Last Week -->
{%metricvalue where:'MetricId == 292' sort:'MetricValueDateTime desc' limit:'1' offset:'1'%}
    {% for metricvalue in metricvalueItems %}
        {% capture previousWorshipAttendance %}
            {{ metricvalue.YValue | Format:'#,##0' }}
        {% endcapture %}
    {% endfor %}
{% endmetricvalue %}

{{ (currentWorshipAttendance | DividedBy:previousWorshipAttendance) | Minus:1 | Format:'###0%'  }} since last week's attendance of {{ previousWorshipAttendance }}

YTD for Metric.lava

{% sql %}
    SELECT SUM([YValue]) FROM [MetricValue] WHERE [MetricId] = 1099 AND YEAR([MetricValueDateTime]) = YEAR(getdate());
{% endsql %}
{% for item in results %}
    YTD:{{ item.Column1 | Format:'#,##0' }}
{% endfor %}