Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have startdate and enddate for every record in a table. i want to create all the dates between start and enddate.
suppose if i have data like this:
ID | Start Date | End Date |
---|---|---|
10 | 01/01/2013 | 05/01/2013 |
67 | 03/02/2013 | 08/02/2013 |
Result output:
ID | DATE |
---|---|
10 | 01/01/2013 |
10 | 02/01/2013 |
10 | 0301/2013 |
10 | 04/01/2013 |
10 | 05/01/2013 |
67 | 03/02/2013 |
67 | 04/02/2013 |
67 | 05/02/2013 |
67 | 06/02/2013 |
67 | 07/02/2013 |
67 | 08/02/2013 |
Please help me to get this result.
Thanks
Aashish
Try this:
temp:
LOAD * Inline
[
ID,StartDate,EndDate
10,01/01/2013,05/01/2013
67,03/02/2013,08/02/2013
];
Result:
LOAD ID, date(StartDate+ IterNo()-1) as DATE_Value
Resident temp
while StartDate+ IterNo() -1 <= EndDate;
HTH
Sushil
Hi, use the intervalmatch
intervalmatch (matchfield)(loadstatement | selectstatement )
LogPedido:
LOAD * INLINE [
Inicio, Termino, Pedido
01:00, 03:35, A
02:30, 07:58, B
03:04, 10:27, C
07:23, 11:43, D
];
LogEvento:
LOAD * INLINE [
Hora, Evento, Comentário
00:00, 0, Início do Turno 1
01:18, 1, Interrupção na Linha
02:23, 2, Reinício da Linha com 50%
04:15, 3, Velocidade da Linha a 100%
08:00, 4, Início do turno 2
11:43, 5, Fim da produção
];
Tabela_Intervalo:
IntervalMatch (Hora) Load
Inicio,
Termino
Resident LogPedido;
Best Regards.
Tonial.
Try this:
temp:
LOAD * Inline
[
ID,StartDate,EndDate
10,01/01/2013,05/01/2013
67,03/02/2013,08/02/2013
];
Result:
LOAD ID, date(StartDate+ IterNo()-1) as DATE_Value
Resident temp
while StartDate+ IterNo() -1 <= EndDate;
HTH
Sushil