All Forums General
sunny.j 90 posts Joined 10/10
07 Feb 2013
how to find the start dates from the Transaction history

Hi
 
i'm trying to write a sql to know the start date for a customer , the data looks like
 
Case :1
customer       start date                 end date      transaction
A                   03/12/2008             05/10/2009   1
A                   06/10/2009             12/01/2010   2
A                   13/01/2010             13/01/2010   3
A                   14/01/2010             31/12/9999   4
 
In this scenario the start will be 03/12/2008
Case :2
customer       start date                 end date      transaction
AB                  03/12/1991             05/10/2000     1
AB                  06/10/2000             12/01/2009     2
AB                   13/01/2011             13/06/2011     3
AB                   14/06/2011             31/12/9999     4
In this scenario the start will be 13/01/2011
when i try to write a sql my query  results are showing wrong.
 
any one would  please help me its quite urgent .

sam_dhse 11 posts Joined 07/10
26 Mar 2013

I am sure you have figured out a solution by now.If not , something of this sort should work.
 
SELECT
CUSTOMER,
MIN(start_date) AS start_d,
MAX(end_date) AS end_d,
RANK() OVER(PARTITION BY CUSTOMER ORDER BY end_d DESC) AS rnk QUALIFY rnk=1
FROM
    (
    SELECT
    CUSTOMER,
    start_date,
    end_date,
    SUM(CASE WHEN start_date - prev_end_date > 1 THEN 1 ELSE 0 END) OVER
    (PARTITION BY CUSTOMER
    ORDER BY start_date
    ROWS UNBOUNDED PRECEDING) AS grp
    FROM
        (
        SELECT
        CUSTOMER,
        start_date,
        end_date,
        MIN(end_date) OVER
        (PARTITION BY
        ORDER BY end_date
        ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_end_date
        FROM table
        ) dt
    ) dt
GROUP BY CUSTOMER, grp

You must sign in to leave a comment.