Hi.
On Tue, Oct 02, 2001 at 03:14:33PM -0500, [EMAIL PROTECTED] wrote:
> Hello All,
>
> I have several queries that generate various financial reports, by date ranges.
> I've been trying to figure out a way to combine all these queries into one query
> , if it's at all possible or if it's practical to combine them into one large
> query. The single query I tried doesn't produce the proper results. The multiple
> queries below do produce the desired results, but I'd like to combine them into
> one single query, if it's possible.
The middle queries have a different time range, but from your own
proposal below, I assume that this is only a typo? If so, the first
four queries can be combined to one quite easily, because only some
other fields are selected, but the FROM and WHERE clause would be the
same. The same for the last two queries:
SELECT format(sum(po.refund_charge*.95),2) as RefundCharge
format(sum(po.refund_check),2) as RefundCheck
format(sum(po.chargeback*.95)+ (count(po.chargeback)*-15),2) as ChargeBack
format(sum(po.badcheck)+ (count(po.badcheck)*-15),2) as Badcheck
FROM payhistory ph, payout po
WHERE ph.paydate between '2001-09-01' and '2001-09-14'
and po.payid = ph.payid;
SELECT format(sum(pi.amt_charge*.95),2) as TotalCharge
format(sum(pi.amt_check),2) as TotalCheck
FROM payhistory ph, payin pi
WHERE ph.paydate between '2001-09-01' and '2001-09-14'
and pi.payid = ph.payid
Now, the last query wants to add data of "payin", referred by
"payhistory". Since you used LEFT JOINs, I assume that there will be
entries in payhistory which refer to payin, but not payout and the
other way around.
SELECT format(sum(po.refund_charge*.95),2) as RefundCharge
format(sum(po.refund_check),2) as RefundCheck
format(sum(po.chargeback*.95)+ (count(po.chargeback)*-15),2) as ChargeBack
format(sum(po.badcheck)+ (count(po.badcheck)*-15),2) as Badcheck
format(sum(pi.amt_charge*.95),2) as TotalCharge
format(sum(pi.amt_check),2) as TotalCheck
FROM payhistory ph
left join payout po on po.payid = ph.payid
left join payin pi on pi.payid = ph.payid
WHERE ph.paydate between '2001-09-01' and '2001-09-14'
> select format(sum(po.refund_charge*.95),2) as RefundCharge,
> format(sum(po.refund_check),2) as RefundCheck,
> format(sum(po.chargeback*.95)+ (count(po.chargeback)*-15),2) as ChargeBack,
> format(sum(po.badcheck)+ (count(po.badcheck)*-15),2) as Badcheck,
> format(sum(pi.amt_charge*.95),2) as TotalCharge,
> format(sum(pi.amt_check),2) as TotalCheck
> from payhistory ph
> left join payin pi on pi.payid = ph.payid
> left join payout po on po.payid = ph.payid
> where ph.paydate between '2001-09-01' and '2001-09-14'
Compared with your query, they look the same. So you better give some
example of result you get with the long query and what you expect to
be different.
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
