I was asked to generate a report to output families that has had any member contribute to the giving fund (AccountId=1) for the first time between two arbitrary dates. To make it a little more complex, I needed to exclude families where any member is also a member of a custom general group (GroupId=6240) containing all the people who ever contributed to the general fund prior to our adoption of Rock.

I'm not the most effective SQL programmer, but I was finally able to generate the following code I wanted to share. Feel free to use this and/or adapt it to your own needs. However, since I'm under no delusions that this is the most effective snippet, I would also invite anyone to tell me how to do this more efficiently =)

SELECT d.[GivingGroupId], f.[Name] AS FamilyName, d.[TransactionDateTime] AS FirstTransactionDate FROM
    (
    Select FamilyFirst.[GivingGroupId], FamilyFirst.[TransactionDateTime] FROM
        (
        SELECT PersonFirst.[GivingGroupId], MIN(PersonFirst.[TransactionDateTime]) AS TransactionDateTime FROM
            (
            SELECT P.[Id], P.[GivingGroupId], MIN(g.[TransactionDateTime]) AS TransactionDateTime
                From [Person] P
                LEFT JOIN [FinancialTransaction] g
                    ON P.[Id]=g.[AuthorizedPersonAliasId]
                LEFT JOIN [FinancialTransactionDetail] a
                    ON g.[Id]=a.[TransactionId]
                WHERE g.[TransactionDateTime] IS NOT NULL
                    AND a.[AccountId]=1
                GROUP BY P.[Id], P.[GivingGroupId]
            ) personFirst
            GROUP BY PersonFirst.[GivingGroupId]
        ) FamilyFirst
        WHERE FamilyFirst.[TransactionDateTime] > @start
            AND FamilyFirst.[TransactionDateTime] <= @end
    ) d
    LEFT JOIN [Group] f ON d.[GivingGroupId]=f.[Id]
    WHERE d.[GivingGroupId] NOT IN
        (
            SELECT DISTINCT p.[GivingGroupId] FROM [Person] p
                LEFT JOIN [GroupMember] g
                    ON g.[PersonId] = p.[Id]
                WHERE g.[GroupId]=6240
                    AND p.[GivingGroupId] IS NOT NULL
        )

On the custom page I created, I put this code in a Dynamic Data block, hid GivingGroupId, provided parameters start=0;end=0 and linked to ~/page/113?GroupId={GivingGroupId}

Above this Dynamic Data block, I created a Custom HTML block with the following code:

<style>
span.DateControl {
    border:1px solid black;
    background-color:#fff;
}
span.DateControl input {
    border:0px;
    background:transparent;
    text-align:center;
}
</style>
<div style="display:inline-block;padding-right:4em;">Beginning date: (MM/DD/YYYY)<br />
    <span class="DateControl">
        <input type="number" id="iMMstart" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iDDstart').focus();},125)}" ></input>/<input type="number" id="iDDstart" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iYYYYstart').focus();},125)}" ></input>/<input type="number" id="iYYYYstart" maxlength="4" style="width:3em;" onFocus="this.select()" onKeyUp="if (this.value.length==4) {setTimeout(function(){ document.getElementById('iMMend').focus();},125)}"></input>
    </span>
</div>
<div style="display:inline-block;padding-right:4em;">Ending date: (MM/DD/YYYY)<br />
    <span class="DateControl">
        <input type="number" id="iMMend" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iDDend').focus();},125)}" ></input>/<input type="number" id="iDDend" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iYYYYend').focus();},125)}" ></input>/<input type="number" id="iYYYYend" maxlength="4" style="width:3em;" onFocus="this.select()"></input>
    </span>
</div>
<div style="display:inline-block;"><input type="submit" class="btn btn-primary" value="View" onClick="window.location.href=window.location.pathname + '?start=' +
                                                                                        document.getElementById('iYYYYstart').value+
                                                                                        document.getElementById('iMMstart').value+
                                                                                        document.getElementById('iDDstart').value+
                                                                                                                '&end=' +
                                                                                        document.getElementById('iYYYYend').value+
                                                                                        document.getElementById('iMMend').value+
                                                                                        document.getElementById('iDDend').value;"></input>
</div>
<script>
var params = {};
if (location.search) {
    var parts = location.search.substring(1).split('&');

    for (var i = 0; i < parts.length; i++) {
        var nv = parts[i].split('=');
        if (!nv[0]) continue;
        params[nv[0]] = nv[1] || true;
    }
}
if (params.start && params.end) {
    document.getElementById('iDDstart').value=params.start.substring(6,8);
    document.getElementById('iMMstart').value=params.start.substring(4,6);
    document.getElementById('iYYYYstart').value=params.start.substring(0,4);
    document.getElementById('iDDend').value=params.end.substring(6,8);
    document.getElementById('iMMend').value=params.end.substring(4,6);
    document.getElementById('iYYYYend').value=params.end.substring(0,4);
    document.getElementById('iMMstart').focus();
}
else {
    var thissun=new Date();
    var lastmon=new Date();
    thissun.setDate(thissun.getDate()-parseInt(thissun.getDay()));
    lastmon.setDate(lastmon.getDate()-6-parseInt(lastmon.getDay()));
    window.location.href=window.location.pathname + '?start=' +
                                        lastmon.getFullYear().toString() +
                                        ((lastmon.getMonth()+1) < 10 ? '0' + (lastmon.getMonth()+1) : (lastmon.getMonth()+1)).toString() +
                                        ((lastmon.getDate()) < 10 ? '0' + (lastmon.getDate()) : (lastmon.getDate())).toString() +
                                                    '&end=' +
                                        (thissun.getFullYear()).toString() +
                                        ((thissun.getMonth()+1) < 10 ? '0' + (thissun.getMonth()+1) : (thissun.getMonth()+1)).toString() +
                                        ((thissun.getDate()) < 10 ? '0' + (thissun.getDate()) : (thissun.getDate())).toString();
}
</script>

This block auto-refreshes the page to fill in the dates for the most recent full week (Mon-Sun) and provides the scripts to make the date boxes work like people expect (since Rock doesn't have a built-in date control that I've found).

Enjoy!