Kids To Move¶

Warning

This script is subject to change and is still being tested.

This script does not run the OrgSearch BlueToolbar but it can be added to the main menu. The list of children comes from regular members of any Organization with the ExtraValue Integer value on the org called MinMonths and MaxMonths.

These values, MinMonths and MaxMonths, are the range of month ages for children in the org inclusive.

You would set up a list of these organizations, one per range, with no gaps. In other words, 0-3, 4-6, 7-8 etc.

The report shows which children will be outside of the appropriate range on the next Sunday from the current date when the report is run. And it will display what organization they are in now, and what the appropriate organization should be. These Organizations are labeled FromOrg and ToOrg. All children in these Organizations will be displayed with a true/false indicator in the last column called Moving. The ones to move are sorted to the top by age.

The report can be used in a Python Script to actually do the moves, either manually or on a schedule. Keep in mind that once the children are moved, the report will no longer show any kids to move for this week. So to document which kids were moved, you may want to save the report or the Excel download before moving.

See also

The Python script to Move Kids is documented at Move Kids

The recommended name is KidsToMove

https://i.tpsdb.com/2016-06-30_06-33-54.png — Sample Report¶

Use the following code to Create the SQL Script. See How to create a SQL Script.

WITH kids1 AS (
    SELECT FromOrgId = om.OrganizationId
          ,FromOrg = o.OrganizationName
          ,om.PeopleId
          ,p.Name
          ,BirthDate = CONVERT(DATE, dbo.Birthday(om.PeopleId))
          ,NextSunday = CONVERT(DATE, dbo.SundayForDate(DATEADD(WEEK, 1, GETDATE())))
    FROM dbo.OrganizationMembers om
    JOIN dbo.People p ON p.PeopleId = om.PeopleId
    JOIN dbo.Organizations o ON o.OrganizationId = om.OrganizationId
    WHERE om.MemberTypeId = 220
    AND EXISTS(SELECT NULL FROM dbo.OrganizationExtra e WHERE e.OrganizationId = om.OrganizationId AND e.Field = 'MinMonths')
), kids2 AS (
    SELECT FromOrgId
          ,FromOrg
          ,PeopleId
          ,Name
          ,BirthDate
          ,MosAge = dbo.AgeInMonths(PeopleId, NextSunday)
    FROM kids1
), orgs AS (
    SELECT ToOrgId = o.OrganizationId
          ,MinMos = oemin.IntValue
          ,MaxMos = oemax.IntValue
    FROM dbo.Organizations o
    JOIN dbo.OrganizationExtra oemin ON oemin.OrganizationId = o.OrganizationId AND oemin.Field = 'MinMonths'
    JOIN dbo.OrganizationExtra oemax ON oemax.OrganizationId = o.OrganizationId AND oemax.Field = 'MaxMonths'
), kidstomove as (
    SELECT  p.FromOrgId
           ,p.FromOrg
           ,p.PeopleId
           ,p.Name
           ,p.BirthDate
           ,p.MosAge
           ,o.ToOrgId
           ,ToOrg = oo.OrganizationName
           ,o.MinMos
           ,o.MaxMos
           ,Moving = CAST(CASE WHEN p.FromOrgId <> o.ToOrgId THEN 1 ELSE 0 END AS BIT)
    FROM kids2 p
    JOIN orgs o ON o.MinMos <= p.MosAge AND o.MaxMos >= p.MosAge
    JOIN dbo.Organizations oo ON oo.OrganizationId = o.ToOrgId
)
select * from kidstomove
where 1=1
order by moving desc, maxmos

Latest Update

11/13/2020

Modify image link with secure protocol.