All Forums Database
sunny.j 90 posts Joined 10/10
06 Feb 2013
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 .
 

sunny.j 90 posts Joined 10/10
06 Feb 2013

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

Qaisar Kiani 337 posts Joined 11/05
06 Feb 2013

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
Qaisar Kiani 337 posts Joined 11/05
06 Feb 2013

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

 

sunny.j 90 posts Joined 10/10
07 Feb 2013

thanks for the reply, yes its a typo error .
in case:2 the start date is 13/01/2011.

sunny.j 90 posts Joined 10/10
07 Feb 2013

your second query is not working for case:2 scenario.
any one would please help me

CarlosAL 512 posts Joined 04/08
07 Feb 2013

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.

You must sign in to leave a comment.