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

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

Re: Help needed to write a qury

Show all headers | View raw

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

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