Groups > comp.databases.ms-sqlserver > #198

Help needed to write a qury

From	Dinesh <dinesht15@gmail.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Help needed to write a qury
Date	2011-04-24 10:26 -0700
Organization	http://groups.google.com
Message-ID	<95071489-1d30-4d5b-b075-aa465d7b654c@x37g2000prb.googlegroups.com> (permalink)

Show all headers | View raw

Hi Experts,

I need help to write a query in SQL server 2008. Below is my table
structure.

My table is having four columns
EmpID,EMPName,DepartmentID,AllocationDate. Below is some sample data
for the table.
AllocationDate is the date on which employee is transfered in that
department.


EmpID  EMPName     DepartmentID        AllocationDate

1	Ajay		1		01/01/2005
1	Ajay		2		04/10/2005
1	Ajay		3		09/22/2005
1	Ajay		1		07/25/2008
1	Ajay		5		03/15/2010

2	John		2		01/10/2008
2	John		1		03/15/2009
2	John		4		02/23/2010
2	John		3		04/20/2011

3	Kate		3		01/11/2007
3	Kate		4		03/16/2008
3	Kate		2		02/24/2009
3	Kate		1		04/21/2010

Now I want to find out all the transfers from one department to
another for all the employess with their old and new department
along with their allocation and trasfer date.
I want my result like this.

EMPID     EMPName            OldDeptID   NewDeptID
AllocationDate   TransferDate
1	   Ajay		1	   2	       01/01/2005       04/10/2005
1	   Ajay		2	   3	       04/10/2005       09/22/2005
1	   Ajay		3	   1	       09/22/2005       07/25/2008
1	   Ajay		1	   5	       07/25/2008       03/15/2010

2	   John		2	   1	       01/10/2008       03/15/2009
2	   John		1	   4	       03/15/2009       02/23/2010
2	   John		4	   3	       02/23/2010       04/20/2011

3	   Kate		3	   4	       01/11/2007       03/16/2008
3	   Kate		4	   2	       03/16/2008       02/24/2009
3	   Kate		2	   1	       02/24/2009       04/21/2010



Any help will be appriciated.

Regards
Dinesh

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Help needed to write a qury Dinesh <dinesht15@gmail.com> - 2011-04-24 10:26 -0700
  Re: Help needed to write a qury "Fred." <ghrno-google@yahoo.com> - 2011-04-24 12:27 -0700
  Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-04-24 21:51 +0200
    Re: Help needed to write a qury Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-25 06:50 +0200
      Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-04-25 10:14 +0200
        Re: Help needed to write a qury Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-25 11:52 +0200
          Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-04-25 15:30 +0200
            Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-04-25 15:31 +0200
              Re: Help needed to write a qury Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-25 16:29 +0200
              Re: Help needed to write a qury "Fred." <ghrno-google@yahoo.com> - 2011-04-25 11:41 -0700
                Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-04-25 23:18 +0200
                Re: Help needed to write a qury Dinesh <dinesht15@gmail.com> - 2011-04-26 01:58 -0700
    Re: Help needed to write a qury "Fred." <ghrno-google@yahoo.com> - 2011-04-25 06:28 -0700
    Re: Help needed to write a qury Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2011-04-25 17:44 +0200
      Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-05-25 00:01 +0200
        Re: Help needed to write a qury Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2011-05-25 21:15 +0200
  Re: Help needed to write a qury --CELKO-- <jcelko212@earthlink.net> - 2011-05-26 06:19 -0700

csiph-web