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

Re: Help needed to write a qury

Path	csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!news.albasani.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Help needed to write a qury
Date	Mon, 25 Apr 2011 15:31:25 +0200
Organization	Erland Sommarskog
Lines	33
Message-ID	<Xns9ED29DEAC6336Yazorman@127.0.0.1> (permalink)
References	<95071489-1d30-4d5b-b075-aa465d7b654c@x37g2000prb.googlegroups.com> <Xns9ED1DE4FB98A7Yazorman@127.0.0.1> <4db4fdc3$0$41114$e4fe514c@news.xs4all.nl> <Xns9ED268284C5CYazorman@127.0.0.1> <4db5449d$0$32470$e4fe514c@news.xs4all.nl> <Xns9ED29DD33336EYazorman@127.0.0.1>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	8bit
Injection-Info	mx02.eternal-september.org; posting-host="DD6dU+BfJNjsjSP4/K/V7w"; logging-data="32229"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19Fmbn2N5oIQe7c0lC5pJ4b"
User-Agent	Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock	sha1:p2QxsN9U3XUGkkQrCxEuvBXnIFM=
Xref	x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:206

Show key headers only | View raw

Erland Sommarskog (esquel@sommarskog.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.

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@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

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