dates between dates

Tue, 13 Oct 2009 22:40:57 GMT

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.

dates between dates

Oleg_Troyansky — Tue, 13 Oct 2009 23:02:43 GMT

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.

Oleg

topic dates between dates in QlikView

dates between dates

dates between dates