conditional iterno so calculate from-to

[amien]

i have this script:

t1:
LOAD * INLINE
[
Invoice#, From, To, Type
1, 01/01/2016, 05/01/2016, monthly
2, 01/01/2016, 05/01/2021, yearly
3, 01/01/2016, 10/01/2018, half year
4, 01/01/2016, 05/01/2018, two monthly
5, 01/01/2016, 05/01/2021, quarterly

];

Inner Join
Load
Invoice#,
AddMonths(MonthStart(From), IterNo()-1) As %Period
Resident t1
While Num(AddMonths(MonthStart(From), IterNo()-1)) <= Num(To)
;

This works fine.

But now i want to get the type involved (monthly, yearly etc) ; i don't need a monthly record in each case.

NoConcatenate
t2:
Load
*
Resident t1
Where
Type = 'monthly' Or //Nothing to delete
  (Type = 'yearly' And Text(Date(Date(From,'MM-DD-YYYY'),'MMDD')) = Text(Date(Date(%Period,'MM-DD-YYYY'),'MMDD')))
;

Drop Table t1;

This works for monthly (ofcourse) and i added yearly. So this means i only need a record ones a year.

Anyone got an idea how to do "half year","two monthly" and "quartherly"?

Example in attachment

Thanks in advanced !

[sunny_talwar]

Try this

t1:

LOAD * INLINE

[

Invoice#, From, To, Type

1, 01/01/2016, 05/01/2016, monthly

2, 01/01/2016, 05/01/2021, yearly

3, 01/01/2016, 10/01/2018, half year

4, 01/01/2016, 05/01/2018, two monthly

5, 01/01/2016, 05/01/2021, quarterly

];

Inner Join

Load

Invoice#,

AddMonths(MonthStart(From), IterNo()-1) As %Period

Resident t1

While Num(AddMonths(MonthStart(From), IterNo()-1)) <= Num(To)

;

NoConcatenate

t2:

Load

*

Resident t1

Where

Type = 'monthly' Or //Nothing to delete

(Type = 'yearly' and Month(From) = Month(%Period)) or

(Type = 'half year' and Match(Num(Month(%Period)), Num(Month(From)), Month(From) + 6)) or

(Type = 'two monthly' and Match(Num(Month(%Period)), Num(Month(From)), Month(From) + 2, Month(From) + 4, Month(From) + 6, Month(From) + 8, Month(From) + 10)) or

(Type = 'quarterly' and Match(Num(Month(%Period)), Num(Month(From)), Month(From) + 3, Month(From) + 6, Month(From) + 9))

;

Drop Table t1;

[MarcoWedel · Accepted solution]

Hi,

one solution might be to include a month interval number in your While/IterNo() iteration, e.g. like this:

t1:
LOAD *,
    MonthName(From,(IterNo()-1)*MonthInterval) as %Period   
INLINE [
    Invoice#, From, To, Type, MonthInterval
    1, 01/01/2016, 05/01/2016, monthly, 1
    2, 01/01/2016, 05/01/2021, yearly, 12
    3, 01/01/2016, 10/01/2018, half year, 6
    4, 01/01/2016, 05/01/2018, two monthly, 2
    5, 01/01/2016, 05/01/2021, quarterly, 3
] While MonthName(From,(IterNo()-1)*MonthInterval) <= To;

hope this helps

regards

Marco

[sunny_talwar]

This is a much better approach rather than first creating all the dates for all the Assessment ID and then deleting them

[amien]

Thanks alot both!

[amien]

just implemented it .. it's briljant! Thanks alot!