Approved Workman Database

Community Contributions => SQL Workbench => Topic started by: ksaathoff on September 01, 2008, 10:02:37 AM

Title: Family Payment Invoices
Post by: ksaathoff on September 01, 2008, 10:02:37 AM

I'm looking for a way to create family payment invoices.  I figure that there is a way with SQL to get a listing of each household name, the amount they owe for dues, and the amount they owe for everything else, just like the banner at the bottom of the check-in page.
Once I have this in a table, I could do a mail merge and create an invoice for each family.
The problem is, I am clueless about SQL.  Can anyone point me in the right direction?

Thanks,

Title: Re: Family Payment Invoices
Post by: fajlawrence on August 20, 2009, 07:08:39 PM

I added a similar request as an enhancement to reports.

Title: Re: Family Payment Invoices
Post by: dave4him on August 20, 2009, 07:31:44 PM

Quote from: ksaathoff on September 01, 2008, 10:02:37 AM

I'm looking for a way to create family payment invoices.  ........... to get a listing of each household name, the amount they owe for dues, and the amount they owe for everything else...

Have you considered using the Household Account Statements in the REPORTS view? Everything you asked for is already there. SQL would be nice if I only could learn it well enough to use it..  :D

Title: Re: Family Payment Invoices
Post by: fajlawrence on September 12, 2009, 03:35:01 PM

Try this.  You can export the results into Excel.

select H.Parent_Name, P.Filing_Name, F.Balance_Due, F.Fee_Description

from Person P, Fee F, Household H

where F.Person_ID = P.Person_ID and F.Balance_Due > 0 and F.Club_Year = '2009-2010' and F.Household_ID = H.Household_ID

Group by H.Parent_Name, P.Filing_Name