June 20th, 2012, 07:47 AM
, SUM(CASE WHEN status='P'
THEN qty ELSE NULL END) AS status_p
, SUM(CASE WHEN status='R'
THEN qty ELSE NULL END) AS status_r
, SUM(CASE WHEN status = 'P'
( SELECT 'R'
WHERE "date" = t."date"
AND status = 'R' )
THEN qty ELSE NULL END) AS status_p_r
FROM daTable AS t
DATE is a reserved word in many database systems, and needs to be escaped
you did not mention which database system you're using, so i chose to escape the column name using standard sql doublequote delimiters