-- non equal join with complex join conditions
SELECT ITEM_CNT, FACT.CAL_DT, ORDER_ID, WEEK_BEG_DT FROM
(
    SELECT SUM(ITEM_COUNT) AS ITEM_CNT, CAL_DT, TEST_ORDER.ORDER_ID
    FROM TEST_KYLIN_FACT AS TEST_KYLIN_FACT
    INNER JOIN TEST_ORDER AS TEST_ORDER
    ON TEST_KYLIN_FACT.ORDER_ID = TEST_ORDER.ORDER_ID
    GROUP BY CAL_DT, TEST_ORDER.ORDER_ID
) FACT
LEFT JOIN EDW.TEST_CAL_DT DT
ON FACT.CAL_DT = DT.CAL_DT
    AND WEEK_BEG_DT = DATE'2013-03-24'
    OR (WEEK_BEG_DT < DATE'2013-03-24'
    AND (CASE WHEN ITEM_CNT > 100 THEN ORDER_ID > 10000 ELSE DT.CAL_DT < DATE'2013-04-24' END)
    AND SUBSTRING(CAST(ORDER_ID AS VARCHAR), 1, 2) <> '14')


