How to structure a CTE - SQL Server Forums

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)

How to structure a CTE

Author

Topic

WillieMakit
Starting Member

1 Post

Posted - 2010-01-09 : 15:06:37

I have inherited a piece of code that is a bit of a pig. It uses a cursor to loop through a list of stores and count customers. I would like to change this to use a CTE, but am not sure how to configure it.

The basic functionality of the SP is:

Declare variables

Set up start and end dates

Declare Cursor

Get a list of stores to check

Open the Cursor

While… Loops through the stores

Insert a new record into the Destination Table

Count up the new customers for this store

Count up the returning customers to the store

Update the new record with the totals

Loop

The code:

view plaincopy to clipboardprint?
01.DECLARE @StartDate datetime
02.DECLARE @EndDate datetime
03.DECLARE @Location varchar
04.DECLARE @Count int
05.DECLARE @Duration int
06.
07.SET @StartDate = (SELECT TOP 1 TheDate from Customers
08.ORDER BY TheDate DESC)
09.
10.IF @StartDate IS NULL
11.BEGIN
12. SET @StartDate = DATEADD(dd, 1, '12/31/2008')
13.END
14.ELSE
15.BEGIN
16. SET @StartDate = DATEADD(dd, 1, @StartDate)
17.END
18.SET @EndDate = DATEADD(dd, 1, @StartDate)
19.
20.-- If StartDate = Today, we don't want to do the update
21.
22.DECLARE VSCursor CURSOR FOR
23. SELECT AcctNo, Location
24. FROM Stores
25. WHERE Status < 3
26.
27.DECLARE @AcctNo INT
28.SELECT @AcctNo = 0
29.SELECT @Count = 0
30.
31.OPEN VSCursor
32.WHILE (0=0) BEGIN
33. FETCH NEXT FROM VSCursor INTO @AcctNo, @Location
34.
35. IF @@FETCH_STATUS <> 0 BREAK
36.
37. INSERT INTO Customers(TheDate, AcctNo) VALUES(@StartDate, @AcctNo )
38.
39. -- Now select all totals for New Customers
40. SET @Count = (
41. SELECT TOP 1 COUNT(DISTINCT NextTrip.Store)
42. FROM FirstTrip
43. LEFT OUTER JOIN NextTrip ON FirstTrip.CustID = NextTrip.CustID
44. WHERE (NextTrip.DTEnter >= @StartDate) AND (NextTrip.DTEnter < @EndDate) AND NextTrip.isBot = 0 AND (NextTrip.isRet = 0) AND (FirstTrip.AcctNo = @AcctNo) AND NextTrip.Store <> @Location
45. GROUP BY CONVERT(DATETIME, FLOOR(CONVERT(NUMERIC(18,9),NextTrip.DTEnter))), FirstTrip.AcctNo)
46.
47. SET @Duration = (
48. SELECT TOP 1 SUM(NextTrip.Dur) AS Duration
49. FROM FirstTrip
50. LEFT OUTER JOIN NextTrip ON FirstTrip.CustID = NextTrip.CustID
51. WHERE (NextTrip.DTEnter >= @StartDate) AND (NextTrip.DTEnter < @EndDate) AND NextTrip.isBot = 0 AND (NextTrip.isRet = 0) AND (FirstTrip.AcctNo = @AcctNo) AND NextTrip.Store <> @Location
52. GROUP BY CONVERT(DATETIME, FLOOR(CONVERT(NUMERIC(18,9),NextTrip.DTEnter))), FirstTrip.AcctNo)
53.
54. UPDATE Customers SET NewCust = @Count, NewCustDur = @Duration WHERE TheDate = @StartDate and AcctNo = @AcctNo
55.
56. -- Now get all the totals for Returning Customers
57. SET @Count =
58. (
59. SELECT TOP 1 COUNT(DISTINCT NextTrip.Store)
60. FROM FirstTrip
61. LEFT OUTER JOIN NextTrip ON FirstTrip.CustID = NextTrip.CustID
62. WHERE NextTrip.isBot = 0 AND NextTrip.Store <> @Location AND NextTrip.CustID IN
63. (
64. SELECT DISTINCT FirstTrip.CustID from FirstTrip
65. INNER JOIN NextTrip ON NextTrip.CustID = FirstTrip.CustID
66. WHERE FirstTrip.AcctNo = @AcctNo and FirstTrip.DateOn >= @StartDate and FirstTrip.DateOn < @EndDate and NextTrip.IsRet = 1
67. )
68. GROUP BY CONVERT(DATETIME, FLOOR(CONVERT(NUMERIC(18,9),NextTrip.DTEnter))), FirstTrip.AcctNo
69. )
70.
71. SET @Duration =
72. (
73. SELECT TOP 1 SUM(NextTrip.Dur) AS Duration
74. FROM FirstTrip
75. LEFT OUTER JOIN NextTrip ON FirstTrip.CustID = NextTrip.CustID
76. WHERE NextTrip.isBot = 0 AND NextTrip.Store <> @Location AND NextTrip.CustID IN
77. (
78. SELECT DISTINCT FirstTrip.CustID from FirstTrip
79. INNER JOIN NextTrip ON NextTrip.CustID = FirstTrip.CustID
80. WHERE FirstTrip.AcctNo = @AcctNo and FirstTrip.DateOn >= @StartDate and FirstTrip.DateOn < @EndDate and NextTrip.IsRet = 1
81. )
82. GROUP BY CONVERT(DATETIME, FLOOR(CONVERT(NUMERIC(18,9),NextTrip.DTEnter))), FirstTrip.AcctNo
83. )
84.
85. UPDATE Customers SET RetCust = @Count, RetCustDur = @Duration WHERE TheDate = @StartDate and AcctNo = @AcctNo
86.
87.END
88.CLOSE VSCursor
89.DEALLOCATE VSCursor
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @Location varchar
DECLARE @Count int
DECLARE @Duration int

SET @StartDate = (SELECT TOP 1 TheDate from Customers
ORDER BY TheDate DESC)

IF @StartDate IS NULL
BEGIN
SET @StartDate = DATEADD(dd, 1, '12/31/2008')
END
ELSE
BEGIN
SET @StartDate = DATEADD(dd, 1, @StartDate)
END
SET @EndDate = DATEADD(dd, 1, @StartDate)

-- If StartDate = Today, we don't want to do the update

DECLARE VSCursor CURSOR FOR
SELECT AcctNo, Location
FROM Stores
WHERE Status < 3

DECLARE @AcctNo INT
SELECT @AcctNo = 0
SELECT @Count = 0

OPEN VSCursor
WHILE (0=0) BEGIN
FETCH NEXT FROM VSCursor INTO @AcctNo, @Location

IF @@FETCH_STATUS <> 0 BREAK

INSERT INTO Customers(TheDate, AcctNo) VALUES(@StartDate, @AcctNo )

-- Now select all totals for New Customers
SET @Count = (
SELECT TOP 1 COUNT(DISTINCT NextTrip.Store)
FROM FirstTrip
LEFT OUTER JOIN NextTrip ON FirstTrip.CustID = NextTrip.CustID
WHERE (NextTrip.DTEnter >= @StartDate) AND (NextTrip.DTEnter < @EndDate) AND NextTrip.isBot = 0 AND (NextTrip.isRet = 0) AND (FirstTrip.AcctNo = @AcctNo) AND NextTrip.Store <> @Location
GROUP BY CONVERT(DATETIME, FLOOR(CONVERT(NUMERIC(18,9),NextTrip.DTEnter))), FirstTrip.AcctNo)

SET @Duration = (
SELECT TOP 1 SUM(NextTrip.Dur) AS Duration
FROM FirstTrip
LEFT OUTER JOIN NextTrip ON FirstTrip.CustID = NextTrip.CustID
WHERE (NextTrip.DTEnter >= @StartDate) AND (NextTrip.DTEnter < @EndDate) AND NextTrip.isBot = 0 AND (NextTrip.isRet = 0) AND (FirstTrip.AcctNo = @AcctNo) AND NextTrip.Store <> @Location
GROUP BY CONVERT(DATETIME, FLOOR(CONVERT(NUMERIC(18,9),NextTrip.DTEnter))), FirstTrip.AcctNo)

UPDATE Customers SET NewCust = @Count, NewCustDur = @Duration WHERE TheDate = @StartDate and AcctNo = @AcctNo

-- Now get all the totals for Returning Customers
SET @Count =
(
SELECT TOP 1 COUNT(DISTINCT NextTrip.Store)
FROM FirstTrip
LEFT OUTER JOIN NextTrip ON FirstTrip.CustID = NextTrip.CustID
WHERE NextTrip.isBot = 0 AND NextTrip.Store <> @Location AND NextTrip.CustID IN
(
SELECT DISTINCT FirstTrip.CustID from FirstTrip
INNER JOIN NextTrip ON NextTrip.CustID = FirstTrip.CustID
WHERE FirstTrip.AcctNo = @AcctNo and FirstTrip.DateOn >= @StartDate and FirstTrip.DateOn < @EndDate and NextTrip.IsRet = 1
)
GROUP BY CONVERT(DATETIME, FLOOR(CONVERT(NUMERIC(18,9),NextTrip.DTEnter))), FirstTrip.AcctNo
)

SET @Duration =
(
SELECT TOP 1 SUM(NextTrip.Dur) AS Duration
FROM FirstTrip
LEFT OUTER JOIN NextTrip ON FirstTrip.CustID = NextTrip.CustID
WHERE NextTrip.isBot = 0 AND NextTrip.Store <> @Location AND NextTrip.CustID IN
(
SELECT DISTINCT FirstTrip.CustID from FirstTrip
INNER JOIN NextTrip ON NextTrip.CustID = FirstTrip.CustID
WHERE FirstTrip.AcctNo = @AcctNo and FirstTrip.DateOn >= @StartDate and FirstTrip.DateOn < @EndDate and NextTrip.IsRet = 1
)
GROUP BY CONVERT(DATETIME, FLOOR(CONVERT(NUMERIC(18,9),NextTrip.DTEnter))), FirstTrip.AcctNo
)

UPDATE Customers SET RetCust = @Count, RetCustDur = @Duration WHERE TheDate = @StartDate and AcctNo = @AcctNo

END
CLOSE VSCursor
DEALLOCATE VSCursor

Any help in converting this to a cte would be much appreciated. Psuedo code would probably do fine.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-10 : 01:16:24

you dont require a cte here. you just need this:-


DECLARE @StartDate datetime 
DECLARE @EndDate datetime 
DECLARE @Location varchar 
DECLARE @Count int 
DECLARE @Duration int 
SET @StartDate = (SELECT TOP 1 TheDate from Customers 
ORDER BY TheDate DESC) 
IF @StartDate IS NULL 
BEGIN 
SET @StartDate = DATEADD(dd, 1, '12/31/2008') 
END 
ELSE 
BEGIN 
SET @StartDate = DATEADD(dd, 1, @StartDate) 
END 
SET @EndDate = DATEADD(dd, 1, @StartDate) 

INSERT INTO Customers(TheDate, AcctNo,NewCust,NewCustDur,RetCust,RetCustDur) 
SELECT @StartDate,s.AcctNo,t1.NewCustCnt,t1.NewCustDur,
t2.RetCustCnt,t2.RetCustDur
FROM Stores s
OUTER APPLY ( 
SELECT TOP 1 COUNT(DISTINCT NextTrip.Store) AS NewCustCnt,SUM(NextTrip.Dur) AS NewCustDur 
FROM FirstTrip 
LEFT OUTER JOIN NextTrip ON FirstTrip.CustID = NextTrip.CustID 
WHERE (NextTrip.DTEnter >= @StartDate) AND (NextTrip.DTEnter < @EndDate) AND NextTrip.isBot = 0 AND (NextTrip.isRet = 0) AND (FirstTrip.AcctNo = s.AcctNo) AND NextTrip.Store <> s.Location 
GROUP BY CONVERT(DATETIME, FLOOR(CONVERT(NUMERIC(18,9),NextTrip.DTEnter))), FirstTrip.AcctNo)t1
OUTER APPLY  ( 
SELECT TOP 1 COUNT(DISTINCT NextTrip.Store) AS RetCustCnt,SUM(NextTrip.Dur) AS RetCustDur
FROM FirstTrip 
LEFT OUTER JOIN NextTrip ON FirstTrip.CustID = NextTrip.CustID 
WHERE NextTrip.isBot = 0 AND NextTrip.Store <> s.Location AND NextTrip.CustID IN 
( 
SELECT DISTINCT FirstTrip.CustID from FirstTrip 
INNER JOIN NextTrip ON NextTrip.CustID = FirstTrip.CustID 
WHERE FirstTrip.AcctNo = s.AcctNo and FirstTrip.DateOn >= @StartDate and FirstTrip.DateOn < @EndDate and NextTrip.IsRet = 1 
) 
GROUP BY CONVERT(DATETIME, FLOOR(CONVERT(NUMERIC(18,9),NextTrip.DTEnter))), FirstTrip.AcctNo 
)t2 
WHERE Status < 3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources