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 10:14:20 +0200
Organization: Erland Sommarskog
Lines: 47
Message-ID: <Xns9ED268284C5CYazorman@127.0.0.1>
References: <95071489-1d30-4d5b-b075-aa465d7b654c@x37g2000prb.googlegroups.com> <Xns9ED1DE4FB98A7Yazorman@127.0.0.1> <4db4fdc3$0$41114$e4fe514c@news.xs4all.nl>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: mx03.eternal-september.org; posting-host="DD6dU+BfJNjsjSP4/K/V7w"; logging-data="2068"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX19EfRjsw1eOZn5EuocnwdFv"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:IWoUcpGUD376iAwYWaBEN9Th+O4=
Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:202

Henk van den Berg (hvandenberg@xs4all.nl) writes:
> 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@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