CODEWITHSUNDEEP
sqlsql-serversql-server-2008
karan oshan
karan oshan

Reputation: 25

Sql data transformation

This is my source table

Reference   ModifiedDate             Teachers   Students SchoolID    ETC
-------------------------------------------------------------------------
1023175     2017-03-03 16:02:01.723     10       25        5
1023175     2017-03-07 07:59:49.283     15       50        15
1023175     2017-03-12 11:14:40.230     25       6         5
1023176     2017-03-04 16:02:01.723     11       35        8
1023176     2017-03-08 07:59:49.283     16       60        25
1023177     2017-03-15 11:14:40.230     15       7         2

I need the following output

Reference   StartDate                 EndDate  
---------------------------------------------
1023175   2017-03-03 16:02:01.723     2017-03-07 07:59:49.283
1023175   2017-03-07 07:59:49.283     2017-03-12 11:14:40.230
1023175   2017-03-12 11:14:40.230     9999-12-31 00:00:00.000 
1023176   2017-03-04 16:02:01.723     2017-03-08 07:59:49.283
1023176   2017-03-08 07:59:49.283     9999-12-31 00:00:00.000 
1023177   2017-03-15 11:14:40.230     9999-12-31 00:00:00.000 (last record should have this value)

Teachers Students SchoolID

 10       25        5
 15       50        15
 25       6         5
 11       35        8
 16       60        25
 15       7         2

All other columns like Teachers,Students and SchoolId etc also have to be in the output along with each record.

Any suggestions on how this can be achieved? Using Sql Server 2008

Upvotes: 1

Views: 41

Answers (1)

SqlZim
SqlZim

Reputation: 38023

using outer apply():

select 
    Reference
  , StartDate = t.ModifiedDate
  , EndDate = coalesce(x.ModifiedDate, convert(datetime,'9999-12-31 00:00:00.000'))
  , Teachers
  , Students
  , SchoolID
from t
  outer apply (
    select top 1 i.ModifiedDate
    from t as i
    where i.Reference = t.Reference
      and i.ModifiedDate > t.ModifiedDate
    order by i.ModifiedDate asc
    ) x

rextester demo: http://rextester.com/RFTD32624

returns:

+-----------+-------------------------+-------------------------+----------+----------+----------+
| Reference |        StartDate        |         EndDate         | Teachers | Students | SchoolID |
+-----------+-------------------------+-------------------------+----------+----------+----------+
|   1023175 | 2017-03-03 16:02:01.723 | 2017-03-07 07:59:49.283 |       10 |       25 |        5 |
|   1023175 | 2017-03-07 07:59:49.283 | 2017-03-12 11:14:40.230 |       15 |       50 |       15 |
|   1023175 | 2017-03-12 11:14:40.230 | 9999-12-31 00:00:00.000 |       25 |        6 |        5 |
|   1023176 | 2017-03-04 16:02:01.723 | 2017-03-08 07:59:49.283 |       11 |       35 |        8 |
|   1023176 | 2017-03-08 07:59:49.283 | 9999-12-31 00:00:00.000 |       16 |       60 |       25 |
|   1023177 | 2017-03-15 11:14:40.230 | 9999-12-31 00:00:00.000 |       15 |        7 |        2 |
+-----------+-------------------------+-------------------------+----------+----------+----------+

Reference:

Upvotes: 2

Related Questions