Problems with CTE (recursive)

Question

Problems with CTE (recursive)

vptj

Grasshopper

Points: 15
More actions
June 15, 2011 at 5:41 am

#242184

Hi all. I've been trying to get a recursive code to work. I can't seem to wrap my head around this one.
The data (some of it, as I receive it from an external system). tbl1:
ShopID ShopName OldID NewID DateClosed
1206 NÆRMAT FORVIK 1487032 31.08.2003
1230 JOKER ASDAL
1255 COOP PRIX ASKIM AVD.181 15.10.2009
1875 ICA SPARMAT 6105 LAMBERTSETER 1325596 20.05.2001
1974 BARTNES OLAV 1925767 28.02.2010
1325596 ICA SPARMAT 6272 LAMBERTSETER 1875 1422021 30.04.2003
1422021 ICA NÆR AVD.6297 NYLÆNDE 1325596 1589381 01.02.2007
1487032 HANDELSSTEDET FORVIK AS 1206 31.12.2009
1589381 BUNNPRIS LAMBERTSETER 1422021
1925767 MATKROKEN AVD.7424 NAUST 1974
(dateformat = dd.mm.yyyy)
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.
Something like this (for shopID = 1875), tbl2:
ShopID CurrShopID*
1589381 1875 BUNNPRIS LAMBERTSETER 1422021
1422021 1875 ICA NÆR AVD.6297 NYLÆNDE 1325596 01.02.2007 1589381
1325596 1875 ICA SPARMAT 6272 LAMBERTSETER 1875 30.04.2003 1422021
1875 1875 ICA SPARMAT 6105 LAMBERTSETER 20.05.2001 1325596
*This colum should only contain the CurrShopID, in this case 1589381.
I have come up with this:
WITH ShopHistory(ShopID_, ShopName, OldID, NewID ,DateClosed) AS (
SELECT ShopID_, ShopName, OldID, NewID, DateClosed
FROM dbo.[BAXT AS$Shops] AS an
UNION ALL
SELECT an.ShopID_, an.ShopName, an.OldID, an.NewID, an.DateClosed
FROM dbo.[BAXT AS$Shops] AS an inner JOIN
ShopHistory AS sh ON an.ShopID = sh.NewID)
SELECT ShopID_, ShopName, OldID, NewID, DateClosed
FROM ShopHistory
This does not work. The results are just a flat structure with only parents.
But if I add the line below the thing works for some strange reason:
where ShopID_ = 1875 (in the anchor part).
Then it produces the data in tbl2, but not sorted the correct way. It does not show the CurrShopID, and list is showing the shops backwards. I want the shop that is not closed (or the last in the chain) on the top of the list.
So the simple(!) question is how to produce results as in tbl2 for all the shops in one view? (there are about 80 000, some has never changed owner, some has just been closed, or a combination).
Maybe CTE is not the way to go?
Thanks a lot for reading this far (-:
Any insight on this would be much appreciated.
Best regards,
Thomas Jensen.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

Hi and welcome to SSC. It is pretty hard to provide anything resembling a real answer to your question based on what you have posted. Take a look at the link in my signature to read best practices on asking a question in such a way as to generate a solid response. We would need some ddl and sample data (insert statements) and the desired output based on the sample data.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vptj Grasshopper Points: 15 More actions · Answer 2

Hi. I'm sorry about the messy post. Here is a script that I think conforms to the rules of this forum:

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

;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.

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.

I hope this is a little better, and please, if any more information is needed, I will be happy to provide it.

Best regards,

Thomas Jensen.

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

I'm going to be off site the next couple days with a client. I will try to check back this evening and help if I can. Hopefully somebody else will jump in here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 4

I think you had it Thomas, save for some sorting:

-- expected results from original post for 1875 as a tabular resultset

SELECT 1589381 AS ShopID,1875 AS CurrShopID,'BUNNPRIS LAMBERTSETER' AS ShopName,1422021 AS NewShopId

UNION ALL SELECT 1422021,1875,'ICA NÆR AVD.6297 NYLÆNDE 1325596 01.02.2007',1589381

UNION ALL SELECT 1325596,1875,'ICA SPARMAT 6272 LAMBERTSETER 1875 30.04.2003',1422021

UNION SELECT 1875,1875,'ICA SPARMAT 6105 LAMBERTSETER 20.05.2001',1325596 ;

-- your original query with some sorting mods, but no other code changes

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 ShopID,

CurrShopID,

ShopName,

NULLIF(NewShopID, 0) AS NewShopID,

NULLIF(DateClosed, 0) AS DateClosed

FROM ShopHistorySort

--WHERE CurrShopID = 1875

ORDER BY CurrShopID ASC,

CASE WHEN DateClosed = '1900-01-01 00:00:00.000' THEN GETDATE()

ELSE DateClosed

END ASC ;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

vptj Grasshopper Points: 15 More actions · Answer 5

Hi. I'm grateful for the reply but I can't seem to see any particular changes to the result set?

For the chain that starts with shopID 1875 the currentID is still 1875 not 1589381 which is the last shop in the chain.

And, the shopID 1589381 is repeated 4 times, the number of shops in the chain. It should only occur once. And that is "togheter" with the other shops in the change.

Remember, each shop only knows about it prev. and next shopID.

I was thinking to traverse the chain from the bottom (starting with the highest ShopID) or somehting. This would most likely solve the CurrShopID problem. Adding an ORDER BY ShopID in the anchor part seemd like a good idea, but nothing changes. Any idea as to why?

Maby CTE's don't care about that clause?