INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close