Requirements Description:
Need to check out a customer one year those days is a continuous business
implement SQL as follows:
To create a table:
Copy Code code as follows:
CREATE TABLE Test_num
(Tyear number,
Tdate date);
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 Code code as follows:
SELECT tyear, MIN (tdate) as StartDate, MAX (tdate), COUNT (tyear) as Endnum
From (SELECT a.*, a.tdate-rownum as Gnum
From (SELECT * to Test_num ORDER by Tyear, tdate)
GROUP by Tyear, Gnum
Order by Tyear, MIN (tdate)