How to calculate count of records between several dates

debugcn Published at Dev

user3652316

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

Angelo Fuchs

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.