Need your help. I have to make a Query. The scenario looks like this:
Report has rows: Sales doc no, start date, end date, cancellation date
Report has columns: Prev period, new bkng, Cancels, Expired, Current yr, Net, plan cost
1) if cancellation date or end date has not yet happened, whichever date is later, then Current yr, Net, plan cost shud be populated
if cancellation date or end date has happened, whichever date is later, then Current yr, Net, plan cost shud not be populated and cancels amount shud be populated
2) if the start date is start of fiscal year i.e 10/1/2007 then, prior year eg 8/4/2006(end date) - sales doc shud be showed also but if there are two or more prior year sales doc with end date before 10/1/2007 then take the prior year sales doc with largest end date.
note:- the 3rd point functionality is only done for start of fiscal yr that is october 10
and Expired, Prev period, shud be populated in prior year sales doc but no value for current yr , no value for plan cost - shud be populated.
3) If cancellation or end date has happend, then only a cancellation flag Y or N should be set
If current sales doc doesnot have cancellation date then flag is set to blank
If cancellation date or end date is not yet happened then flag is also set to blank
Set flag to Y if an Object 0Reason has value AR or else if has no value then set to N
4) If selection month eg 5/2007 and end dat eis 2/2007 then Net value shud show up in expired till end of fiscal year and a Truly expired should be set Y or N
if there is no future period for slaes doc in the table /cube then set it to Y
if there is future period for slaes doc in the table /cube then set it to N
5) A truly new booking Y or N flag shud be set if there is no prior sales doc i.e truly a new booking. The start date falls in the current fiscal year