SELECT query that Selects from Next Row given certain criteria using SQL Server 2008 R2? -

-

i want create table shows employees' hire , accompanying term date on same line. i'm using data in 2 tables: employees , eechange.

eechange has 3 columns important: employee_no, action_date, action_type. action_type 'h', 'r' or 't' 'hire', 'rehire' or 'term'. far i'm concerned 'hire' , 'rehire' dates effective dates , 'term' dates termination dates.

i can create select query places effective dates (ed) in ed column , term dates (td) in td column. problem not on same row. difficulty i'm having there may not associated td every ed. see below: employee (ee) 14 hired on 5/1/98, rehired on 9/13/06 , termed on 3/23/98. not know why database has rehire followed hire. far concerned ee 14 hired on 5/1/98 , termed on 3/23/98. going assume there no break in employment.

here tables:

employees:

ee no   name 6       anil 12      viktor 14      sherry 15      juan 48      susan 50      kevin

eechange:

ee no   action_type action_date 6       h           5/1/1998 6       t           7/26/2010 12      h           5/1/1998 12      r           4/16/2012 14      h           5/1/1998 14      r           9/13/2006 14      t           9/19/2008 15      h           3/23/1998 48      h           7/1/1998 48      r           10/21/2008 48      t           1/3/2009 50      h           7/2/1998 50      r           7/16/2010 50      t           5/1/2012 50      r           12/1/2013 50      t           2/15/2015

my query:

select      ltrim(employees.employee_no) [ee no],     row_number() on (partition employees.employee_no order eechange.action_date) [seq],     eechange.action_type,     case         when eechange.action_type = 'h' or eechange.action_type = 'r'           eechange.action_date     end [effective date],     case         when eechange.action_type = 't'           eechange.action_date     end [term date]      dbo.employees employees left outer join      dbo.emp_employment_action_changes eechange on employees.employee_no = eechange.employee_no

i added sequence column see if order action_date column per [ee no]. able so, unsuccessful , finding select query give me desired result: ed , td on same line.

my result:

ee no   seq action_type effective date  term date 6       1   h           5/1/1998     6       2   t                           7/26/2010 12      1   h           5/1/1998     12      2   r           4/16/2012    14      1   h           5/1/1998     14      2   r           9/13/2006    14      3   t                           9/19/2008 15      1   h           3/23/1998    48      1   h           7/1/1998 48      2   r           10/21/2008   48      3   t                           1/3/2009 50      1   h           7/2/1998 50      2   r           7/16/2010 50      3   t                           5/1/2012 50      4   r           12/1/2013 50      5   t                           2/15/2015

desired result:

ee no   name    effective date  term date 6       anil    5/1/1998        7/26/2010 12      viktor  5/1/1998     14      sherry  5/1/1998        9/19/2008 15      juan    3/23/1998    48      susan   7/1/1998        1/3/2009 50      kevin   7/2/1998        5/1/2012 50      kevin   12/1/2013       2/15/2015

notice how kevin has 5 lines in eechange table. want first ed accompanying term date , next ed , accompanying term date. active ees there no final term date.

any can give great. tried using lead() doesn't work sql server 2008 r2. couldn't find way make lead() skip on eds came in sequence before tds.

so, looks problem gaps-and-island problem, meaning need find start , end of discrete ranges. if treat both actions h , r markers start of range problem find earliest end date succeeding every start date later date. (sort of - find hard describe). logic should pretty easy follow though.

i tested sample data , query seems give desired result. 

with  (     select         e.eeno, e.name,         case            when action_type in ('r','h')            action_date         end effective_date,         case            when action_type = 't'            action_date         end term_date     employees e      join eechange ee on e.eeno = ee.eeno ),  b (     select         eeno, name, effective_date,         case            when effective_date not null            (              select min(term_date)               a2                a2.term_date > a.effective_date , a2.eeno = a.eeno              )         end term_date     ) select      eeno, name, min(effective_date) effective_date, term_date  b  effective_date not null group eeno, name, term_date

sample sql fiddle

sample result:

| eeno |   name | effective_date |  term_date | |------|--------|----------------|------------| |    6 |   anil |     1998-05-01 | 2010-07-26 | |   12 | viktor |     1998-05-01 |     (null) | |   14 | sherry |     1998-05-01 | 2008-09-19 | |   15 |   juan |     1998-03-23 |     (null) | |   48 |  susan |     1998-07-01 | 2009-01-03 | |   50 |  kevin |     1998-07-02 | 2012-05-01 | |   50 |  kevin |     2013-12-01 | 2015-02-15 |

Comments

Post a Comment

Popular posts from this blog

qt - Using float or double for own QML classes -

-
i've created components , helper methods using c++ , qt used in qml gui. of methods calculate gui elements (size, transformations, scene graph items creation). target arm 32 bit using opengl / eglfs render output. opengl using float , qml using real (defined double ). what should use in c++ do not lose precision? if use qreal ( double ) shouldn't lose precision. if git grep float in qtbase.git , can find out bit more: dist/changes-5.2.0-**************************************************************************** dist/changes-5.2.0-* important behavior changes * dist/changes-5.2.0-**************************************************************************** dist/changes-5.2.0- dist/changes-5.2.0- - qt compiled qreal typedef'ed double on dist/changes-5.2.0: platforms. qreal float on arm chipsets before. guarantees more dist/changes-5.2.0- consistent behavior between platforms qt supports, binary dist/chan...
Read more

Create Outlook appointment via C# .Net -

-
i need create outlook appointment using microsoft.office.interop.outlook , while can work locally on own workstation , works if run web application via server's browser, not work when connect server externally. hence, think might permission issue. i changed application pool identity " localsystem " don't access denied error. unfortunately, doesn't work. the app behaves if appointment created, appointment doesn't appear in outlook. here include file on top of aspx.cs page using outlook = microsoft.office.interop.outlook; here code using pop appointment. outlook.application apptapp = new outlook.application(); outlook.appointmentitem appt = apptapp.createitem(outlook.olitemtype.olappointmentitem) outlook.appointmentitem; appt.subject = txtfirstname.text + " " + txtlastname.text; appt.body = txtcomment.text; appt.alldayevent = false; appt.start = datetime.parse(txtreminderdate.text + " 8:00 am"); appt.end = datetime.p...
Read more

ios - Swift Array Resetting Itself -

-
i have 2 arrays: pointhistory , datehistory . have nstimer , , when timer runs out, action called. in action appending int pointhistory , , current date string datehistory . here timer action: func timeraction() { let steppervalue = int(stepper.value) employee.numberofpoints = steppervalue networking.saveemployee(employee, completion: { (error) -> void in if error != nil { let alert = uialertcontroller(title: "error", message: "there error updating \(self.employee.name)'s points", preferredstyle: .alert) let alertaction = uialertaction(title: "ok", style: .cancel, handler: nil) alert.addaction(alertaction) self.presentviewcontroller(alert, animated: true, completion: nil) } }) var dateformatter = nsdateformatter() dateformatter.dateformat = "mm-dd-yyyy" dateformatter.timezone = nstimezone.localtimezone() var date = nsdate() var ...
Read more