Help needed to write a qury

#1

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

#2

On Apr 24, 1:26*pm, Dinesh <dinesh... (AT) gmail (DOT) com> wrote:

Quote:

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.

#3

Dinesh (dinesht15 (AT) gmail (DOT) com) writes:

Quote:

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, esquel (AT) sommarskog (DOT) 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

#4

On 24-04-2011 21:51, Erland Sommarskog wrote:

Quote:

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

If I copy/paste and run this code, it complains about invalid columnnames.
I do notice that there's no reference to a table in the AS (SELECT
EmpId...) part, but when I put in the name of the table that I created,
I get those invalid columnname errors as well.
I'm sure I'm forgetting something very basic, but I can't get it to run.
What am I overlooking?

Best,
Henk

#5

Henk van den Berg (hvandenberg (AT) xs4all (DOT) nl) writes:

Quote:

On 24-04-2011 21:51, Erland Sommarskog wrote:
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

If I copy/paste and run this code, it complains about invalid columnnames.
I do notice that there's no reference to a table in the AS (SELECT
EmpId...) part, but when I put in the name of the table that I created,
I get those invalid columnname errors as well.
I'm sure I'm forgetting something very basic, but I can't get it to run.
What am I overlooking?

I forgot the FROM clause in the CTE:

WITH numbered AS (
SELECT EmpID, EmpName, DepartmentID, AllocationDate,
rowno = row_number() OVER(PARTITION BY EmpID
ORDER BY AllocationDate)
FROM tbl
)
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

Had Dinesh posted CREATE TABLE statement for his table and INSERT statements
with sample data, I would have been able to test my query. Now I wasn't.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

#6

On 25-04-2011 10:14, Erland Sommarskog wrote:

Quote:

I forgot the FROM clause in the CTE:

Thanks.

Quote:

Had Dinesh posted CREATE TABLE statement for his table and INSERT statements
with sample data, I would have been able to test my query. Now I wasn't.
Here's the create/insert stuff.

Since I'm trying to get my head around CTE and partition by/over order
by, I'd appreciate a pointer if you can find the time for it.

Quote:

create table EmployeeDept (EmpID int, EMPName varchar(10),
DepartmentID int, AllocationDate date)
insert into EmployeeDept values
(1, 'Ajay', 1, '2005/01/01'),
(1, 'Ajay', 2, '2005/04/10'),
(1, 'Ajay', 3, '2005/09/22'),
(1, 'Ajay', 1, '2008/07/25'),
(1, 'Ajay', 5, '2010/03/18'),

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

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

Your query results in

EmpID EmpName OldDeptID NewDeptID AllocationDate TransferDate
----------- ---------- ----------- ----------- -------------- ------------
1 Ajay 1 2 2005-01-01 2005-04-10
1 Ajay 1 1 2005-01-01 2009-03-15
1 Ajay 1 4 2005-01-01 2008-03-16
1 Ajay 2 3 2005-04-10 2005-09-22
1 Ajay 2 4 2005-04-10 2010-02-23
1 Ajay 2 2 2005-04-10 2009-02-24
1 Ajay 3 1 2005-09-22 2008-07-25
1 Ajay 3 3 2005-09-22 2011-04-20
1 Ajay 3 1 2005-09-22 2010-04-21
1 Ajay 1 5 2008-07-25 2010-03-18
2 John 2 2 2008-01-10 2005-04-10
2 John 2 1 2008-01-10 2009-03-15
2 John 2 4 2008-01-10 2008-03-16
2 John 1 3 2009-03-15 2005-09-22
2 John 1 4 2009-03-15 2010-02-23
2 John 1 2 2009-03-15 2009-02-24
2 John 4 1 2010-02-23 2008-07-25
2 John 4 3 2010-02-23 2011-04-20
2 John 4 1 2010-02-23 2010-04-21
2 John 3 5 2011-04-20 2010-03-18
3 Kate 3 2 2007-01-11 2005-04-10
3 Kate 3 1 2007-01-11 2009-03-15
3 Kate 3 4 2007-01-11 2008-03-16
3 Kate 4 3 2008-03-16 2005-09-22
3 Kate 4 4 2008-03-16 2010-02-23
3 Kate 4 2 2008-03-16 2009-02-24
3 Kate 2 1 2009-02-24 2008-07-25
3 Kate 2 3 2009-02-24 2011-04-20
3 Kate 2 1 2009-02-24 2010-04-21
3 Kate 1 5 2010-04-21 2010-03-18

#7

On Apr 24, 3:51*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Dinesh (dinesh... (AT) gmail (DOT) 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... (AT) sommarskog (DOT) 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/sqls...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.

#8

Henk van den Berg (hvandenberg (AT) xs4all (DOT) nl) writes:

Quote:

Here's the create/insert stuff.

Since I'm trying to get my head around CTE and partition by/over order
by, I'd appreciate a pointer if you can find the time for it.

Thanks for the sample data!

I see that I had forgot the join condition on EmpId - without the query is
kind of meaningless.

The principle is simple: number the rows, and then make a self-join on
the row number with the next number.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

#9

Erland Sommarskog (esquel (AT) sommarskog (DOT) se) writes:

Quote:

Thanks for the sample data!

I see that I had forgot the join condition on EmpId - without the query is
kind of meaningless.

The principle is simple: number the rows, and then make a self-join on
the row number with the next number.

Pressed Send to quickly! Here is the query:

WITH numbered AS (
SELECT EmpId, EmpName, DepartmentID, AllocationDate,
rowno = row_number() OVER(PARTITION BY EmpId
ORDER BY AllocationDate)
FROM EmployeeDept
)
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
AND b.EmpId = a.EmpId
ORDER BY a.EmpId, a.rowno

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

#10

On 25-04-2011 15:31, Erland Sommarskog wrote:

Quote:

Erland Sommarskog (esquel (AT) sommarskog (DOT) se) writes:
Thanks for the sample data!

I see that I had forgot the join condition on EmpId - without the query is
kind of meaningless.

The principle is simple: number the rows, and then make a self-join on
the row number with the next number.
JOIN numbered b ON b.rowno = a.rowno +1
AND b.EmpId = a.EmpId

Yes, that was what I had in mind as well. Just didn't manage to put it
all together.

Thanks, Henk