Help Sql: Please verify solution and let me know if any better to achieve this ....

[abhijeetgupta29nov]

Input:
Ordernum Name Zip Start_Date End_Date
1 John 90210 3/15/2011 3/16/2011
1 John 90210 3/16/2011 3/18/2011
1 John 42538 3/18/2011 4/3/2011
1 John 90210 4/3/2011 4/9/2011
2 Jerry 23245 4/12/2011 4/15/2011
2 Paul 23245 4/15/2011 4/22/2011
2 Paul 23245 4/22/2011 4/29/2011

Output:
Ordernum Name Zip Start_Date End_Date
1 John 90210 3/15/2011 3/18/2011
1 John 42538 3/18/2011 4/3/2011
1 John 90210 4/3/2011 4/9/2011
2 Jerry 23245 4/12/2011 4/15/2011
2 Paul 23245 4/15/2011 4/29/2011


--------Solution -------

DROP TABLE QQQ_HELP_QUERY;

CREATE VOLATILE MULTISET TABLE QQQ_HELP_QUERY
(
Ordernum INT,
Name VARCHAR(20),
Zip INT,
Start_Date VARCHAR(10),
End_Date VARCHAR(10)
)
ON COMMIT PRESERVE ROWS;

INSERT INTO QQQ_HELP_QUERY ('1','John','90210','03/15/2011','03/16/2011');
INSERT INTO QQQ_HELP_QUERY ('1','John','90210','03/16/2011','03/18/2011');
INSERT INTO QQQ_HELP_QUERY ('1','John','42538','03/18/2011','04/03/2011');
INSERT INTO QQQ_HELP_QUERY ('1','John','90210','04/03/2011','04/09/2011');
INSERT INTO QQQ_HELP_QUERY ('2','Jerry','23245','04/12/2011','04/15/2011');
INSERT INTO QQQ_HELP_QUERY ('2','Paul','23245','04/15/2011','04/22/2011');
INSERT INTO QQQ_HELP_QUERY ('2','Paul','23245','04/22/2011','04/29/2011');

DROP TABLE QQQ_HELP_QUERY_WRK;

CREATE VOLATILE TABLE QQQ_HELP_QUERY_WRK AS (
SELECT Ordernum,NAME, ZIP, CAST(START_DATE AS DATE FORMAT 'MM/DD/YYYY') START_DATE,
--CAST(END_DATE AS DATE FORMAT 'MM/DD/YYYY'),
--CAST(NEXT_START_DATE AS DATE FORMAT 'MM/DD/YYYY'),
CAST(NEXT_END_DATE AS DATE FORMAT 'MM/DD/YYYY') as END_DATE
FROM (
SELECT Ordernum,NAME, ZIP, START_DATE,END_DATE,
COALESCE (MIN(START_DATE) OVER (PARTITION BY NAME,ZIP ORDER BY START_DATE ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING),'12/31/9999') as NEXT_START_DATE
, COALESCE(MIN(END_DATE) OVER (PARTITION BY NAME,ZIP ORDER BY START_DATE ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING),'12/31/9999') AS NEXT_END_DATE
from QQQ_HELP_QUERY
) REF where
CAST(NEXT_START_DATE AS DATE FORMAT 'MM/DD/YYYY') -CAST(END_DATE AS DATE FORMAT 'MM/DD/YYYY') = 0
) WITH DATA ON COMMIT PRESERVE ROWS


SELECT * FROM (
SELECT Ordernum,NAME, ZIP, CAST(START_DATE AS DATE FORMAT 'MM/DD/YYYY') START_DATE,
CAST(END_DATE AS DATE FORMAT 'MM/DD/YYYY') as END_DATE
FROM QQQ_HELP_QUERY_WRK
union
SELECT A.Ordernum,A.NAME, A.ZIP, CAST(A.START_DATE AS DATE FORMAT 'MM/DD/YYYY') START_DATE,
CAST(A.END_DATE AS DATE FORMAT 'MM/DD/YYYY') as END_DATE
FROM QQQ_HELP_QUERY A LEFT JOIN QQQ_HELP_QUERY_WRK B ON
(A.Ordernum = B.Ordernum AND A.NAME = B.NAME)
WHERE CAST (A.END_DATE AS DATE FORMAT 'MM/DD/YYYY') NOT BETWEEN B.START_DATE AND B.END_DATE
UNION
SELECT A.Ordernum,A.NAME, A.ZIP, CAST(A.START_DATE AS DATE FORMAT 'MM/DD/YYYY') START_DATE,
CAST(A.END_DATE AS DATE FORMAT 'MM/DD/YYYY') as END_DATE
FROM QQQ_HELP_QUERY A WHERE A.NAME NOT IN (SELECT DISTINCT NAME FROM QQQ_HELP_QUERY_WRK)
) REF ORDER BY NAME,START_DATE