CODEWITHSUNDEEP
postgresqlparaccel
user3652316
user3652316

Reputation: 13

How to calculate count of records between several dates

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

Upvotes: -2

Views: 190

Answers (1)

Angelo Fuchs
Angelo Fuchs

Reputation: 9941

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.

Upvotes: 0

Related Questions