Reusing query result in subqueries

I have this ugly request ....

sum(CASE 
        WHEN effective_from_date < '2011-05-24' THEN (rate * (effective_to_date - '2011-05-24' + 1)) 
        WHEN effective_to_date > '2011-05-28' THEN (rate * ('2011-05-28' - effective_from_date + 1)) 
        ELSE (rate * (effective_to_date - effective_from_date + 1))
    END
    ) as price_cal_rate
        FROM calendar_event
        WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314') AND 
        ((effective_from_date BETWEEN '2011-05-24' AND '2011-05-28')  OR (effective_to_date BETWEEN '2011-05-24' AND '2011-05-28')) 
         AND 
         NOT EXISTS (

         SELECT days_diff FROM (


        SELECT  ((effective_from_date - lag(effective_to_date) OVER (PARTITION BY NULL ORDER BY effective_from_date ASC))) AS days_diff, effective_from_date, effective_to_date
             FROM calendar_event
             WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314') AND 
        ((effective_from_date BETWEEN '2011-05-26' AND '2011-05-28') OR (effective_to_date BETWEEN '2011-05-26' AND '2011-05-28')) 


        ) AS t WHERE COALESCE(days_diff, 0) > 1 

        ) AND EXISTS (select * from  (
          select min(effective_from_date) as min_date, max(effective_to_date) as max_date FROM calendar_event
        WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314') AND 
        ((effective_from_date BETWEEN '2011-05-24' AND '2011-05-28')  OR (effective_to_date BETWEEN '2011-05-24' AND '2011-05-28'))
        ) as max_min WHERE min_date <= '2011-05-24' and max_date >= '2011-05-28')

the request calculates the speed by the date range .... the request is fine ... but there is a lot of duplication in the request .... I was wondering if there is a good way to store the result of this auxiliary request somewhere

FROM calendar_event
        WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314') 
AND 
            ((effective_from_date BETWEEN '2011-05-24' AND '2011-05-28')  OR (effective_to_date BETWEEN '2011-05-24' AND '2011-05-28'))  

and use it during my request ....

+3
source share
1 answer

You can use the temporary table as "mu is too short", but if you only need the result in one "main" query, and you use PostgreSQL 8.4 or higher, you can also use with queries

+5
source

All Articles