Dynamically Calculating Next Fiscal Mo

[petperson]

I am trying to create a Dynamic date for Next Fiscal Month. Here are the requirements:

Attribute: Prev Physical Inventory Dt
Attribute: Current Physical Inventory Dt

Based on the date of the Previous Physical Inventory date, I need to dynamically calculate what the next Fiscal month would be. For example,

Prev PI Dt Cur PI Dt FisMth Week Dt
7/24/2003 6/3/2004 200306 7/27/2003
7/24/2003 6/3/2004 200306 8/3/2003

7/24/2003 6/3/2004 200307 8/10/2003

In this example, the previous PI date is 7/24/2003 and the Fiscal month for that date is 200306. Based on the PI date of 7/24/2003, I need to be able to calculate the next Fiscal Mth is 200307. In this example, I would NOT want to count the 2 rows where Fiscal Mth = 200306, but would want to start with the row where Fiscal Mth = 200307 and Week date = 8/10/2003. The challenge here is that the Previous Physical Inventory dates will vary throughout the month. Has anyone encountered this type of scenario before? Help is appreciated.

thanks!

 

[entaroadun]

Build a translation table that encapsulates your conversion logic, then build a view with that table with non-equijoins. That's the easiest way with the best performance. It should be fairly straightforward...

 

[nlim]

what's the logic steps from Previous PI date to Next fiscal month? Does it something like this?

if month(prev PI date) is xxxxx then aaaaa,
elseif yyyyy then bbbbbb
....(repeat elseif then)
else zzzzz

 

[mstrNate]

Hi there,

... "Based on the date of the Previous Physical Inventory date, I need to dynamically calculate what the next Fiscal month would be" ...

I think this might help, but then I might have misunderstood what you were looking for. Also, not sure if this will work in an attribute if that is where you wanted to put it. Of course, the names are kind of generic, hope it makes sense though.

ApplyComparison("#0 in
(select min(fiscal_mth)
from time_dm
where fiscal_mth > (select fiscal_mth
from time_dm
where prev_fiscal_dt = #1))
",[Fiscal Month]@Id, [Prev PI Date]@Id)