0

A table is given with the columns customer, entry_date, exit_date. I need a query that shows me each customer and counts up the entry_date in 10 day intervals, but only up to the exit_date.

The entry for a customer who does not yet have an issue date has the entry date is in such format '0001-01-01'.

**Customer**   **Entry_date**    **Exit_date**
  Mike           2019-01-01        2019-01-30
  Henry          2019-01-10        0001-01-01
  Thomas         2019-02-08        0001-01-01

Output:

Customer  entry_10    entry_20    entry_30    Count_interval
Mike      2019-01-10  2019-01-10  2019-01-10  3
Henry     2019-01-20  2019-01-30  2019-02-09  3
Thomas    no_data     no_data     no_data     0

My query below doesn't achieve the result.

Select customer, 

case when entry_date + 10 day < curdate() and entry_date < exit_date 
     then entry_date + 10 day else 'no_data' end as 'entry_10',
case when entry_date + 20 day < curdate() and entry_date < exit_date
     then entry_date + 20 day else 'no_data' end as 'entry_20',
case when entry_date + 30day < curdate() and entry_date < exit_date
     then entry_date + 30 day else 'no_data' end as 'entry_30',

case
when entry_date + 30 day < curdate() and entry_date < exit_date
     then '3'
when entry_date + 20 day < curdate() and entry_date < exit_date
     then '2'
when entry_date + 10 day < curdate() and entry_date < exit_date
then '1'
else '0' end as 'Count_interval'

from customer_table

How could I improve my query? I appreciate your tips. Thanks..

  • DB2, but I would also find an SQL command useful and reformat it to DB2. – Butterfly Feb 11 at 22:39
  • What is wrong with your query? – Lennart Feb 12 at 10:52
  • The result for Count_interval for customer Henry should be 3, but my query's result is 0. Because my query looks if entry_date is smaller than exit_date, but exit_date isn't bigger than entr_date, due to format 0001-01-01. – Butterfly Feb 12 at 11:24
  • You can rewrite 0001-01-01 to current_date (or whatever date you would like) like: entry_date < coalesce(nullif(exit_date,'0001-01-01'),current_date) – Lennart Feb 12 at 11:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.