Valid between date fields

[#1]

Hi Dear QV Comunity

I Have this table

Id	BEGDATE	ENDDATE	STATUS
50018897	01/01/2010	01/12/2010	2
50018897	01/12/2010	31/05/2011	0
50018897	01/06/2012	31/12/2012	2
50021285	01/01/2010	31/05/2011	2
50021285	01/06/2011	31/10/2012	0
50021285	01/11/2012	31/12/2012	2
50081478	01/01/2010	01/12/2010	2
50081478	01/12/2010	31/05/2011	0
50081478	01/06/2012	31/12/2012	2
50083229	01/01/2010	31/05/2011	2
50083229	01/06/2011	31/10/2012	0
50083229	01/11/2012	31/12/2012	2
53011210	01/01/2010	01/12/2010	2
53011210	01/12/2010	31/05/2011	0
53011210	01/06/2012	31/12/2012	2
53028587	01/01/2010	31/05/2011	2
53028587	01/06/2011	31/10/2012	0
53028587	01/11/2012	31/12/2012	2
53045666	01/01/2010	31/05/2011	2
53045666	01/06/2011	31/10/2012	0
53045666	01/11/2012	31/12/2012	2

An a Calendar table with dates between 01/01/2010 to 31/12/2012 bya by day

I need know the Quantity of ID With Status = 2 for each month. I trieda with the sample of cookbook (Counter Interval match) but the memory is out, Somebody can help me

[RedSky001]

See the script below.

Then create a chart with a dimenioon of:

=MonthName(Date)

and expresison of:

count({$<STATUS={'2'}>}DISTINCT Id)

Bit of a rush but hopefully is what you wanted.

Mark

data:

load * Inline [Id,          BEGDATE,          ENDDATE,          STATUS

50018897,          01/01/2010,          01/12/2010,          2

50018897,          01/12/2010,          31/05/2011,          0

50018897,          01/06/2012,          31/12/2012,          2

50021285,          01/01/2010,          31/05/2011,          2

50021285,          01/06/2011,          31/10/2012,          0

50021285,          01/11/2012,          31/12/2012,          2

50081478,          01/01/2010,          01/12/2010,          2

50081478,          01/12/2010,          31/05/2011,          0

50081478,          01/06/2012,          31/12/2012,          2

50083229,          01/01/2010,          31/05/2011,          2

50083229,          01/06/2011,          31/10/2012,          0

50083229,          01/11/2012,          31/12/2012,          2

53011210,          01/01/2010,          01/12/2010,          2

53011210,          01/12/2010,          31/05/2011,          0

53011210,          01/06/2012,          31/12/2012,          2

53028587,          01/01/2010,          31/05/2011,          2

53028587,          01/06/2011,          31/10/2012,          0

53028587,          01/11/2012,          31/12/2012,          2

53045666,          01/01/2010,          31/05/2011,          2

53045666,          01/06/2011,          31/10/2012,          0

53045666,          01/11/2012,          31/12/2012,          2

];

temp:

load min(BEGDATE) as minDate

,min(ENDDATE) as MaxDate

Resident data;

LET vDateMin = peek('minDate',0,'temp');

LET vDateMax = peek('MaxDate',0,'temp');

DROP TABle temp;

Calendar: 

load

Date($(vDateMin) + RowNo() - 1) AS Date

AUTOGENERATE 1 

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

IntervalMatch:

IntervalMatch (Date) load DISTINCT BEGDATE , ENDDATE

Resident data;

          MAP_Date:

          Load

          (BEGDATE & '+' & ENDDATE)                                                                                           AS DateKey

          ,Date

          Resident IntervalMatch;

          drop table IntervalMatch;

          // 3.2 Join the indvidual dates back to the main table using the composite key

          ALL:

          NoConcatenate

          load *

          ,(BEGDATE & '+' & ENDDATE) as DateKey

          Resident data;

          INNER join (ALL)

                    load *

                    Resident MAP_Date;

          Drop tables MAP_Date, data;

          drop field DateKey;