I am facing a problem while Claculating the Item Ageing Logic .
I have a table
Item Code EntType Organization Code Qty Date
A5323 TFP 6 2 11/03/2011
A5323 DIS 6 3 21/07/2011
A5323 ADJ 6 4 22/07/2012
A5323 MIS 6 -2 22/08/2012
A6243 TFP 6 4 22/03/2012
A6243 MIS 6 -1 22/07/2012
Now we have to plot a chart on the basis of bucket of '0-90','90-180','180-270' and so on.
In this case we have to Find the max date on the basis of +ve Entype ie having the Positive Quantity ie TFP ,DIS etc.
For example for first Item Code A5323-TFP ,DIS ,ADJ
and For Second A6243 -TFP
and we have to Consider the max date for this +ve Entype ie
for first case
1)A5323 ADJ 6 4 22/07/2012
2)A6243 TFP 6 4 22/03/2012
and so on.
User will enter a date and from that we have to calculate the dynamic button.
if((UserInputDate-max date)>='0' and (UserInputDate-max date)<='90',Dual('0-90',1), and so on for Bucket.
But we have to keep one thing in mind maxdate for calculation of Bucket will be the max date of +ve Enttype but the Quantity will be total sum
in case user has input 21/07/2012
than tha format will be
ItemCode UserInput Date Bucket MaxDate Qty
A5323 21/07/2012 0-90 21/07/2011 It will be sum of both +ve and -ve Entype as on date enter by user
A6243 21/07/2012 90-180 22/03/2012
In case anyone have idea to solve the issue.
Please guide me how to do so.
Thanks in advance