How to calculate count of records between several dates

Question

I have a table that looks like:

date         name     lookback_date
1995-01-31   T1       1994-10-24
1995-01-30   T1       1994-10-23
1995-01-29   T1       1994-10-22
1995-01-31   T2       1994-10-24
1995-01-30   T2       1994-10-23
1995-01-29   T2       1994-10-22
1995-01-31   T3       1994-10-24
1995-01-30   T3       1994-10-23
1995-01-29   T3       1994-10-22

and so on

I need to calculate count of names between each record's date and lookback_date

edit: i need a result looks like that:

 date         name     lookback_date  cnt
1995-01-31   T1       1994-10-24     70
1995-01-30   T1       1994-10-23     69
1995-01-29   T1       1994-10-22     67
1995-01-31   T2       1994-10-24     73
1995-01-30   T2       1994-10-23     65
1995-01-29   T2       1994-10-22     63
1995-01-31   T3       1994-10-24     68
1995-01-30   T3       1994-10-23     66
1995-01-29   T3       1994-10-22     65

input date is aquired with this statement

select date, ticker_name
        ,dateadd(days, -100, date) as lookback_date
from table1

`dateadd` is a SQL Server function. Your question is tagged Postgresql. What is the database engine? — Clodoaldo Neto, May 19 '14 at 12:50
PADB. ParAccel cluster. It uses Postgresql with some extra functions. dateadd is among them. — user3652316, May 19 '14 at 12:54

score 0 · Accepted Answer · answered May 19 '14 at 13:05

Try this (I haven't tried it)

SELECT t1.date, t1.ticker_name, dateadd(days, -100, date) AS lookback_date,
    (SELECT count(ticker_name) AS cnt
     FROM table1
     WHERE date > t1.date
           AND date < dateadd(days, -100, date))
FROM table1 t1

It uses a subselect in the SELECT clause that should do it, but needs optimization.

Basically you want to use two selects that reference each other, one that does the date range selection and one that does the counting. Then join them either in the FROM clause or in the SELECT clause.

The FROM clause would be better as it is better readable and thus better maintainable.

and that helped. with couple of tweaks, but your idea worked. thanks! — user3652316, May 19 '14 at 13:37

How to calculate count of records between several dates

1 Answers1