1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
| SELECT SUM(tmp.nombre) as somme, tmp.date as date FROM (
(SELECT count(*) as nombre, to_char(datecreation, 'YYYY-MM') as date FROM archive WHERE idann IN (SELECT idann FROM annonce an WHERE (opem, opta) IN (
SELECT DISTINCT opem, opta FROM annonce an
WHERE statut = 'OK' AND dateannonce < date_trunc('month', CURRENT_DATE)
AND dateannonce > date_trunc('month', CURRENT_DATE -360) - INTERVAL '1' MONTH)
AND typeoperation = 'REAPTG' AND statut = 'OK' AND dateannonce < date_trunc('month', CURRENT_DATE)
AND dateannonce > date_trunc('month', CURRENT_DATE -360) - INTERVAL '1' MONTH
AND opta <> 'APNF00' AND opem <> 'APNF00')
AND datecreation > current_date -360 GROUP BY to_char(datecreation, 'YYYY-MM'))
UNION(SELECT count(*) as nombre, to_char(datecreation, 'YYYY-MM') as date FROM archive_longue WHERE idann IN (
SELECT idann FROM annonce an WHERE (opem, opta) IN (
SELECT DISTINCT opem, opta FROM annonce an WHERE statut = 'OK'
AND dateannonce < date_trunc('month', CURRENT_DATE) AND dateannonce > date_trunc('month', CURRENT_DATE -360) - INTERVAL '1' MONTH)
AND typeoperation = 'REAPTG'
AND statut = 'OK'
AND dateannonce < date_trunc('month', CURRENT_DATE)
AND dateannonce > date_trunc('month', CURRENT_DATE -360) - INTERVAL '1' MONTH AND opta <> 'APNF00' AND opem <> 'APNF00') AND datecreation > current_date -360 GROUP BY to_char(datecreation, 'YYYY-MM'))) as tmp GROUP BY tmp.date ORDER BY 2,1 desc; |
Partager