[ledgersmb-users] Useful sql selects - at least for me :-)

[bill Ott <..hidden..>]

As tax time approaches, I thought I'd share two sql select scripts that I have found useful. 

The first one gets all of the email addresses that were current during the year (for sending out W-9 forms, for example)

It works as written through January of the next year. If you use it in February, you'll need to modify the "now()-'31d'". it runs on LSMB 1.5

select distinct b.contact from ar a, eca_to_contact b where (date_trunc('year',a.transdate)) = (date_trunc('year',now()-'31d'::interval))  and a.entity_credit_account = b.credit_id and b.contact_class_id>14 order by b.contact;

The second get all of the email addresses that were current in the prior month (for sending out "happy New Years" to your tenants)

select distinct b.contact from ar a, eca_to_contact b where (date_trunc('month',a.transdate)) = (date_trunc('month',now()-'31d'::interval)) and a.entity_credit_account = b.credit_id and b.contact_class_id>14 order by b.contact;

Hope you find them useful. If you make improvements, please share.

-- 
Regards,
Bill Ott

Home: 919-363-0031
Cell: 919-434-7589
Email: Mailto:..hidden..
Website: http://www.theotts.org
Profile: http://www.linkedin.com/in/wbott

_______________________________________________
users mailing list
..hidden..
https://lists.ledgersmb.org/mailman/listinfo/users