CODEWITHSUNDEEP
sqlgroup-bydb2
ares
ares

Reputation: 4413

database db2: Group by custom date range

I've a table in db2 with columns:

PK (varchar)
COLUMN1 (VARCHAR)
..
..
..
CREATION_TIMESTAMP (TIMESTAMP)

I need counts based on a custom date range say every five days. I'll start with a day like 'Dec 1st 2014' and then group my counts based on 5 day gaps:

group 1 : Dec 1 to Dec 5
group 2 : Dec 6 to Dec 10
...
...
and so on.

If I have to group by pre defined ranges it's easy; I can simply do 

select count(*), week(CREATION_TIMESTAMP) 
from  MYTABLE 
group by week(CREATION_TIMESTAMP);

Similar for day, month, year etc. But is there any way to define custom range as mentioned above?

Upvotes: 0

Views: 2608

Answers (1)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7181

You need a function that maps the creation_time to your wanted intervals. Something like:

select monthname(CREATION_TIMESTAMP)
     , day(CREATION_TIMESTAMP) + 1 - mod(day(CREATION_TIMESTAMP),5)
     , count(1)
from ...
group by monthname(CREATION_TIMESTAMP)
    , day(CREATION_TIMESTAMP) + 1 - mod(day(CREATION_TIMESTAMP),5)

Example:

with t(n) as  ( values date('2014-12-01') 
                union all 
                select n + 1 day from t 
                where n < '2014-12-20'
              ) select distinct day(n) + 1 - mod(day(n),5), day(n) + 5 - mod(day(n),5) 
                from t;

1           2          
----------- -----------
SQL0347W  The recursive common table expression "LELLE.T" may contain an 
infinite loop.  SQLSTATE=01605

      1           5
      6          10
     11          15
     16          20
     21          25

Edit: extended example

with t(n) as  ( 
    values date('2014-12-01') 
    union all 
    select n + 1 day 
    from t where n < '2014-12-20' 
) 
select day(n)
     , mod(day(n),5)
     , day(n) + 1 - mod(day(n),5) 
from t

      1           1           1
      2           2           1
      3           3           1
      4           4           1
      5           0           6
      6           1           6
      7           2           6
      8           3           6
      9           4           6
     10           0          11
     11           1          11
     12           2          11
     13           3          11
     14           4          11
     15           0          16
     16           1          16
     17           2          16
     18           3          16
     19           4          16
     20           0          21

Upvotes: 1

Related Questions