Board index » delphi » SQL sum, grouped by date intervals. How to?

SQL sum, grouped by date intervals. How to?

I want to sumarize the records in one table grouped in
"age-segments". The segment specs is in another table.
I could pass the dates as parameters to the TQuery if its
difficult to use two tables in the SQL.

Table structures (Paradox):
TableA             TableB
----------         -------------
TransType S*         Intervall   A1*
Date      D*         StartDate   D
Amount    N          EndDate     D

Sample records:
TableA  (money to sum)
-------------------
1   01-Jan-97   100
1   10-Jun-96   500
1   17-Mar-98   150
1   01-Feb-99   200
2   01-Jun-96   50
2   01-Jul-98   300

TableB  (date segments)
--------------------
A   01-Jan-95   30-Jun-96
B   01-Jul-96   20-Feb-98
C   21-Feb-98   01-Jan-2010

Q: I want, for a given transType, have in this case three sums. one sum
   in each intervall (A,B,C)

The above example tables should give the following for transType=1 :
A  600
B  150
C  200

Is this possible with local SQL?

A big PreThank's to anyone with a sugestion.
- Michael

 

Re:SQL sum, grouped by date intervals. How to?


Here is the *tested* solution.  I even used your sample data and
noticed that your example solution set is incorrect.

The solution:
Based on your example, the TQuery's SQL property should be
   SELECT TableB.Intervall, SUM(TableA.Amount)
     FROM TableA, TableB
     WHERE (TransType = :TransType)
       AND (TableA.Date >= TableB.StartDate)
       AND (TableA.Date <= TableB.EndDate)
     GROUP BY TableB.Intervall

The correct result set for your example should be:
 A  500
 B  100
 C  350

Don't forget to define the parameter, datasource, etc.

Quote
Michael Sageryd wrote:

> I want to sumarize the records in one table grouped in
> "age-segments". The segment specs is in another table.
> I could pass the dates as parameters to the TQuery if its
> difficult to use two tables in the SQL.

> Table structures (Paradox):
> TableA             TableB
> ----------         -------------
> TransType S*         Intervall   A1*
> Date      D*         StartDate   D
> Amount    N          EndDate     D

> Sample records:
> TableA  (money to sum)
> -------------------
> 1   01-Jan-97   100
> 1   10-Jun-96   500
> 1   17-Mar-98   150
> 1   01-Feb-99   200
> 2   01-Jun-96   50
> 2   01-Jul-98   300

> TableB  (date segments)
> --------------------
> A   01-Jan-95   30-Jun-96
> B   01-Jul-96   20-Feb-98
> C   21-Feb-98   01-Jan-2010

> Q: I want, for a given transType, have in this case three sums. one sum
>    in each intervall (A,B,C)

> The above example tables should give the following for transType=1 :
> A  600
> B  150
> C  200

> Is this possible with local SQL?

> A big PreThank's to anyone with a sugestion.
> - Michael

Other Threads