Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Perhaps i am approaching this wrong, but my class documentation (7.0) implied this works with overlapping data and all the samples appears to fail in this case. My sample code is;
periods_tmp:
LOAD * INLINE [
Period, DStart, DEnd
2009-08, 9/1/2009, 5/1/2009
2009-07, 8/1/2009, 4/1/2009
2009-06, 7/1/2009, 3/1/2009
2009-05, 6/1/2009, 2/1/2009
2009-04, 5/1/2009, 1/1/2009
2009-03, 4/1/2009, 12/1/2008
2009-02, 3/1/2009, 11/1/2008
];
periods:
Load Period, Date#(DStart) as PStart, Date#(DEnd) as PEnd
Resident periods_tmp;
drop table periods_tmp;
history:
Load *;
SQL select * from History where createdate > getdate()-100;
LEFT JOIN (history)
Intervalmatch (CREATEDATE)
Load PStart, PEnd Resident [periods];
This fails to get me wahat I want and i don't think its a syntax issue. Any suggestions would help here. i can load the history table with a lot of booleans tied to the periods, make a intermidiate table of periods to history dates and repetitively load it or is there another method I'm missing with the Inertvalmatch
Thanks
periods_tmp:
LOAD * INLINE [
Period, DStart, DEnd
2009-08, 9/1/2009, 5/1/2009
2009-07, 8/1/2009, 4/1/2009
2009-06, 7/1/2009, 3/1/2009
2009-05, 6/1/2009, 2/1/2009
2009-04, 5/1/2009, 1/1/2009
2009-03, 4/1/2009, 12/1/2008
2009-02, 3/1/2009, 11/1/2008
];
periods:
Load Period, Date#(DStart) as PStart, Date#(DEnd) as PEnd
Resident periods_tmp;
drop table periods_tmp;
history:
Load *;
SQL select * from History where createdate > getdate()-100;
LEFT JOIN (history)
Intervalmatch (CREATEDATE)
Select PStart, PEnd Resident periods;
I finally got the right combination of syntax and it worked,
This worked,
periods_tmp:
LOAD * INLINE [
Period, DEnd, DStart
2009-08, 9/1/2009, 5/1/2009
2009-07, 8/1/2009, 4/1/2009
2009-06, 7/1/2009, 3/1/2009
2009-05, 6/1/2009, 2/1/2009
2009-04, 5/1/2009, 1/1/2009
2009-03, 4/1/2009, 12/1/2008
2009-02, 3/1/2009, 11/1/2008
];
//Convert strings to Dates
periods:
Load Period, Date#(DStart) as PStart, Date#(DEnd) as PEnd
Resident periods_tmp;
//Drop Temp Table
drop table periods_tmp;
//Load History and Strip TimeStamp
history:
Load Floor(Createdate) as Createdate, HistoryID;
SQL select Createdate, HistoryID
from History
;
// This Generates a syn$ Table on Start/End - but does the binding.
LEFT JOIN (history)
Intervalmatch (Createdate)
Load PStart, PEnd Resident periods;
// NEED TO DROP SYN$ TABLE !!! - So ...
// Add Period to Table based on PStart/PEnd
LEFT JOIN (history)
LOAD
PStart
,PEnd
,Period
RESIDENT periods;
// Dropping Perios Drops the Syn$ Table too
DROP TABLE periods;
I finally got the right combination of syntax and it worked,
This worked,
periods_tmp:
LOAD * INLINE [
Period, DEnd, DStart
2009-08, 9/1/2009, 5/1/2009
2009-07, 8/1/2009, 4/1/2009
2009-06, 7/1/2009, 3/1/2009
2009-05, 6/1/2009, 2/1/2009
2009-04, 5/1/2009, 1/1/2009
2009-03, 4/1/2009, 12/1/2008
2009-02, 3/1/2009, 11/1/2008
];
//Convert strings to Dates
periods:
Load Period, Date#(DStart) as PStart, Date#(DEnd) as PEnd
Resident periods_tmp;
//Drop Temp Table
drop table periods_tmp;
//Load History and Strip TimeStamp
history:
Load Floor(Createdate) as Createdate, HistoryID;
SQL select Createdate, HistoryID
from History
;
// This Generates a syn$ Table on Start/End - but does the binding.
LEFT JOIN (history)
Intervalmatch (Createdate)
Load PStart, PEnd Resident periods;
// NEED TO DROP SYN$ TABLE !!! - So ...
// Add Period to Table based on PStart/PEnd
LEFT JOIN (history)
LOAD
PStart
,PEnd
,Period
RESIDENT periods;
// Dropping Perios Drops the Syn$ Table too
DROP TABLE periods;