This should be easy

[nibeck]

New to SQL, so bear with me.....

I have a Payments table that has the following fields:

Doc_Date Ledger_Gross Ledger_Net
-------- ------------ -------
1/1/01 100 90
2/1/01 100 90
3/1/01 200 180

I need a quesry that will return 5 items:
1 - Total Gross
2 - Total Net
3 - Total deducations
4 - Last pay date
5 - Last pay amount

I got the first 4 with the following:

SELECT
SUM(Payment.LEDGER_GROSS),
SUM(Payment.LEDGER_NET),
SUM(Payment.Ledger_Gross - Payment.Ledger_Net),
MAX(Payment.Doc_Date)

Now, I can't figure out how to get the value of the payment associated with the Last pay date (Doc_Date).

Help.......

- Mike

 

[olded]

Mike:

The only way I can think to do it, is to save the max(doc_date) in a temp table and then perform a sub-select against the temp table such as this:

select max(doc_date) max_date from payment into temp newtemp;
select ledger_gross from payment where doc_date in (select max_date from newtemp);
drop table newtemp;

My database (informix) has the limitation of insisting that any aggregate value (such as MAX) must have an alias (max_date) in the example.

Your database may allow you to get max(doc_date) into a temp table without aliasing.

Regards,


Ed
Schaefer

 

[Guest_imported]

In DB2 for AS/400 I have just tested the following ok, it is using only sub-queries :

SELECT sum(payment.ledger_gross) as totalgross,
sum(payment.ledger_net ) as totalnet ,
sum(payment.ledger_gross-payment.ledger_net) as totaldeduct,
max(payment.doc_date) as lastpaydate,
helptable.ledger_gross as lastpayment
FROM payment, payment helptable
WHERE helptable.doc_date = (SELECT max(doc_date)
FROM payment)
GROUP BY helptable.ledger_gross

milan432