Re: [AGENCY-general] First move-in report?

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

This is still giving any move-ins during the selected time period, as opposed to first move-ins.  Pick almost any client at random, especially toward the top of the list, and you'll find tenants on their sixth building.

Would it help to bring in their SPC registration date?  I can't think of a way to do that without setting an arbitrary time between registration and move-in.
________________________________
From: Ken Tanzer [ken...@gm...]
Sent: Wednesday, March 11, 2015 4:38 PM
To: Michael James
Cc: age...@li...
Subject: Re: First move-in report?

Hey Mike.  I don't think that DISTINCT ON (residence_date) will get you where you want to be.  If you do that, it's going to filter out multiple records with the same move in date, so for example on the first of any month you'd only see one movein.  DISTINCT ON (client_id) is definitely what you want, since that will filter out multiple records for the same client.

I'm not sure what issue you encountered with the DISTINCT ON (client_id). I went ahead and tried it on your system, and the SQL I sent didn't work, because the last line said order by residence_date, but the subquery had already renamed that to move_in.  So that last line should read ORDER BY move_in.

Try this query first without, and then with, the DISTINCT ON (client_id).  When you run it without DISTINCT, you'll see the 5th & 6th rows are the same client, and with the DISTINCT you only get one row for them.  I'm pretty sure that's what you want, but let me know if I'm missing something.

Cheers,
Ken

SELECT DISTINCT ON (client_id) * FROM (
SELECT client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out
FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code)
WHERE
residence_date BETWEEN '1/1/14' AND '12/31/14'
ORDER BY client_id,residence_date
) foo
ORDER BY client_id,move_in;

On Wed, Mar 11, 2015 at 11:20 AM, Michael James <mj...@pl...<mailto:mj...@pl...>> wrote:
I changed your “DISTINCT ON” to grab the residence_date instead of the client_id, and that got me closer:

SELECT
DISTINCT ON (residence_date) residence_date AS move_in,
client_id,
ro.housing_project_code,housing_unit_label AS unit
FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code)
WHERE
residence_date BETWEEN '$sdate' AND '$edate'
ORDER BY residence_date,client_id

It’s showing brand new SPC tenants *except* for the first to clients returned by the query.  They’ve been around a while.  Any ideas?

From: Ken Tanzer [mailto:ken...@gm...<mailto:ken...@gm...>]
Sent: Tuesday, March 10, 2015 12:52 PM

To: Michael James
Cc: age...@li...<mailto:age...@li...>
Subject: Re: First move-in report?

This is your current SQL:

SELECT client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out
FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code)
WHERE
$which BETWEEN '$sdate' AND '$edate'

This SQL would only get the first moveins:

SELECT DISTINCT ON (client_id) client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out
FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code)
WHERE
$which BETWEEN '$sdate' AND '$edate'
ORDER BY client_id,residence_date

If you want a separate report, you can just run with that.  Or you could add a new option to the report:

PICK first_only "All moves?"
"" "Showing all moves"
"DISTINCT ON (client_id)" "Showing only one move per client"
ENDPICK

And then your SQL can become:

SELECT $first_only client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out
FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code)
WHERE
$which BETWEEN '$sdate' AND '$edate'
ORDER BY client_id,residence_date

The only issue then is that your report is now sorted by client.  Currently, there is no sort specified, but it looks like it's coming out by residence_date.  So as a final step, you could wrap the whole thing as a subselect and restore the residence_date order:

SELECT * FROM (

SELECT $first_only client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out
FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code)
WHERE
$which BETWEEN '$sdate' AND '$edate'
ORDER BY client_id,residence_date

) foo
ORDER BY residence_date

I think that will all work as described, but I haven't tested it.  So let me know if it doesn't work, or if you have any questions about it.  Thanks.

Ken

On Tue, Mar 10, 2015 at 12:31 PM, Michael James <mj...@pl...<mailto:mj...@pl...>> wrote:
I’d be happy with #1 or #3.  I think the existing report is very useful as it is.

Mike

From: Ken Tanzer [mailto:ken...@gm...<mailto:ken...@gm...>]
Sent: Tuesday, March 10, 2015 12:12 PM
To: Michael James
Cc: age...@li...<mailto:age...@li...>
Subject: Re: First move-in report?

Hey Mike.  Which of these are you wanting?

1)  A separate report to only list first moveins
2)  This report changed to only list first moveins
3)  An option for this report, so it could report as it does now, or optionally only list first moveins?

Let me know.  Thanks.

Ken

On Tue, Mar 10, 2015 at 9:42 AM, Michael James <mj...@pl...<mailto:mj...@pl...>> wrote:
Hi Ken,

We’ve got a move-in/move-out report in SPC that works well (see variable and code block below).  There’s a lot of tenant movement, so if you pick a two-week stretch you’ll likely see a dozen or so folks who’ve moved from one building to another.

Is there an easy way to get a report of people moving into their first unit during the chosen time period?  It would be useful to see who is moving in for the first time.

Thanks,
Mike

-----

DATE sdate "Start date"
DATE edate "End date"

PICK which "Move-ins or Move-outs?"
"residence_date" "Move ins"
"residence_date_end" "Move outs"
ENDPICK

SELECT client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out
FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code)
WHERE
$which BETWEEN '$sdate' AND '$edate'

--
[X]
AGENCY Software
A Free Software data system
By and for non-profits
http://agency-software.org/
https://agency-software.org/demo/client
ken...@ag...<mailto:ken...@ag...>
(253) 245-3801<tel:%28253%29%20245-3801>

Subscribe to the mailing list<mailto:age...@li...?body=subscribe> to
learn more about AGENCY or
follow the discussion.

--
[X]
AGENCY Software
A Free Software data system
By and for non-profits
http://agency-software.org/
https://agency-software.org/demo/client
ken...@ag...<mailto:ken...@ag...>
(253) 245-3801<tel:%28253%29%20245-3801>

Subscribe to the mailing list<mailto:age...@li...?body=subscribe> to
learn more about AGENCY or
follow the discussion.

--
[X]
AGENCY Software
A Free Software data system
By and for non-profits
http://agency-software.org/
https://agency-software.org/demo/client
ken...@ag...<mailto:ken...@ag...>
(253) 245-3801

Subscribe to the mailing list<mailto:age...@li...?body=subscribe> to
learn more about AGENCY or
follow the discussion.

Re: [AGENCY-general] First move-in report?

AGENCY is a Free Software Data System by and for Nonprofits and others

Re: [AGENCY-general] First move-in report?