# Detect consecutive dates ranges using q?

**URL:** <https://forum.kx.com/t/detect-consecutive-dates-ranges-using-q/11859>\
**Category:** Community Support\
**Tags:** kdb-and-q\
**Created:** [January 18, 2019, 10:15pm UTC](https://forum.kx.com/t/detect-consecutive-dates-ranges-using-q/11859 "2019-01-18T22:15:00Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Shawneee1](https://avatars.discourse-cdn.com/v4/letter/s/ecc23a/32.png) [@Shawneee1](https://forum.kx.com/u/Shawneee1)\
**Post date:** [January 18, 2019, 10:15pm UTC](https://forum.kx.com/t/detect-consecutive-dates-ranges-using-q/11859/1 "2019-01-18T22:15:00Z")

</div>

Hi,

I am new to q. I am trying to find continuous time range for table below? Maybe it’s easy but I can’t figure it out on Friday afternoon…

q)show test:( c1:`a`a`a`b`b`a`a`a`b`b`a; c2: 2016.01.01+til 11)

c1 c2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

-------------&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

a&nbsp; 2016.01.01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

a&nbsp; 2016.01.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

a&nbsp; 2016.01.03&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

b&nbsp; 2016.01.04&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

b&nbsp; 2016.01.05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

a&nbsp; 2016.01.06&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

a&nbsp; 2016.01.07&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

a&nbsp; 2016.01.08&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

b&nbsp; 2016.01.09&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

b&nbsp; 2016.01.10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

a&nbsp; 2016.01.11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;

how do I query it so that I can get:

c1 StartDate&nbsp; &nbsp; &nbsp;EndDate

* * *

a&nbsp; &nbsp;2016.01.01&nbsp; 2016.01.03

b&nbsp; &nbsp;2016.01.04&nbsp; 2016.01.05

a&nbsp; &nbsp;2016.01.06&nbsp; 2016.01.08

b&nbsp; &nbsp;2016.01.09&nbsp; 2016.01.10

a&nbsp; &nbsp;2016.01.11&nbsp; 2016.01.11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;

Many thanks!

Shaw

---

<div class="post-metadata">

**Author:** ![jwbuitenhuis](https://avatars.discourse-cdn.com/v4/letter/j/45deac/32.png) [@jwbuitenhuis](https://forum.kx.com/u/jwbuitenhuis)\
**Post date:** [January 19, 2019, 1:44pm UTC](https://forum.kx.com/t/detect-consecutive-dates-ranges-using-q/11859/2 "2019-01-19T13:44:00Z")

</div>

Use differ, and reverse to get the endings:

q)test:( c1:`a`a`a`b`b`a`a`a`b`b`a; c2: 2016.01.01+til 11)

q)select c1, start:c2, end:(exec reverse c2 from reverse test where differ c1) from test where differ c1

c1 start&nbsp; &nbsp; &nbsp; end&nbsp; &nbsp; &nbsp; &nbsp;

* * *

a&nbsp; 2016.01.01 2016.01.03

b&nbsp; 2016.01.04 2016.01.05

a&nbsp; 2016.01.06 2016.01.08

b&nbsp; 2016.01.09 2016.01.10

a&nbsp; 2016.01.11 2016.01.11

---

<div class="post-metadata">

**Author:** ![rahul\_asati041](https://avatars.discourse-cdn.com/v4/letter/r/b5e925/32.png) [@rahul\_asati041](https://forum.kx.com/u/rahul_asati041)\
**Post date:** [January 19, 2019, 3:37pm UTC](https://forum.kx.com/t/detect-consecutive-dates-ranges-using-q/11859/3 "2019-01-19T15:37:00Z")

</div>

Couple of ways to do this:

1. Using groupby

&nbsp;&nbsp;&nbsp; q) value select first c1,startDate:first c2, endDate:last c2 by sums differ c1 from test

1. Take the row indexes where symbol value changes and filter out values from columns using those indexes.

&nbsp;&nbsp;&nbsp; q)&nbsp; r: where differ test`c1  
&nbsp;&nbsp;&nbsp; q)&nbsp; select c1 r, startDate:c2 r, endDate: c2 1\_(r,count test)-1 from test

Second solution performs better than the first.

---

<div class="post-metadata">

**Author:** ![Shawneee1](https://avatars.discourse-cdn.com/v4/letter/s/ecc23a/32.png) [@Shawneee1](https://forum.kx.com/u/Shawneee1)\
**Post date:** [January 19, 2019, 7:48pm UTC](https://forum.kx.com/t/detect-consecutive-dates-ranges-using-q/11859/4 "2019-01-19T19:48:00Z")

</div>

I see, very helpful. Thank you all!&nbsp;

I added extra conditions to make sure c2 is consecutive in days…

works if there is gap between days.&nbsp;

test:( c1:`a`a`a`b`b`a`a`a`b`b`a`a; c2:(2016.01.01+til 11), 2016.02.01)

r: asc distinct ((where differ (1i, 1\_deltas test`c2)), (where differ test`c1))

select c1 r, startDate:c2 r, endDate: c2 1\_(r,count test)-1 from test&nbsp;

r startDate&nbsp; endDate

* * *

a 2016.01.01 2016.01.03

b 2016.01.04 2016.01.05

a 2016.01.06 2016.01.08

b 2016.01.09 2016.01.10

a 2016.01.11 2016.01.11

a 2016.02.01 2016.02.01

Have a great&nbsp;weekend!

&nbsp;

---

<div class="post-metadata">

**Author:** ![Flying1](https://avatars.discourse-cdn.com/v4/letter/f/f4b2a3/32.png) [@Flying1](https://forum.kx.com/u/Flying1)\
**Post date:** [January 20, 2019, 3:29pm UTC](https://forum.kx.com/t/detect-consecutive-dates-ranges-using-q/11859/5 "2019-01-20T15:29:00Z")

</div>

An alternative that might be more intuitive:

`q)test:([] c1:`a`a`a`b`b`a`a`a`b`b`a`a; c2:(2016.01.01+til 11), 2016.02.01)q)select first c1,start:first c2,end:last c2 by sums 1<>(deltas@;c2)fby c1 from testc1| c1 start end--| ------------------------1 | a 2016.01.01 2016.01.032 | b 2016.01.04 2016.01.053 | a 2016.01.06 2016.01.084 | b 2016.01.09 2016.01.105 | a 2016.01.11 2016.01.116 | a 2016.02.01 2016.02.01`