August 26, 2011 at 8:24 am
Hi guys/girls,
Could anyone point to the right direction as to how to convert this cursor to something like a set-based query?
I've been fiddling for as long time and know that converting to a while loop might not help with anything at all.
I'm trying to find BoxID's which have box positions which are not in a sequence.
Example:
BoxID Province BoxPosition
5632 KZN 1
5632 KZN 2
5632 KZN 3
5633 KZN 1
5633 KZN 3
5633 KZN 4
5632 Limpompo 1
5632 Limpompo 2
5632 Limpompo 5
5636 Limpompo 1
5636 Limpompo 2
5636 Limpompo 3
In the above example, BoxID's 5632 in Limpompo and 5633 in KZN don't have a sequential BoxPosition or we may say are missing a boxPosition which in turn I add onto a table variable @NonSequentialBoxPositions as seen below.
The database has around 22 000 000 (22 million) records and I don't even want to try this query on so much data.
Any suggestions?
/* Report 5 Box Where Box Position is NOT sequential Per Province */
SET NOCOUNT ON
DECLARE @BoxID int, @BoxPosition int, @ProvinceID int,
@message varchar(200)
DECLARE @NonSequentialBoxPositions
TABLE (NonSeqBoxPosID int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
BoxID int, ProvinceID int)
DECLARE provinceBox_cursor CURSOR FOR
SELECT DISTINCT TOP 9000 BoxID, ProvinceID
FROM [Application]
WHERE FlagDeleted = 0 AND BoxPosition > 0 AND BoxID > 0
ORDER BY BoxID, ProvinceID
OPEN provinceBox_cursor
FETCH NEXT FROM provinceBox_cursor
INTO @BoxID, @ProvinceID
WHILE @@FETCH_STATUS = 0
BEGIN
-- Declare an inner cursor based on BoxID & ProvinceID from the outer cursor.
DECLARE BoxPosition_cursor CURSOR FOR
SELECT DISTINCT BoxPosition
FROM [Application]
WHERE ProvinceID = @ProvinceID AND BoxID = @BoxID -- Variable value from the outer cursor
ORDER BY BoxPosition
OPEN BoxPosition_cursor
FETCH NEXT FROM BoxPosition_cursor INTO @BoxPosition
DECLARE @OldPosition INT
SET @OldPosition = NULL
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@BoxPosition <> (@OldPosition + 1))
BEGIN
INSERT INTO @NonSequentialBoxPositions (BoxID, ProvinceID) VALUES (@BoxID, @ProvinceID)
BREAK
END
SET @OldPosition = @BoxPosition
FETCH NEXT FROM BoxPosition_cursor INTO @BoxPosition
END
CLOSE BoxPosition_cursor
DEALLOCATE BoxPosition_cursor
-- Get the next vendor.
FETCH NEXT FROM provinceBox_cursor
INTO @BoxID, @ProvinceID
END
CLOSE provinceBox_cursor
DEALLOCATE provinceBox_cursor
SELECT [Name] Province, COUNT([Name]) [Non-Sequential]--, tv.BoxID
FROM @NonSequentialBoxPositions tv
INNER JOIN [Province] p ON p.ProvinceID = tv.ProvinceID
GROUP BY [Name]
ORDER BY Province
SET NOCOUNT OFF
I forgot to mention this in under SQL 2005;
Moderators, please move this to appropriate sub-form?
August 26, 2011 at 8:54 am
DROP TABLE #Application
CREATE TABLE #Application (BoxID INT, Province VARCHAR(10), BoxPosition INT)
INSERT INTO #Application (BoxID, Province, BoxPosition)
SELECT 5632, 'KZN', 1 UNION ALL
SELECT 5632, 'KZN', 2 UNION ALL
SELECT 5632, 'KZN', 3 UNION ALL
SELECT 5633, 'KZN', 1 UNION ALL
SELECT 5633, 'KZN', 3 UNION ALL
SELECT 5633, 'KZN', 4 UNION ALL
SELECT 5632, 'Limpompo', 1 UNION ALL
SELECT 5632, 'Limpompo', 2 UNION ALL
SELECT 5632, 'Limpompo', 5 UNION ALL
SELECT 5636, 'Limpompo', 1 UNION ALL
SELECT 5636, 'Limpompo', 2 UNION ALL
SELECT 5636, 'Limpompo', 3
SELECT BoxID, Province
FROM (
SELECT BoxID, Province, BoxPosition,
BoxPositionID = ROW_NUMBER() OVER (PARTITION BY BoxID, Province ORDER BY BoxPosition)
FROM #Application
) d
WHERE BoxPosition <> BoxPositionID
GROUP BY BoxID, Province
ORDER BY Province, BoxID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 29, 2011 at 2:48 am
Wow, ChrisM thanks, I'm going to check this out. I seemed close to what I want. Thanks again
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply