Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2006
    Posts
    33

    Question Unanswered: conversion query

    Hi there,

    I have a source table with following columns:

    employer businessnumber added_date
    1 sn01 2001/09/08
    1 bn02 2004/07/02
    2 tn01 2001/03/03
    ...

    after conversion, I want to have something like:
    employer businessnumber startdate enddate
    1 sn01 2001/09/08 2004/07/01
    1 bn02 2004/07/02 null
    2 tn01 2001/03/03 null

    so you see if source record is the last one for the employer, we'll use NULL as the enddate, if the source record is NOT the last one, we'll use the Added_date(from the next record) - 1 day.

    I wonder if I can use some queries to archive this?

    Thanks a lot.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think something like this will get you what you want:


    with t1 (employer, business_number, added_date, ordinal) as
    (select employer,business+number,added_date,row_number() over(partition by employer order by added_date)
    select t1.employer,t1.business_number,t1.added_date as start_date,
    (select t2.added_date from t1 as t2 where t1.employer = t2.employer and t2.ordinal = (t1.ordinal + 1)) as enddate
    from t1
    order by 1,3

    Andy

Posting Permissions

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