A Query Question - for accounting

[dand]

Could one of the query experts on this site help to develop the following report-

Date: be able to specify the date range
For each of the days specified in the date range I would like the following:
The date
Total Income (the same amount that can be seen on each line of "This Month" report in the production and Income report in the report list of OD)
Credit Card Income
Total - Credit Card Income

Ex: if your total income for Jan 1, 2010 is $1,000 and $200 of it was Credit Card
Jan 2, 2010 is $3,000 and $500 of it was Credit Card

Then the report would show: 1/1/10 1,000 200 800
1/2/10 3,000 500 2,500

If you can figure this out, I will send you a free crate of Iowa snow..... or my next denture patient...or both!

Thanks

DanD

[V Suite]

Code: Select all

SET @FromDate='2010-01-01' , @ToDate='2010-01-31';
SELECT py.PayDate, 
(Select SUM(PayAmt) from payment where payment.PayDate = py.PayDate) AS $TOTAL ,
(Select SUM(PayAmt) from payment where payment.PayType=71 and payment.PayDate = py.PayDate) AS $CREDITCARD, 
(Select SUM(PayAmt) from payment where payment.PayType<>71 and payment.PayDate = py.PayDate) AS '$TOTAL-CC'
FROM payment py
WHERE PayDate between @FromDate and @ToDate
Group by PayDate

This gives in separate columns: Date, Total, CreditCard Amount, Total-Credit Card

I have to confess, this solution is slow presumably because of the nested Selects.
The column $TOTAL-$CREDITCARD is recalculated instead of using $TOTAL-$CREDITCARD since this value is undefined (0.00) if the $CREDITCARD is 0.00 (no credit card payments that day) !!!
Thirdly, the numerical value of the Definition - Payment Type - Credit Card may not be the same (71) on all installations. However, the Payment Category is presumably Hardcoded and thus the same (10) on all installations, so you'd need to choose the definition number for credit card payments from the enumerated list of all the payment type values:

Code: Select all

Select DefNum, ItemName FROM definition WHERE definition.Category=10

Finally, the query can be extended to show all total payment values for all payment types for the given date range, not just credit cards. If anyone needs it just reply below.

Trinidad
West Indies

[dand]

V Suite,

Thanks for the prompt reply.

This is what I am looking for, except that the "$Total" needs to include the insurance checks as well.

So the daily "$Total" should include the following three amounts:
insurance checks
cash
credit card payments

This would obviously change "$Total-CC" as well

Hopefully this correction is not difficult to do.

Thanks again,

DanD

[V Suite]

Greetings dand. Very late response. I had to rewrite everything to make this work - and I finally discovered an instruction to make it work on my machine which does not have any insurance table. It should be what you want, since it seems accurate for me. Let me know.

Note: Any day in the period with no income is not listed at all. For example in January 2010, we did not work until the 4th, so the table starts with the 4th, and omits any weekend day without income.

Cheers.

Code: Select all

SET @FromDate='2010-01-01' , @ToDate='2010-01-31';
DROP TABLE IF EXISTS tmp, tmp1, tmp2, tmp3;
CREATE TABLE tmp
SELECT PayAmt, PayDate FROM payment WHERE PayDate between @FromDate and @ToDate;
CREATE TABLE tmp1
SELECT PayAmt, PayDate  FROM payment WHERE PayDate between @FromDate and @ToDate AND PayType=71;
CREATE TABLE tmp2
SELECT PayAmt, PayDate  FROM payment WHERE PayDate between @FromDate and @ToDate AND PayType<>71;
CREATE TABLE tmp3
SELECT InsPayAmt, DateCP FROM claimproc WHERE claimproc.Status IN (0, 1, 4) AND claimproc.DateCP BETWEEN @FromDate AND @ToDate;

SELECT py.PayDate,
(Select SUM(PayAmt) from tmp  where  tmp.PayDate = py.PayDate) AS $Income,
(Select SUM(InsPayAmt) from tmp3 where  tmp3.DateCP = py.PayDate) AS $InsIncome,
(SELECT $Income + IFNULL($InsIncome,0)) AS $TotalIncome,
(Select SUM(PayAmt) from tmp1 where tmp1.PayDate = py.PayDate) AS $CREDITCARD,
(Select SUM(PayAmt) from tmp2 where tmp2.PayDate = py.PayDate) AS $IncomeMinusCC,
(SELECT $IncomeMinusCC + IFNULL($InsIncome,0)) AS $TotalIncomeMinusCC
FROM tmp py
WHERE PayDate between @FromDate and @ToDate
Group by PayDate;
DROP TABLE IF EXISTS tmp, tmp1, tmp2, tmp3;

[V Suite]

Fixed.

Cheers.

[dand]

V Suite,

Thanks!
This is exactly what I was looking for. Hopefully others out there will find it useful as well.

DanD

[fishdrzig]

How would you label this report?