Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
use interval match function
Any other suggestion friends.
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;
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
Use date(YourFieldName, 'MMM YYYY')
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
Can you please share the same in SQL ?
Regards
KC
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