The post describes an interesting example of use of subqueries in Web Intelligence.

The task is to display year-to-day revenue figures.

The universe database structure is the following:

The universe contains the following objects:

• Revenue defined as SUM(INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE)
• Invoice Date objects such as Invoice Date, Month, Quarter, Year.
• Today object defined as SYSDATE (Oracle).

The task can be solved with the following query in Web Intelligence:

The generated SQL is:

SELECT
  SUM(INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE),
  INVOICE_DATE.THE_DATE,
  INVOICE_DATE.YEAR
FROM
  SERVICE INNER JOIN INVOICE_LINE 
  ON (INVOICE_LINE.SERVICE_ID=SERVICE.SERVICE_ID)
  INNER JOIN SALES ON (INVOICE_LINE.INV_ID=SALES.INV_ID)
  INNER JOIN CALENDARDAY  INVOICE_DATE 
  ON (SALES.INVOICE_DATE_ID=INVOICE_DATE.DATE_ID)  
WHERE
  (
   INVOICE_DATE.YEAR  =  ANY 
     (
     SELECT
       INVOICE_DATE.YEAR
     FROM
       CALENDARDAY  INVOICE_DATE
     WHERE
       INVOICE_DATE.THE_DATE  =  SYSDATE
     )
   AND
   INVOICE_DATE.THE_DATE  <=  SYSDATE
  )
GROUP BY
  INVOICE_DATE.THE_DATE, 
  INVOICE_DATE.YEAR