Hi All,
I need to perform the following calculation in a Report Column:
Sum(ProductID where Product name = 'Golf Equiptment' and Product Item= 'Clubs' and the Order Date is between the previous month 1st and last day)
This being June I need to display for May-01-08 to May-31-08
How can I achieve this.
This is urgent please let me know how to do this.
Thanks in advance for the help!
Kbasha
You want to Total the product IDs? This seems odd...
Maybe the example I gave product ID does not make sense, but what I'm trying to achieve is.......
Can you replace PRoduct ID with Requisition ID? Requisition ID's has values ReqID = 3456, 234, 3567 which meet the criteria for past month.
So now I column value should show 3. Individually I know how to acheieve this.
Count(CASE
WHEN PRODUCT = 'Golf Equipment' AND Product_item= 'Clubs'
THEN REq_ID
END) will give answer 3. So far it is working fine.
The help I'm looking for is how to insert the calculation for the previous month.
Hope this helps.
Thanks,
Kbasha
Try this...
TO_NUMBER(TO_CHAR(sysdate, 'MM')) - 1.
This will give you the month number for the previous month.
Try
Order Date Between _first_of_month(_add_months(sysdate(),-1)) AND _last_of_month(_add_months(sysdate(),-1))