Re: Query requirement

20 Views

Hi Guys,

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

Req

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

Thanks

Rob