3 Replies Latest reply: Nov 3, 2010 4:30 AM by Sjoerd van den Bergh RSS

    How to count days on road with different dimensions

    Sjoerd van den Bergh

      Hi,

      please have a look at attached QV doc. I need to be able obtain the number of tripdays by selecting for example two months (see example) and not by using the 'LINK' field.

      Available datamodel is one that gives me an ugly solution. I know there should be a much more sofisticated and simple answer to this issue. Please help.

       

       

       

       



       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       





       

       

       

       

       

       

       

       

       

       

       

       

       

       

       





       

       

       

       

       

       

       



       

      DATA:
      LOAD Transport,
      Date(Date) as Date,
      MonthStart(Date) as CalendarMonthStart,
      QuarterStart(Date) as CalendarQuarterStart,
      YearStart(Date) as CalendarYearStart,
      MonthEnd(Date) as CalendarMonthEnd,
      QuarterEnd(Date) as CalendarQuarterEnd,
      YearEnd(Date) as CalendarYearEnd,
      Month(Date) as Month,
      Year(Date) as TRIPYEAR,
      Year,
      Trip,
      Transport&Year&Trip as Link
      FROM TRIPLEDGER);

      join LOAD Transport&Year&Trip as Link,
      Enddate,
      Startdate,
      num(Floor(Enddate-Startdate)+2) as Tripdays
      FROM table is dates);

      left join (DATA) INTERVALMATCH (Date)
      LOAD DISTINCT Startdate, Enddate
      RESIDENT [DATA];

      join (DATA) load
      Link,
      if(year(Enddate)>Year(Startdate), (1+num(Floor(Enddate-CalendarYearStart))),Tripdays) as Tripdays2,
      if(Year(Enddate)>=Year(Date), '1', '0') as FLAGTripdays
      Resident [DATA];

       

      Thanks in advance,



      Sjoerd