Query Example: Revenue by store for each brand

This example takes the revenue for each brand and calculates the revenue by store.

Products are grouped by store, current week, prior week, and prior month totals.

SELECT STORE_NUMBER,
    SUM(CASE WHEN ((CALENDAR_DATE >= 8/8/2010)
               AND (CALENDAR_DATE < 8/14/2010))
       THEN EXTENDED_PRICE ELSE 0 END) AS CURR_PERIOD,
    SUM(CASE WHEN ((CALENDAR_DATE >= 8/1/2010)
               AND (CALENDAR_DATE <= 8/7/2010))
       THEN EXTENDED_PRICE ELSE 0 END) AS PRIOR_WEEK,
    SUM(CASE WHEN ((CALENDAR_DATE >= 7/1/2010)
              AND (CALENDAR_DATE <= 7/28/2010))
       THEN EXTENDED_PRICE ELSE 0 END) AS PRIOR_MONTH
FROM   PERIOD,PRODUCT,DAILY_SALES,STORE
WHERE  PRODUCT.PRODKEY=DAILY_SALES.PRODKEY
   AND PERIOD.PERKEY=DAILY_SALES.PERKEY
   AND STORE.STOREKEY=DAILY_SALES.STOREKEY
   AND CALENDAR_DATE BETWEEN 7/1/2010 and 8/14/2010
   AND ITEM_DESC LIKE 'NESTLE%'
GROUP BY STORE_NUMBER
ORDER BY STORE_NUMBER;