how to find the start dates from history

Database
Enthusiast

how to find the start dates from 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 :1

customer       start date                 end date      transaction

A                   03/12/1991             05/10/2000     1

A                   06/10/2000             12/01/2009     2

A                   13/01/2011             13/06/2011     3

A                   14/06/2011             31/12/9999     4

In this scenario the start will be 13/11/2011

when i try to write a sql my query  results are showing wrong.

any one would  please help me its quite urgent .

6 REPLIES
Enthusiast

Re: how to find the start dates from history

any one please help me to write the sql to get  this result

Enthusiast

Re: how to find the start dates from history

In your second example there are no customers with start date of 13/11/2011.

Anyways, If you just need the minimum start date against a customer then you can do this...

SELECT CUSTOMER_ID, MIN(START_DATE)   FROM <<TABLE_NAME>> GROUP BY CUSTOMER_ID

Enthusiast

Re: how to find the start dates from history

And If you need the complete record then you can

SELECT TBL1.*
FROM <<TABLE_NAME>> AS TBL1
INNER JOIN
(
SELECT CUSTOMER_ID, MIN(START_DATE) MIN_START_DATE FROM <<TABLE_NAME>> GROUP BY CUSTOMER_ID
) TBL2
ON TBL1.CUSTOMER_ID = TBL2.CUSTOMER_ID
AND TBL1.START_DATE = TBL2.MIN_START_DATE
Enthusiast

Re: how to find the start dates from history

thanks for the reply, yes its a typo error .

in case:2 the start date is 13/01/2011.

Enthusiast

Re: how to find the start dates from history

your second query is not working for case:2 scenario.

any one would please help me

Junior Supporter

Re: how to find the start dates from history

Sunny:

Maybe this approach will help you:

BTEQ -- Enter your SQL request or BTEQ command:

SELECT *

FROM YOUR_TABLE

ORDER BY 1,2;

*** Query completed. 8 rows found. 4 columns returned.

*** Total elapsed time was 1 second.

customer start_date end_date transaction_n

-------- ---------- ---------- -------------

A 2008-12-03 2009-10-05 1

A 2009-10-06 2010-01-12 2

A 2010-01-13 2010-01-13 3

A 2010-01-14 9999-12-31 4

B 1991-12-03 2000-10-05 1

B 2000-10-06 2009-01-12 2

B 2011-01-13 2011-06-13 3

B 2011-06-14 9999-12-31 4

BTEQ -- Enter your SQL request or BTEQ command:

SELECT customer,

start_date,

end_date,

transaction_n

FROM (

SELECT customer,

start_date,

end_date,

transaction_n,

MAX(end_date) OVER (PARTITION BY customer

ORDER BY start_date

ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) prev_end_date

FROM YOUR_TABLE

) pre

WHERE start_date- coalesce(prev_end_date,date '1900-01-01') > 1

QUALIFY ROW_NUMBER() OVER (PARTITION BY customer ORDER BY transaction_n DESC) = 1

ORDER BY 1,2;

*** Query completed. 2 rows found. 4 columns returned.

*** Total elapsed time was 1 second.

customer start_date end_date transaction_n

-------- ---------- ---------- -------------

A 2008-12-03 2009-10-05 1

B 2011-01-13 2011-06-13 3

Cheers.

Carlos.