| Year to Date Totals [message #404431] |
Thu, 21 May 2009 15:46 |
Leonard Martin
Messages: 45
Registered: May 2002
Location: Canada
|
Member |
|
|
Hello
I have the following query which gives me the Organization Name, Supplier name, Invoice Amount for Year to date, Total Invoice Amount for current Month and Total Invoice Amount for Previous Month.
This works fine if I am to run this query as is.
But what I need to do is, substitute the Invoice Date as a parameter. I will setup a parameter for Month and Year.
If the user selects Mar 2009, then my query should output results for YTD till Mar, Sum(Invoice Amount) for MARCH and Sum(Invoice_AmounT) for February i.e Previous Month.
If I am to add the invoice date, then I would have to add it to the group by clause which will give incorrect results.
Can someone guide me on how to do this ?
SELECT name,
remit_to_supplier_name,
(SELECT Sum(invoice_amount) sum_year
FROM ap_invoices_all c,
hr_operating_units d
WHERE c.legal_entity_id = d.organization_id
AND c.remit_to_supplier_name = a.remit_to_supplier_name
AND c.invoice_date BETWEEN Trunc(SYSDATE,'YYYY') AND SYSDATE
AND c.legal_entity_id = a.legal_entity_id) YEAR,
(SELECT Sum(invoice_amount)
FROM ap_invoices_all e,
hr_operating_units f
WHERE e.legal_entity_id = f.organization_id
AND e.legal_entity_id = a.legal_entity_id
AND e.remit_to_supplier_name = a.remit_to_supplier_name
AND e.invoice_date BETWEEN Trunc(SYSDATE,'Month') AND SYSDATE) curr_month,
(SELECT Sum(invoice_amount)
FROM ap_invoices_all e,
hr_operating_units f
WHERE e.legal_entity_id = f.organization_id
AND e.legal_entity_id = a.legal_entity_id
AND e.remit_to_supplier_name = a.remit_to_supplier_name
AND e.invoice_date BETWEEN (Trunc(Trunc(SYSDATE,'MM') - 1,'MM')) AND Last_day(Trunc(Trunc(SYSDATE,'MM') - 1,'MM'))) prev_month
FROM ap_invoices_all a,
hr_operating_units b
WHERE a.legal_entity_id = b.organization_id
AND a.legal_entity_id = 332
GROUP BY NAME,
remit_to_supplier_name,
legal_entity_id
Example of output:
TEST Corporation,XYZ Company,2098514.05,152000,152000
[EDITED by LF: applied [code] tags]
[Updated on: Thu, 21 May 2009 16:00] by Moderator Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
