How do i subtract a number of days from a prepared statement parameter in db2?
Just the cross join bit:

SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS ,
    SUM(CALC.SUBTOTAL) AS REVENUE 
FROM (
        SELECT 
            CASE
                WHEN O.DATE &lt; the_param - 21 DAYS THEN 1
                WHEN O.DATE &gt;= the_param - 21 DAYS AND O.DATE &lt; the_param - 14 DAYS THEN 2
                WHEN O.DATE &gt;= the_param - 14 DAYS AND O.DATE &lt; the_param - 7 DAYS THEN 3
                WHEN O.DATE &gt;= the_param - 7 DAYS THEN 4
            END AS WEEK, 
            O.ID, O.SUBTOTAL
        FROM COMPANY.ORDERS AS O INNER JOIN 
            COMPANY.LOCATIONS AS L ON O.LOCATION_ID = L.ID INNER JOIN 
            COMPANY.STORES AS S ON O.STORE_ID = S.ID CROSS JOIN
            (SELECT ? as the_param FROM SYSIBM.SYSDUMMY1)
        WHERE O.DATE &gt;= the_param - 28 DAYS
            AND O.DATE &lt;= the_param
            AND S.NUMBER = ?
    ) AS CALC
GROUP BY CALC.WEEK


you might need to give the subquery an alias—not sure if that is mandatory on DB2