Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    1

    Unanswered: PL/SQL or SQL Help needed

    Hello SQL experts:
    Can you help? I am new to PL/SQL. I have this table:
    Customer# Start-Date End-Date
    C1 Jan 01 2011 Jan 31 2011
    c1 Feb 01 2011 Feb 28 2011
    c1 May01 2011 May 31 2011
    ---

    I need to do the following:
    if the activity dates are continuous, to merge them into one record. So, the output will be:
    c1 Jan 01 2011 Feb 28 2011
    c1 May01 2011 May 31 2011

    If you have any ideas, appreciate your input.

    SQL Newbie

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    this is a common homework problem & numerous previous solutions exist here & on other forums
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2012
    Posts
    13
    Hi,
    I hope below one works fine.

    WITH sample_data AS
    (
    SELECT 'p1' programme, '01-jan-2011' START_DATE, '31-jan-2011' end_date FROM dual UNION ALL
    SELECT 'p1' programme, '01-feb-2011' START_DATE, '28-feb-2011' end_date FROM dual UNION ALL
    SELECT 'p1' programme, '01-may-2011' START_DATE, '31-may-2011' end_date FROM dual UNION ALL
    SELECT 'p2' programme, '01-apr-2011' START_DATE, '30-apr-2011' end_date FROM dual UNION ALL
    SELECT 'p2' programme, '01-jun-2011' START_DATE, '30-jun-2011' end_date FROM dual UNION ALL
    SELECT 'p2' programme, '01-mar-2011' START_DATE, '31-mar-2011' end_date FROM dual UNION ALL
    SELECT 'p2' programme, '01-jul-2011' START_DATE, '31-jul-2011' end_date FROM dual UNION ALL
    SELECT 'p3' programme, '01-aug-2011' START_DATE, '31-aug-2011' end_date FROM dual
    )

    SELECT a.programme, a.START_DATE, b.end_date
    FROM sample_data a, sample_data b
    WHERE a.programme = b.programme
    AND b.START_DATE = (to_date(a.end_date,'dd-mon-yyyy') + 1);

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •