Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Posts
    80

    Unanswered: Convert SCD type 2 group in single record

    Hi,

    I have a table which manages the data in SCD type 2 fashion with EFFECTIVE and END DATE to define active and closed records. I am trying to write a sql where in I am not sure how many records are there for a particular group but I want all the effective dates and end dates in one single record for a particular primary key. Could you please help?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I assume you want your result set to look like this:

    PK, ef_date1, end_date1, ef_date2, end_date2, ... ef_daten,end_daten

    where n is the number of rows that exists for the particular PK.

    If this is so, you cannot do this. A result set is basically a table, it has to be rectangular. Each row has to have the same number of columns.

    The best you can do, is pick an arbitrary value for n, that is current greater or equal to the maximum nunber of rows for all PKs. But once this quantity is exceeded in the data, you code will be broken.

    Andy

  3. #3
    Join Date
    Jan 2005
    Posts
    80
    Yes, I was looking for the same thing as you have defined. Thank you very much for the information!!!

  4. #4
    Join Date
    Jan 2005
    Posts
    80
    Alternatively, Can we fetch the values like this:


    PK EFFECTIVE DATE END DATE NEXT EFFECTIVE DATE NXTENDDT
    X 2009-01-01 2009-01-30 2009-01-31 2009-02-28
    X 2009-01-31 2009-02-28 2009-03-01 2009-03-31
    X 2009-03-01 2009-03-31 2009-04-01 9999-12-31

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes, but why would you want to do that? Why not just "select pk,effective_date,end_date from mytable order by pk,effective_date,end_date"?

    Andy

  6. #6
    Join Date
    Jan 2005
    Posts
    80
    By Doing that I can validate the records are in proper format. like I can check if the effective date and next effective date is greater or not.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1)Your using "primary key" is not a good word.
    Because, "primary key" is a word used in RDB theory and SQL language for one of the unique set(s) of not null column(s) in a table.

    2)You can query anomaly without making all the effective dates and end dates in one single record.
    For example,
    query to find pair of rows which are next effective date is not greater than end date would be:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /* sample data */
     table_name(id, effective_date, end_date) AS (
    VALUES
     (1, '2009-01-01', '2009-01-05')
    ,(1, '2009-01-06', '2009-01-08')
    ,(1, '2009-01-10', '2009-01-12')
    ,(1, '2009-01-12', '2009-01-15')
    ,(1, '2009-01-20', '2009-01-25')
    ,(1, '2009-01-22', '2009-01-23')
    ,(1, '2009-01-24', '2009-01-27')
    ,(2, '2009-01-01', '2009-01-06')
    ,(2, '2009-01-07', '2009-01-10')
    ,(2, '2009-01-21', '2009-01-30')
    )
    /* end of sample data */
    SELECT b.id
         , b.effective_date
         , b.end_date
         , n.effective_date AS next_effective_date
         , n.end_date       AS next_end_date
         , 'Anomaly'        AS "Comment"
      FROM table_name b
      JOIN table_name n
       ON  n.id             =  b.id
       AND n.effective_date >= b.effective_date
       AND n.end_date       <> b.end_date
       AND n.effective_date <= b.end_date
     ORDER BY
           b.id
         , b.effective_date
         , b.end_date
         , n.effective_date
    ;
    ------------------------------------------------------------------------------
    
    ID          EFFECTIVE_DATE END_DATE   NEXT_EFFECTIVE_DATE NEXT_END_DATE Comment 
    ----------- -------------- ---------- ------------------- ------------- -------
              1 2009-01-10     2009-01-12 2009-01-12          2009-01-15    Anomaly
              1 2009-01-20     2009-01-25 2009-01-22          2009-01-23    Anomaly
              1 2009-01-20     2009-01-25 2009-01-24          2009-01-27    Anomaly
    
      3 record(s) selected.
    Last edited by tonkuma; 06-24-09 at 01:40.

  8. #8
    Join Date
    Jan 2005
    Posts
    80
    Thanks a lot for enlightening me with all the knowledge.

Posting Permissions

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