sql - Counting the number of returning item for a timeframe -

context:

for every item returned, need know how many time "this" item return in different timeframe: 30,60,90,120,180,365 days.

an item unique based on serial (itm_item_serial).

sample data:

complete sample creation script, , expected result here*.

create table itemreturn (      [itm_id] int,       [itm_item_serial] int,       [itm_cdate] datetime );  insert itemreturn ([itm_id], [itm_item_serial], [itm_cdate]) values            (1, 1, '2016-10-02 02:00:00'),     (2, 1, '2016-09-03 02:00:00'),     (3, 1, '2016-11-03 01:00:00')    ; 

expected result: itm_item_serial = 1

itm_id    30d    60d    90d    120d    180d    365d 1          0      0      0      0       0        0 2          1      0      0      0       0        0 3          1      1      0      0       0        0 

0 or null if there no return in time frame.

how work: itm_item_serial = 1

[itm_id]  [itm_item_serial] [itm_cdate]  1,         1,               '2016-10-02 02:00:00' 2,         1,               '2016-09-03 02:00:00' 3,         1,               '2016-11-03 01:00:00' 

• for [itm_id]=1, there 0 previous return.
• for [itm_id]=2, there 1 previous return
  • on '2016-10-02'. datediff = 29. there 1 return in timeframe "0-30 days".
• for [itm_id]=3, there 2 previous return.
  • on '2016-09-03'. datediff = 60. there 1 return in timeframe "30-60 days".
  • on '2016-10-02'. datediff = 31. there 1 return in timeframe "30-60 days".

*: rextester data sample, , ordered data sample.

sql fiddle: http://sqlfiddle.com/#!6/00460/4

does not give result provided provide counts various day lapses

; data_cte (     select         m1.itm_cdate         , datediff(day, m1.itm_cdate, m2.itm_cdate) daydiff         , m1.itm_id         , m1.itm_item_serial     itemreturn m1         left outer join itemreturn m2             on m1.itm_item_serial = m2.itm_item_serial     m1.itm_cdate < m2.itm_cdate         , m1.itm_cdate > dateadd(day, -30, m2.itm_cdate) ), aggr_cte (     select          itm_id, itm_item_serial         , case              when daydiff < 30 '30d'             when daydiff < 60 '60d'             when daydiff < 90 '90d'             when daydiff < 120 '120d'             when daydiff < 180 '180d'             else '365d'         end daylapse     data_cte )select      itm_id, [30d], [60d], [90d], [120d], [180d], [365d] (     select itm_id, itm_item_serial, daylapse     aggr_cte ) src pivot (     count(itm_item_serial)     daylapse in ([30d], [60d], [90d], [120d], [180d], [365d]) ) pivottable 

data_cte result (output):

itm_cdate               daydiff     itm_id      itm_item_serial ----------------------- ----------- ----------- --------------- 2016-09-03 02:00:00.000 29          2           1 2016-09-03 02:00:00.000 29          3           1 2016-09-03 02:00:00.000 29          5           2 2016-09-03 02:00:00.000 29          13          6 2016-08-05 02:00:00.000 29          15          6 2016-08-04 02:00:00.000 1           14          6 

update: 2017-07-26

final version of query

; data_cte (       select       itm_id       , itm_item_serial       , row_number() on (partition itm_item_serial order itm_item_serial) itm_item_rownum       , itm_cdate       [itemreturn] ir1   ), date_cte (     select          d2.itm_id         , d1.itm_item_serial         , datediff(day, d1.itm_cdate, d2.itm_cdate) daydiff      data_cte d1, data_cte d2      d1.itm_item_serial = d2.itm_item_serial         , datediff(day, d1.itm_cdate, d2.itm_cdate) >= 0 ), aggr_cte (     select          itm_id, itm_item_serial         , case              when daydiff <= 0 '0d'             when daydiff <= 30 '30d'             when daydiff <= 60 '60d'             when daydiff <= 90 '90d'             when daydiff <= 120 '120d'             when daydiff <= 180 '180d'             else '365d'         end daylapse     date_cte )select      itm_id, [30d], [60d], [90d], [120d], [180d], [365d] (     select itm_id, itm_item_serial, daylapse     aggr_cte ) src pivot (     count(itm_item_serial)     daylapse in ([0d], [30d], [60d], [90d], [120d], [180d], [365d]) ) pivottable