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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.