Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: Help needed to write a qury

Show all headers | View raw

On Apr 24, 3:51 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Dinesh (dinesh...@gmail.com) writes:
> > 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
>
> WITH numbered AS (
>    SELECT EmpID, EmpName, DepartmentID, AllocationDate,
>           rowno = row_number() OVER(PARTITION BY EmpID
>                                     ORDER BY AllocationDate)
> )
> SELECT a.EmpID, a.EmpName, a.DepartmentID AS OldDeptID,
>        b.DepartmentID AS NewDeptID, a.AllocationDate,
>        b.AllocationDate AS TransferDate
> FROM   numbered a
> JOIN   numbered b ON b.rowno = a.rowno +1
> ORDER  BY a.EmpId, a.rowno
>
> Fred who posted the other solution should take notice. The row_number
> function, added in SQL 2005, is much more efficient in numbering the rows
> than using a subquery.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx- Hide quoted text -
>
> - Show quoted text -

Absolutely.  My solution was before 2005, and I hadn't had to revisit
it because it was a small table.

Fred.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | 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