I'm stuck. I have a PTO calendar that I'm pulling for Sharepoint that gives me a start date and a stop date.
LOAD subfield(ows_Author,'',2) as LanId,
ows_EventDate as PTOStartDate,
ows_EndDate as PTOStopDate,
ows__ModerationStatus AS PTOAPPROVESTATUS,
ows_fAllDayEvent as ALLDAY,
subfield(ows_LanID,'',2) AS EXCEPTIONLANID,
if(ALLDAY=0,interval(ows_EndDate-ows_EventDate,'D hh:mm'),interval(round(interval(ows_EndDate-ows_EventDate,'D hh:mm')),'D hh:mm')) as TimeOff FROM [http://teamspace/sites......
I have another table giving me days something happened in the system.
PND_POL_NO
,
LOB_DESC,
ACTIONDATE as DATE,
UNDERWRITER as LanId,
FINAL_ACT_UW_ID,
IF(RQMT_ID<>'NERCOR' AND ISNULL(FINAL_ACT_UW_ID) AND LOB_DESC<>'CRITICAL ILLNESS' OR LOB_DESC<>'LIFE' OR LOB_DESC<>'GROUP',1) AS AHPROCESSED,
IF(RQMT_ID='NERCOR',1) AS SENTTOSS,
IF(FINAL_ACT_UW_ID>0 AND LOB_DESC<>'CRITICAL ILLNESS' OR LOB_DESC<>'LIFE' OR LOB_DESC<>'GROUP',1) AS AHRESOLVED,
IF(LOB_DESC='CRITICAL ILLNESS' AND RQMT_ID='NERCOR' AND FINAL_ACT_UW_ID>0,1) AS CIRESOLVED,
IF(LOB_DESC='CRITICAL ILLNESS' AND RQMT_ID<>'NERCOR' AND ISNULL(FINAL_ACT_UW_ID),1) AS CIPROCESSED,
IF(LOB_DESC='LIFE' AND RQMT_ID='NERCOR' AND FINAL_ACT_UW_ID>0,1) AS LIFERESOLVED,
IF(LOB_DESC='LIFE' AND RQMT_ID<>'NERCOR' AND ISNULL(FINAL_ACT_UW_ID),1) AS LIFEPROCESSED,
IF(LOB_DESC='GROUP' AND RQMT_ID='NERCOR' AND FINAL_ACT_UW_ID>0,1) AS GROUPRESOLVED,
IF(LOB_DESC='GROUP' AND RQMT_ID<>'NERCOR' AND ISNULL(FINAL_ACT_UW_ID),1) AS GROUPPROCESSED RESIDENT CALC;
I need to know if I pick a date (second table) for a LanID (second table) is it between a PTOStartDate (fist table) and PTOStopDate (first table). The LanID could have multiple instances of PTOStartDate and StopDate.
The answer to your problem is INTERVALMATCH, but I'm not sure how it's going to behave with multiple ranges by LanID - try it out and see if you get correct results.
