I have a table (Sybase IQ) that contains data for different customers and their customer value from a certain point of time:
Old Customer Value Table:
customer_id | load_date | customer_value
5 | 2016-01-01 | Basic
5 | 2016-04-01 | Premium
5 | 2016-08-01 | Gold
6 | 2016-01-01 | Basic
6 | 2016-04-01 | Premium
6 | 2016-08-01 | Gold
7 | 2016-01-01 | Basic
7 | 2016-04-01 | Premium
7 | 2016-08-01 | Gold
To join this table with other tables I have to know what was the valid customer_value for a customer in a specific timeframe. So the idea was to transform the above table and create a new table with the following form.:
New Customer Value Table To Create
customer_id | date_valid_from | date_valid_to | customer_value
5 | 2016-01-01 | 2016-03-31 | Basic
5 | 2016-04-01 | 2016-08-24 | Premium
5 | 2016-08-25 | NULL | Gold
6 | 2016-01-01 | 2016-03-31 | Basic
6 | 2016-04-01 | 2016-08-24 | Premium
6 | 2016-08-25 | NULL | Gold
7 | 2016-01-01 | 2016-03-31 | Basic
7 | 2016-04-01 | 2016-08-24 | Premium
7 | 2016-08-25 | NULL | Gold
Sadly I can't find any solution for an SQL query that creates me the desired output.
Use lead to get the date on the next row and subtract 1 day from it to get the end date of a period.
select customer_id,date_valid_from,
dateadd(day,-1,next_date) date_valid_to,customer_value
from (
select
customer_id,
load_date as date_valid_from,
lead(load_date) over(partition by customer_id order by load_date) as next_date,
customer_value
from tablename
) x