Refactoring Old / Uncommented SQL

I came across the uncommented sql below. I needed to reuse the logic that was being applied here somewhere else. This was taken out of one of our stored procedures. (I renamed some of the tables for this post)

INSERT INTO #PrintItems(PersonID, RowID)
SELECT t2.PersonID, t2.RowID
FROM #Items t INNER JOIN #Items t2
ON t.PersonID=t2.PersonID AND t.RowID<=t2.RowID
GROUP BY t2.PersonID, t2.RowID
HAVING CASE WHEN SUM(t.Lines)>@linesPerPage THEN 0 ELSE SUM(t.Lines) END<>0

SET @Rows=@@ROWCOUNT

WHILE @Rows<>0
BEGIN

	SET @CycleCount=@CycleCount+1

	INSERT #PrintSets(RowID, PrintTemplateID)
	SELECT t.RowID, PL.UID+(@UniqueSeed*@CycleCount)
	FROM #Items t
		INNER JOIN #PrintItems BI ON t.RowID=BI.RowID
		INNER JOIN @People PL ON t.PersonID=PL.PersonID

	DELETE #PrintItems

	DELETE t
	FROm #Items t INNER JOIN #PrintSets BS
	ON t.RowID=BS.RowID

	INSERT INTO #PrintItems(PersonID, RowID)
	SELECT t2.PersonID, t2.RowID
	FROM #Items t INNER JOIN #Items t2
	ON t.PersonID=t2.PersonID AND t.RowID<=t2.RowID
	GROUP BY t2.PersonID, t2.RowID
	HAVING CASE WHEN SUM(t.Lines)>@linesPerPage THEN 0 ELSE SUM(t.Lines) END<>0

	SET @Rows=@@ROWCOUNT
END

Without any comments, it took me some time to understand what it was trying to accomplish.
Don’t ask me why PrintTemplateID is looking for this particular format, but I was looking for a way to refactor this code as I needed to reuse it elsewhere.

The above sql was looking to generate PrintTemplateID, where each million denoted a page for a given person, and each page could have @linesPerPage.
RPL.UID being the person’s rownum based on their name

I’m not sure when the query above was written(My guess is post Sql 2005), but I am imagine that even the first query below would run pre sql 2005… Regardless, the lack of comments above made me quite sad.

--Foreach rowid, count how many rows have come before it for the given person
--Increment PrintTemplateID by UniqueSeed for each batch of rows over @linesPerPage
UPDATE T
SET PrintTemplateID = T2.PrintTemplateID
FROM #items AS T
INNER JOIN (
SELECT t2.RowID, RPL.UID + ((COUNT(1)/(@linesPerPage+1))+1) * @UniqueSeed AS PrintTemplateID
FROM #items AS T
INNER JOIN #items AS T2 ON T.RowID <= t2.RowID AND t2.PersonID = t.PersonID
INNER JOIN @People AS RPL ON T2.PersonID = RPL.PersonID
GROUP BY RPL.UID, T2.RowID
) AS T2 ON T.RowID = t2.RowID

--OR
--Using Rank Over Partition By
UPDATE T
SET PrintTemplateID = T2.PrintTemplateID
FROM #items AS T
INNER JOIN (
SELECT t.RowID, RPL.UID + ((Rank() OVER (PARTITION BY T.PersonID ORDER BY t.RowID asc)/(@linesPerPage+1))+1) * @UniqueSeed  AS PrintTemplateID
FROM #items AS T
INNER JOIN @People AS RPL ON T.PersonID = RPL.PersonID
) AS T2 ON T.RowID = t2.RowID