Groups | Search | Server Info | Keyboard shortcuts | Login | Register

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

Re: Help needed to write a qury

Show all headers | View raw

On Apr 24, 1:26 pm, Dinesh <dinesh...@gmail.com> wrote:
> 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

For you to actually idenify transfers, (EMPID, ALLOCATIONDATE) needs
to be a unique constraint in your base table which I will call
[assignment].  This implies the business rule of a maximum of one
transfer per day.  The one time I tackled this sort of thing I added a
field SEQUENCE (int) to the base table, and updated it following table
updates as follows:

UPDATE u SET SEQUENCE=CNT FROM assignment as u INNER JOIN
          (SELECT a.EMPID, a.ALLOCATIONDATE, COUNT(*) AS CNT
                        FROM assignment a INNER JOIN assignment b ON
a.EMPID=b.EMPID AND a.ALLOCATIONDATE >= b.ALLOCATIONDATE
                        GROUP BY a.EMPID, a.ALLOCATIONDATE) s
          ON u.EMPID=s.EMPID AND u.ALLOCATIONDATE=s.ALLOCATIONDATE

where in the derived table, s, CNT is the number of earlier
assignments for the EMPID and ALLLOCATION DATE plus 1.   Then, once
SEQENCE is made current, you can identify the transfers by autojoining
consecutive sequence numbers for each employee:

SELECT f.*, t.* FROM assignment f INNER JOIN assignment t ON
f.EMPID=t.EMPID AND f.SEQUENCE+1 = t.SEQUENCE

where f.* is short for the from department fields appropriately
renamed, and t.* is short for the to department fields appropriately
renamed.  You can't put a unique constraint on (EMPID, SEQUENCE)
because it it is not consistently maitined as such.

Note that you have an issue because your database structure allows a
new alias for an employee with each assignment.  I am failry sure it
is not your intent to allow this..

This approach is not particularly elegant, but it will work, and can
be reasonably quick given appropriate indexing.  It could be faster to
create a temporary table with indices in place of the derived table if
the number of assignements is large.

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