Dan Kelly Dan Kelly - 6 months ago 11
SQL Question

MIN date from within GROUP

I'm trying to work out if the following is possible within a single query.

We have the following Query that gets information from Tables A and B

SELECT
a.wj_job
,a.wj_sched
,a.wj_lump
,SUM(b.wj_billed) AS wj_billed
,SUM(b.wj_received) AS wj_recd
,a.wj_lbdate
,a.wj_fecom1
FROM ag_fsch a
JOIN ag_fschd b ON a.wj_jbnum = b.wj_jbnum AND a.wj_sched = b.wj_sched
WHERE
a.wj_jbnum LIKE "4733.%"
GROUP BY
a.wj_jbnum
,a.wj_sched
ORDER BY
a.wj_jbnum
,a.wj_sched


I would like to extract the last unpaid bill date from table b as part of the above Query. The same thing can be achieved by the query below, but only for a specific result in the original Query.

SELECT
MIN(wj_bdate)
FROM ag_fschd
WHERE
wj_jbnum = 4733.00
AND wj_sched = 6
AND wj_billed <> 0
AND wj_received = 0

Answer

I think you can use conditional aggregation. I'm not sure because there are two keys in the group by, not just the job number. But, this may be the output you want:

SELECT a.wj_job, a.wj_sched, a.wj_lump, SUM(b.wj_billed) AS wj_billed,
       SUM(b.wj_received) AS wj_recd, a.wj_lbdate, a.wj_fecom1,
       MIN(CASE WHEN b.wj_sched = 6 AND b.wj_billed <> 0 AND b.wj_received = 0
                THEN wj_bdate
           END)
FROM ag_fsch a JOIN
     ag_fschd b
     ON a.wj_jbnum = b.wj_jbnum AND a.wj_sched = b.wj_sched 
WHERE a.wj_jbnum LIKE '4733.%'
GROUP BY a.wj_jbnum, a.wj_sched 
ORDER BY a.wj_jbnum, a.wj_sched;