1 Reply Latest reply: Nov 19, 2012 12:42 PM by Mark Sheraton RSS

    Valid between date fields

      Hi Dear QV Comunity

       

       

      I Have this table

       

       

       

      IdBEGDATEENDDATESTATUS
      5001889701/01/201001/12/20102
      5001889701/12/201031/05/20110
      5001889701/06/201231/12/20122
      5002128501/01/201031/05/20112
      5002128501/06/201131/10/20120
      5002128501/11/201231/12/20122
      5008147801/01/201001/12/20102
      5008147801/12/201031/05/20110
      5008147801/06/201231/12/20122
      5008322901/01/201031/05/20112
      5008322901/06/201131/10/20120
      5008322901/11/201231/12/20122
      5301121001/01/201001/12/20102
      5301121001/12/201031/05/20110
      5301121001/06/201231/12/20122
      5302858701/01/201031/05/20112
      5302858701/06/201131/10/20120
      5302858701/11/201231/12/20122
      5304566601/01/201031/05/20112
      5304566601/06/201131/10/20120
      5304566601/11/201231/12/20122

       

       

      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

        • Re: Valid between date fields
          Mark Sheraton

          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;