I Need to count records in next 30 days from start_date?

Question

I am trying to find # of records in next 30 days from start date for each record

I have a table:

Patid             Start_date
1234              1/1/2015
1234              1/10/2015
1234              1/30/2015
1234             2/19/2015
1234              3/5/2015
1234              3/6/2015
1234              3/7/2015

I want to write a simple sql query that should give me the following result:

patid:            Start_Date       #of Records in Next 30 Days
1234              1/1/2015            2
1234              1/10/2015           2
1234              1/30/2015           1
1234              2/19/2015           3  
1234              3/5/2015            2
1234              3/6/2015            1
1234              3/7/2015            0

Best Regards, Sunny

You likely need a subquery that holds COUNT and WHERE Start_Date > [currently selected start date] — qwerty_so, Feb 06 '15 at 20:50

score 1 · Accepted Answer · answered Feb 06 '15 at 20:50

In generic SQL,the easiest way is with a correlated subquery:

select t.*,
       (select count(*)
        from table t2
        where t2.patid = t.patid and
              t2.start_date > t.start_date and
              t2.start_date <= t.start_date + interval '30 days'
       ) as Next30Days
from table t;

This uses ANSI standard syntax for the date arithmetic -- a standard mostly observed in the breach. Each database seems to have its own rules for massaging dates.

I Need to count records in next 30 days from start_date?

1 Answers1