Get Months

[#1]

Hi,

I have those date,

load * Inline
[StartDate,EndDate,OrderId
'2010-10-01','2010-12-01',1
'2010-05-01','2010-07-01',2
];

I want to classify them into:

Order includes how many valid month:

For example:

OrderId Month

1 10

1 11

1 12

How to do it?

Thanks!

[Anonymous]

Hi Isaac,

I would use something like:

tempcalender:
load * Inline
[StartDate,EndDate,OrderId
'2010-10-01','2010-12-01',1
'2010-05-01','2010-07-01',2];
Master_Calendar:
load
Month(StartDate) as StartMonth,
Month(EndDate) as Endmonth,
StartDate,
EndDate,
OrderId
resident tempcalender;
drop Table tempcalender

[#3]

Hi Isaac, try with this:

Original_Table:
load * Inline
[StartDate,EndDate,OrderId
'2010-10-01','2010-12-01',1
'2010-05-01','2010-07-01',2];


Table:
Load iterno() as Iterno,
num(month(StartDate + iterno()-1)) as Time,
OrderId
resident Original_Table
while iterno() <= EndDate-StartDate+1;

Drop table Original_Table;