Date with in Mindate and Maxdate

[jyothish8807]

Hello All,

          I have two tables table 1 and table 2:

Table1

Name	Min Date	Max Date
a	1/30/2005	4/20/2005
a	3/30/2005	6/28/2005
b	1/20/2007	3/20/2007

Table 2:

Name	Date
a	1/30/2005
a	2/30/2005
a	3/30/2005
a	4/20/2005
a	5/30/2005
a	6/28/2005
a	7/30/2005
b	1/20/2007
b	3/20/2007

Now for each entries in Table 2 i want to check if the date falls in the range of MIN date and MAX date mention in table 2.We can take those dates as Jan 2005 (Format also) considering month and year only in both tables.

If any month in not mentioned in Table 2 which falls in the range of MIN and MAX Date, the date should be recorded in a separate field corresponding to name in table 1.

Note: In table 1 Same user can have more than two entries.

Regards

Jyothish KC

[er_mohit]

use interval match function

[jyothish8807]

Any other suggestion friends.

[maxgro · Accepted solution]

RESULT

flagin = 1 if date in interval

SCRIPT

SET DateFormat='MM/DD/YYYY';

Table1:

load * inline [

Name, MinDate, MaxDate

a, 1/30/2005, 4/20/2005

a, 3/30/2005, 6/28/2005

b, 1/20/2007, 3/20/2007

];

Table2:

load * inline [

Name, Date

a, 1/30/2005

a, 2/30/2005

a, 3/30/2005

a, 4/20/2005

a, 5/30/2005

a, 6/28/2005

a, 7/30/2005

b, 1/20/2007

b, 3/20/2007

];

Tmp:

NoConcatenate

load Name,

date(MonthStart(MinDate)) as MinDate, date(MonthStart(MaxDate)) as MaxDate Resident Table1;

join (Tmp) load Name, date(MonthStart(Date)) as Date Resident Table2;

DROP Table Table1, Table2;

Final:

load

Name, Date, MinDate, MaxDate,

if(Date<=MaxDate and Date>=MinDate, 1, 0) as FlagIn

Resident Tmp;

DROP Table Tmp;

[jyothish8807]

Hi,

Need few more changes in the above.

Name    MinDate   MAXDate

A          Jan 2009   Mar 2009

Now i want to convert this table into this form:

Name Date

A        Jan 2009

A        Feb 2009

A        Mar 2009

Thanks & Regards

Jyothish KC

[anbu1984]

Use date(YourFieldName, 'MMM YYYY')

[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 you please share the same in SQL ?

Regards

KC

[anbu1984]

Select t2.a, t1.Min_date, t1.Max_date, t2.date, case when t2.date between t1.Min_date and t1.Max_date then 1 else 0 end flag

from Table2 t2, Table1 t1

where t2.a=t1.a