Convert SCD type 2 group in single record

ahmedwaseem2000 · #1 (**permalink**) 06-23-09, 11:34

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?

ARWinner · #2 (**permalink**) 06-23-09, 11:49

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

ahmedwaseem2000 · #3 (**permalink**) 06-23-09, 11:59

Yes, I was looking for the same thing as you have defined. Thank you very much for the information!!!

ahmedwaseem2000 · #4 (**permalink**) 06-23-09, 12:58

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

ARWinner · #5 (**permalink**) 06-23-09, 13:05

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

ahmedwaseem2000 · #6 (**permalink**) 06-23-09, 13:19

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.

tonkuma · #7 (**permalink**) 06-23-09, 15:21

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.

ahmedwaseem2000 · #8 (**permalink**) 06-24-09, 00:10

Thanks a lot for enlightening me with all the knowledge.

Internet Services

Web Development

Digital Marketing

Consumer Tech