Sum for multiple date ranges in a single call?

Question

I have the following query:

SELECT 
   SUM("balance_transactions"."fee") AS sum_id 
   FROM "balance_transactions" 
   JOIN charges ON balance_transactions.source = charges.balance_id 
   WHERE "balance_transactions"."account_id" = 6 
      AND (balance_transactions.type = 'charge' 
      AND charges.refunded = false 
      AND charges.invoice IS NOT NULL) 
      AND ("balance_transactions"."created" BETWEEN '2013-12-20' AND '2014-01-19');

What that does is adds up all the "fees" that occurred between those two dates. Great. Works fine.

The problem is that I almost always need those fees for hundreds of date ranges at a time, which amounts to me running that same query hundreds of times. Not efficient.

But is there some way to condense this into a single query for all the date ranges?

For instance, I'd be calling SUM for a series of ranges like this:

2013-12-20 to 2014-01-19
2013-12-21 to 2014-01-20
2013-12-22 to 2014-01-21
2013-12-23 to 2014-01-22
2013-12-24 to 2014-01-23
...so on and so on

I need to output the sum of fees collected in each date range (and ultimately need that in an array).

So, any ideas on a way to handle that and reduce database transactions?

FWIW, this is on Postgres inside a Rails app.

Answer 1

Well coming from a SQL Server background I would change your where clause to

...
AND (
      "balance_transactions"."created" BETWEEN '2013-12-20' AND '2014-01-19'
      OR
      "balance_transactions"."created" BETWEEN '2013-12-21' AND '2014-01-20'
      OR
      "balance_transactions"."created" BETWEEN '2013-12-23' AND '2014-01-22'
      OR
      "balance_transactions"."created" BETWEEN '2013-12-24' AND '2014-01-23'
    );

Just be sure you have a good index on those dates! :)

Answer 2

if i understand well you want to reutilize the date query. For this the part of the query that can be reutilized is the daily part. I mean:

SELECT 
   SUM("balance_transactions"."fee") AS sum_id 
   FROM "balance_transactions" 
   JOIN charges ON balance_transactions.source = charges.balance_id 
   WHERE "balance_transactions"."account_id" = 6 
      AND (balance_transactions.type = 'charge' 
      AND charges.refunded = false 
      AND charges.invoice IS NOT NULL) 
      AND ("balance_transactions"."created" = 'yyyy-mm-dd');

Assuming that your "created" field is just date and not timestamp, and if the data of past days doesn't change, you can dump this query to a table:

insert into sum_table
SELECT 
   "balance_transactions"."created" balance_created
   SUM("balance_transactions"."fee") AS balance_fee 
   FROM "balance_transactions" 
   JOIN charges ON balance_transactions.source = charges.balance_id 
   WHERE "balance_transactions"."account_id" = 6 
      AND (balance_transactions.type = 'charge' 
      AND charges.refunded = false 
      AND charges.invoice IS NOT NULL) 
   group by "balance_transactions"."created"
;

and then change your main query to:

SELECT 
   SUM(balance_fee) AS sum_id 
   FROM sum_table where balance_created between ('2013-12-20' AND '2014-01-19');

Another optimization is to eliminate the between because usually it does not uses indexes, and if you have lots of different dates it can be slow.

Better this way:

SELECT 
   SUM(balance_fee) AS sum_id 
   FROM sum_table where balance_created in ('2013-12-20', '2013-12-21', '2013-12-22' ... '2014-01-19');

But for this you have to create the SQL directly in the client application (ej. DAO)

Hope this helps.

Answer 3

Assuming I understand your request correctly I think what you need is something along these lines:

SELECT "periods"."start_date", 
       "periods"."end_date", 
       SUM(CASE WHEN "balance_transactions"."created" BETWEEN "periods"."start_date" AND "periods"."end_date" THEN "balance_transactions"."fee" ELSE 0.00 END) AS period_sum
  FROM "balance_transactions" 
  JOIN charges ON balance_transactions.source = charges.balance_id 
  JOIN ( SELECT '2013-12-20'::date as start_date, '2014-01-19'::date as end_date UNION ALL
         SELECT '2013-12-21'::date as start_date, '2014-01-20'::date as end_date UNION ALL
         SELECT '2013-12-22'::date as start_date, '2014-01-21'::date as end_date UNION ALL
         SELECT '2013-12-23'::date as start_date, '2014-01-22'::date as end_date UNION ALL
         SELECT '2013-12-24'::date as start_date, '2014-01-23'::date as end_date
         ) as periods
    ON "balance_transactions"."created" BETWEEN "periods"."start_date" AND "periods"."end_date"
 WHERE "balance_transactions"."account_id" = 6 
   AND "balance_transactions"."type" = 'charge' 
   AND "charges"."refunded" = false 
   AND "charges"."invoice" IS NOT NULL
 GROUP BY "periods"."start_date", "periods"."end_date"

This should return you all the periods you're interested in in one single resultset. Since the query is 'generated' on the fly in your front-end you can add as many rows to the periods part as you want.

Edit: with some trial and error I managed to get it working [in sqlFiddle][1] and updated the syntax above accordingly.

Answer 4

Here's a untested procedure you can used.

CREATE OR REPLACE PROCEDURE sum_fees(v_start IN Date, v_end in Date) IS

BEGIN
  SELECT 
   SUM("balance_transactions"."fee") AS sum_id 
   FROM "balance_transactions" 
       JOIN charges ON balance_transactions.source = charges.balance_id 
   WHERE "balance_transactions"."account_id" = 6 
      AND (balance_transactions.type = 'charge' 
      AND charges.refunded = false 
      AND charges.invoice IS NOT NULL) 
      AND ("balance_transactions"."created" BETWEEN v_start AND v_end);
END;

Then call the procedure with your range date.

Answer 5

SELECT periods.start_date, 
     periods.end_date, 
     SUM(fee) AS Period_Sum
FROM "balance_transactions" 
JOIN charges ON balance_transactions.source = charges.balance_id 
JOIN
(SELECT CAST('2013-12-20' AS DATE) AS start_date, CAST('2014-01-19' AS DATE) AS end_date UNION     ALL
 SELECT  CAST('2013-12-21' AS DATE),CAST('2014-01-20'  AS DATE) UNION ALL
 SELECT  CAST('2013-12-22' AS DATE),  CAST('2014-01-21' AS DATE) UNION ALL
 SELECT CAST('2013-12-23' AS DATE),  CAST('2014-01-22' AS DATE) UNION ALL
 SELECT  CAST('2013-12-24' AS DATE), CAST('2014-01-23' AS DATE)) as periods
ON "balance_transactions"."created" BETWEEN periods.start_date AND periods.end_date
WHERE "balance_transactions"."account_id" = 6 
AND (balance_transactions.type = 'charge' 
AND charges.refunded = false 
AND charges.invoice IS NOT NULL) 
GROUP BY periods.start_date, periods.end_date

Here is link to SQL Fiddle Where I tested it: http://sqlfiddle.com/#!10/535ac/11/0

Answer 6

Try this:

create table timeframes (
    start_dt date,
    end_dt date
);

insert into timeframes values ('2013-12-20', '2014-01-19');
insert into timeframes values ('2013-12-21', '2014-01-20');
insert into timeframes values ('2013-12-22', '2014-01-21');
insert into timeframes values ('2013-12-23', '2014-01-22');
insert into timeframes values ('2013-12-24', '2014-01-23');

SELECT 
    tf.start_date, 
    tf.end_date, 
    SUM(CASE 
        WHEN t.created BETWEEN tf.start_date AND tf.end_date THEN t.fee
        ELSE 0.00 
    END) as transaction_sum
FROM 
    balance_transactions t
INNER JOIN 
    charges c
ON 
    t.source = c.balance_id 
INNER JOIN 
    timeframes tf
ON 
    t.created BETWEEN tf.start_date AND tf.end_date
WHERE 
    t.account_id = 6
AND 
    (
    t.type = 'charge' 
        AND 
    c.refunded = false 
        AND 
    c.invoice IS NOT NULL
    ) 
GROUP BY
    tf.start_date, 
    tf.end_date