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

 

[MadMaxx]

try this


SELECT
SUM(Payment.LEDGER_GROSS),
SUM(Payment.LEDGER_NET),
SUM(Payment.Ledger_Gross - Payment.Ledger_Net),
MAX(Payment.Doc_Date)
Payment.(_GROSS or _NET)
FROM Payment
WHERE Payment.Doc_Date = MAX(Payment.Doc_Date)

-Maxx

 

[sakitah]

Max, wont that return for the first 4 columns the sum for the max doc_Date only?
try this:
SELECT
SUM(P.LEDGER_GROSS),
SUM(P.LEDGER_NET),
SUM(P.Ledger_Gross - P.Ledger_Net),
MAX(P.Doc_Date) MAX_D,
IV.(_GROSS or _NET)
FROM Payment P,
(SELECT _GROSS (or _NET)
FROM Payment T
WHERE T.Doc_Date = MAX_D) IV

but, u know im not sure u can reference the alia for MAX(P.Doc_Date) which is MAX_D in the inline view, so u might just have to do another select instead of the alias to to return the max.
hope this helps...
Peace
Sakitah

 

[sem]

select extrem.*, payment.amount Last_pay_amount from
( SELECT
SUM(Payment.LEDGER_GROSS) Total_Gross,
SUM(Payment.LEDGER_NET) Total_Net,
SUM(Payment.Ledger_Gross - Payment.Ledger_Net) Total_deducations,
MAX(Payment.Doc_Date) Last_pay_date ) extrem,
Payment
where payment.doc_date=extrem.Last_pay_date

Though you may get more than 1 row if a number of payments are associated with the same pay date.

 

[nibeck]

Sem,

Not quite following your example. What is the 'extrem' stuff?

- Mike

 

[sem]

Nothing special, extrem is just an alias for subquery (extremum). You may use any word if you do not like this