CODEWITHSUNDEEP
sqlsql-server
JamesMarc
JamesMarc

Reputation: 169

SQL Query to generate an extra field from data in the table

I have a table with 3 fields like this sample table Tbl1

Person Cost FromDate
1       10  2009-1-1
1       20  2010-1-1
2       10  2009-1-1

I want to query it and get back the 3 fields and a generated field called ToDate that defaults to 2099-1-1 unless there is an actual ToDate implied from another entry for the person in the table.

select Person,Cost,FromDate,ToDate From Tbl1

Person Cost FromDate ToDate
1       10  2009-1-1 2010-1-1
1       20  2010-1-1 2099-1-1
2       10  2009-1-1 2099-1-1

Upvotes: 0

Views: 90

Answers (2)

deroby
deroby

Reputation: 6002

Although Thorsten's answer is perfectly fine, it would be more efficient to use window-functions to match the derived end-dates.

;WITH nbrdTbl 
   AS ( SELECT Person, Cost, FromDate, row_nr = ROW_NUMBER() OVER (PARTITION BY Person ORDER BY FromDate ASC)
         FROM Tbl1)

SELECT t.Person, t.Cost, t.FromDate, derived_end_date = COALESCE(nxt.FromDate, '9991231')
  FROM nbrdTbl t
  LEFT OUTER JOIN nbrdTbl nxt
               ON nxt.Person = t.Person
              AND nxt.row_nr = t.row_nr + 1
ORDER BY t.Person, t.FromDate

Doing a test on a 2000-records table it's about 3 times as efficient according to the Execution plan (78% vs 22%).

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You can select the minimum date from all dates that are after the record's date. If there is none you get NULL. With COALESCE you change NULL into the default date:

select 
  Person, 
  Cost, 
  FromDate, 
  coalesce((select min(FromDate) from Tbl1 later where later.FromDate > Tbl1.FromDate), '2099-01-01') as ToDate 
From Tbl1
order by Person, FromDate;

Upvotes: 1

Related Questions