how to find the start dates from the Transaction history

General
Enthusiast

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 .

1 REPLY
Enthusiast

Re: how to find the start dates from the Transaction history

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