Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Problems with CTE (recursive)

Author  Topic  thjens Starting Member 1 Post Posted - 2011-06-16 : 08:41:49 Hi all. I've been trying to get a recursive code to work. I can't seem to wrap my head around this one. This script will produce the test data: --===== If the test table already exists, drop it USE [vptjTemp] GO IF OBJECT_ID('ShopHistory','U') IS NOT NULL DROP TABLE ShopHistory --===== Create the test table with CREATE TABLE ShopHistory (ShopID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, ShopName varchar(50), OldShopID int, NewShopID int, DateClosed DATEtime) --===== Setup any special required conditions especially where dates are concerned --SET DATEFORMAT DMY go SET IDENTITY_INSERT ShopHistory ON INSERT INTO ShopHistory (ShopID, ShopName, OldShopID, NewShopID, DateClosed) select '1206', 'Shop1206', '', '1487032' ,'2003-08-31' union all select '1875', 'Shop1875', '', '1325596' ,'2001-05-20' union all select '1230', 'Shop1230', '', '' ,'' union all select '1255', 'Shop1255', '', '' ,'2009-10-15' union all select '1974', 'Shop1974', '', '1925767' ,'2010-02-28' union all select '1325596', 'Shop1325596', '1875', '1422021' ,'2003-04-30' union all select '1422021', 'Shop1422021', '1325596', '1589381' ,'2007-02-01' union all select '1487032', 'Shop1487032', '1206', '' ,'2009-12-31' union all select '1589381', 'Shop1589381', '1422021', '' ,'' union all select '1925767', 'Shop1925767', '1974', '' ,'' SET IDENTITY_INSERT ShopHistory off What happens is that a shop may close and change owner. Thereby a new shop is created. The old shop get a value in NewID, and the new shop gets a value in OldID What I want is to sort/group the data for each shop with the current shop, and the history for that shop. Fetch the data: ;WITH ShopHistorySort(ShopID, CurrShopID, ShopName, OldShopID, NewShopID, DateClosed) AS ( SELECT ShopID, ShopID AS CurrShopID, ShopName, OldShopID, NewShopID, DateClosed FROM ShopHistory AS an --where ShopID = 1875 or ShopID = 1206 or ShopID = 1974 or ShopID = 1230 or ShopID = 1255 UNION ALL SELECT bn.ShopID, currShopID AS CurrShopID, bn.ShopName, bn.OldShopID, bn.NewShopID, bn.DateClosed FROM ShopHistory AS bn inner JOIN ShopHistorySort AS bh ON bn.ShopID = bh.NewShopID WHERE bn.ShopID <>0 ) SELECT * FROM ShopHistorySort ORDER BY CurrShopID desc, ShopID desc This produces an output where, if a shop is in the middle of the chain, the shops are repeated, which is natural because of the nature of the data. Try this script and you'll see. The problem: If I activate the where clause in the anchor, the output is almost the way I want it, apart from the CurrShopID, which should always contain the last shop in the chain, not the first. So for shop 1589381 (which start with 1875), the CurrShopID should be 1589381, not 1875. And of course, I need this working without the where clause active somehow. Thanks a lot for reading this far (-: Any insight on this would be much appreciated. Best regards, Thomas Jensen.