Requirements:
You need to find that a customer has continuously handled the business for those days of a year.
The SQL implementation is as follows::
Create a table:
Copy codeThe Code is as follows:
Create table test_num
(Tyear number,
Tdate );
Test Data:
Insert into test_num
Select 2014, trunc (sysdate)-1 from dual union all
Select 2014, trunc (sysdate)-002 from dual union all
Select 2014, trunc (sysdate)-003 from dual union all
Select 2014, trunc (sysdate)-004 from dual union all
Select 2014, trunc (sysdate)-005 from dual union all
Select 2014, trunc (sysdate)-007 from dual union all
Select 2014, trunc (sysdate)-008 from dual union all
Select 2014, trunc (sysdate)-009 from dual union all
Select 2013, trunc (sysdate)-120 from dual union all
Select 2013, trunc (sysdate)-121 from dual union all
Select 2013, trunc (sysdate)-122 from dual union all
Select 2013, trunc (sysdate)-124 from dual union all
Select 2013, trunc (sysdate)-125 from dual union all
Select 2013, trunc (sysdate)-127 from dual union all
Select 2015, trunc (sysdate)-099 from dual union all
Select 2015, trunc (sysdate)-100 from dual union all
Select 2015, trunc (sysdate)-101 from dual union all
Select 2015, trunc (sysdate)-102 from dual union all
Select 2015, trunc (sysdate)-104 from dual union all
Select 2015, trunc (sysdate)-105 from dual;
Write SQL:
Copy codeThe Code is as follows:
Select tyear, MIN (TDATE) as startdate, MAX (TDATE), COUNT (TYEAR) AS ENDNUM
FROM (select a. *, A. TDATE-ROWNUM AS GNUM
FROM (SELECT * FROM TEST_NUM order by tyear, TDATE))
Group by tyear, GNUM
Order by tyear, MIN (TDATE)