need to query most recent 6months of usage 1

[ccclsi]

I have a report where i have figured out how to pull the most recent 12 months of part usage, here is the select statement:

{INVENTORY_TRANS.PART_ID} like {?PART_ID} and
{INVENTORY_TRANS.TYPE} = "O" and
{INVENTORY_TRANS.TRANSACTION_DATE} >= dateadd("yyyy", -1, currentdate) and
{INVENTORY_TRANS.CLASS} = "I"

what i need to do now is query most recent 6months of usage. I a having problems with this. Here is formula that does not seem to work:


If {INVENTORY_TRANS.TRANSACTION_DATE} >= dateadd("m", -6, currentdate) then Sum ({INVENTORY_TRANS.QTY}) else 0


Using CR 8.5, on Oracle 8i Db. TIA for your help,, Bill

 

[lbass]

You should use:

If {INVENTORY_TRANS.TRANSACTION_DATE} >= dateadd("m", -6, currentdate) then {INVENTORY_TRANS.QTY}

Then insert a summary on this formula. Your version was saying that when the date is in the last six months, show the sum of ALL quantities.

-LB

 

[ccclsi]

Thanks a bunch, that worked great. Have a wonderful weekend!