date picker

[jyothish8807]

Hi All

I am trying to auto populate the dates between MINDATE and MAXDATE:

Name    MINDate   MAXDate

A          Jan 2009   May 2009

Now i want to convert this table into this form:

Name Date

A        Jan 2009

A        Feb 2009

A        Mar 2009

A        Apr 2009

A       May 2009

Thanks & Regards

Jyothish KC

[tresesco · Accepted solution]

PFA.

Temp:
Load * Inline [
Name,    MINDate,   MAXDate

A,          Jan 2009,   May 2009
];

Load
Name,
Date(AddMonths(Date#(MINDate,'MMM YYYY'),IterNo()-1), 'MMM YYYY') as Date
Resident Temp While AddMonths(Date#(MINDate,'MMM YYYY'),IterNo()-1)<= Date#(MAXDate, 'MMM YYYY');

drop Table Temp;

[MK_QSL]

Temp:

Load

  Name,

  Date(Date#(MINDate,'MMM YYYY'),'DD/MM/YYYY') as MinDate,

  Date(Date#(MAXDate,'MMM YYYY'),'DD/MM/YYYY') as MaxDate

Inline

[

  Name, MINDate,  MAXDate

  A,      Jan 2009, May 2009

];

NoConcatenate

Final:

Load

  Name,

  Date(AddMonths(MinDate,IterNo()-1),'MMM YYYY') as Date

Resident Temp

While AddMonths(MinDate,IterNo()-1)<=MaxDate;

Drop Table Temp;

[its_anandrjs]

Load your table like

Tab1:

LOAD * INLINE [

Name,MINDate,MAXDate

A,   Jan 2009,May 2009

];

Tab2:

LOAD

Name,

Num(Date#(MINDate,'MMM YYYY')) AS MinDate,

Num(Date#(MAXDate,'MMM YYYY')) AS MaxDate

Resident Tab1;

DROP Table Tab1;

LET vMin = Peek('MinDate',0,'Tab2');

LET vMax = Peek('MaxDate',0,'Tab2');

NoConcatenate

Data:

Load

  Name,

  Date(AddMonths($(vMin),IterNo()-1),'MMM YYYY') as Date

Resident Tab2

While AddMonths($(vMin),IterNo()-1) <= $(vMax);

Drop Table Tab2;

Regards

[jyothish8807]

can i have the same in SQL?

[jyothish8807]

Can you please share the same in SQL?

[jyothish8807]

Can you please share the same in SQL also?

[tresesco]

What exactly do you want to perform in SQL?

[anbu1984]

In Oracle sql

SELECT SRC_TBL.MinDate,

    SRC_TBL.MaxDate,

    ADD_MONTHS(TRUNC(SRC_TBL.MinDate,'MM'),RNO-1) MTH_STRT_DT

  FROM SRC_TBL

  LEFT OUTER JOIN

    (SELECT ROWNUM RNO FROM DUAL CONNECT BY LEVEL <= 240 --Replace 240 by maximum no of months between min and max date

    ) RW

  ON 1 = 1

  WHERE ADD_MONTHS(TRUNC(MinDate,'MM'),RNO-1) <= MaxDate

[jyothish8807]

Its not working. I am writing this in ms access.