Reports for 15 days, 30 days, etc...

jwinchester1

Hey all,

I've been working displaying queries from our mysql database to our website so clients can log in and see their respective info. Now I have to create a query that separates the fields by Company, then displays 3 columns - Days Passed(15 day, 30 day, 45, 60, and 90 & over), Invoice Quantity(how many invoices received from those periods), and finally Invoice Total(the $ amount added from those periods). And then at the bottom display the total of invoices for all periods, and the total invoice amounts for all. Then break and the next Company's info...

I've written queries to display all the info in one table, with all companies grouped, but my boss wants them separate.

Kinda like this:

Company A

Period | Invoice Qty. | Invoice Total

15 Days | 11 | 2000.00

30 Days | 4 | 1100.00

45 Days | 3 | 0.00

60... |

90 & Over |

TOTAL - | 18 | 3200.00

Company B

Same idea, diff. company

Any suggestions?

Thanks,

JWinchester

devinemke

what is your data structure? (table names, field names, field types, etc...)

jwinchester1

Sry, forgot the most important part.

Fields:

Cmpny From tblCompanies TEXT

InvoiceTotal From tblInvoices NUMBER

Program(Not displayed but has to have "tgn") FROM tblClaims TEXT

DispatchDate FROM tblClaims Date/Time

jwinchester1

Any suggestions?

Here are my querys:

$sql = 'SELECT tblInsuranceCompanies.Cmpny, tblInvoices.InvoiceTotal, tblClaims.Program, tblClaims.DispatchDate'
        . ' FROM (tblClaims INNER JOIN tblInvoices ON tblClaims.SeqNum = tblInvoices.SeqNum) INNER JOIN tblInsuranceCompanies ON tblClaims.CustID = tblInsuranceCompanies.Abrvtn'
        . ' WHERE (((tblClaims.Program)="tgn")) AND DispatchDate BETWEEN CURDATE() AND (CURDATE() - INTERVAL 15 DAY)';

$sql2 = 'SELECT tblInsuranceCompanies.Cmpny, tblInvoices.InvoiceTotal, tblClaims.Program, tblClaims.DispatchDate'
        . ' FROM (tblClaims INNER JOIN tblInvoices ON tblClaims.SeqNum = tblInvoices.SeqNum) INNER JOIN tblInsuranceCompanies ON tblClaims.CustID = tblInsuranceCompanies.Abrvtn'
        . ' WHERE (((tblClaims.Program)="tgn")) AND DispatchDate BETWEEN CURDATE() AND (CURDATE() - INTERVAL 30 DAY)';

$sql3 = 'SELECT tblInsuranceCompanies.Cmpny, tblInvoices.InvoiceTotal, tblClaims.Program, tblClaims.DispatchDate'
        . ' FROM (tblClaims INNER JOIN tblInvoices ON tblClaims.SeqNum = tblInvoices.SeqNum) INNER JOIN tblInsuranceCompanies ON tblClaims.CustID = tblInsuranceCompanies.Abrvtn'
        . ' WHERE (((tblClaims.Program)="tgn")) AND DispatchDate BETWEEN CURDATE() AND (CURDATE() - INTERVAL 45 DAY)';

$sql4 = 'SELECT tblInsuranceCompanies.Cmpny, tblInvoices.InvoiceTotal, tblClaims.Program, tblClaims.DispatchDate'
        . ' FROM (tblClaims INNER JOIN tblInvoices ON tblClaims.SeqNum = tblInvoices.SeqNum) INNER JOIN tblInsuranceCompanies ON tblClaims.CustID = tblInsuranceCompanies.Abrvtn'
        . ' WHERE (((tblClaims.Program)="tgn")) AND DispatchDate BETWEEN CURDATE() AND (CURDATE() - INTERVAL 60 DAY)';

$sql5 = 'SELECT tblInsuranceCompanies.Cmpny, tblInvoices.InvoiceTotal, tblClaims.Program, tblClaims.DispatchDate'
        . ' FROM (tblClaims INNER JOIN tblInvoices ON tblClaims.SeqNum = tblInvoices.SeqNum) INNER JOIN tblInsuranceCompanies ON tblClaims.CustID = tblInsuranceCompanies.Abrvtn'
        . ' WHERE (((tblClaims.Program)="tgn")) AND DispatchDate BETWEEN CURDATE() AND (CURDATE() - INTERVAL 90 DAY)';

How do run each one, but only display the results at the specific table row?

30 -

45 -

60 -

etc.?

Thanks

Jwinchester

Roger_Ramjet

You do this in one query by making it a Union Query . You can use the outer sorting of the whole result set to get the figures into company order.
You probably also need to be using Group By and Aggregate Functions to getr the totals and counts you are after.