Line by line (no cursor or loops)

Question

Here I have an example of data from students having RollNumbers and their coursecodes.

------------------------- Roll CourseCode -------------------------- 1011 CS201 2213 CS201 3312 CS101 4000 CS201 1011 CS101 5312 ME102 1011 PT101 3312 ME102 

The result should be Coursecode and its exam date.

e.g. (Sort individual exchange codes)

First, I select CS201 and assign this coursecode date; placing it in a temporary table, then I select CS101 and check the temporary table to see if the RollNumbers of this Coursecode will match any other RollNumber of another Coursecode in the temporary table.

 --------------------- Code Date --------------------- CS101 1 CS201 2 ME102 1 PT101 3 

My code is:

• #temp3 contains all the data (CourseCodes, RollNumbers)
• #mytemp1 (output)

and the cursor contains distinctive coursecodes

 SET @cursor = CURSOR FOR SELECT DISTINCT coursecode FROM #temp3 ORDER BY CourseCode OPEN @cursor FETCH NEXT FROM @cursor INTO @cursorid WHILE @@FETCH_STATUS = 0 BEGIN BEGIN SET @j=1 WHILE(@j !=9999999) BEGIN IF( SELECT COUNT(*) FROM #temp3 WHERE CourseCode = @cursorid AND RegdNo IN ( SELECT RegdNo FROM #temp3 WHERE CourseCode IN ( SELECT coursecode FROM #myTemp1 WHERE counter1 = @j) )) = 0 BEGIN INSERT INTO #myTemp1 VALUES (@cursorid,@j) SET @j=9999999 END ELSE BEGIN SET @j = @j + 1 END END END FETCH NEXT FROM @cursor INTO @cursorid END CLOSE @cursor DEALLOCATE @cursor 

This code works fine, but takes too much time (4110222 entries)

Any help would be appreciated

Answer 1

Here is the code. I believe that you have an error in the output, and CS101 should be preceded by CS201 :

 DECLARE @t TABLE ( Roll INT, Code CHAR(5) ) INSERT INTO @t VALUES ( 1011, 'CS201' ), ( 2213, 'CS201' ), ( 3312, 'CS101' ), ( 4000, 'CS201' ), ( 1011, 'CS101' ), ( 5312, 'ME102' ), ( 1011, 'PT101' ), ( 3319, 'ME102' ); WITH cte1 AS ( SELECT code , ROW_NUMBER() OVER ( ORDER BY Code ) AS rn FROM @t GROUP BY code ), cte2 AS ( SELECT code , rn , 1 AS Date FROM cte1 WHERE rn = 1 UNION ALL SELECT c1.code , c1.rn , CASE WHEN EXISTS ( SELECT * FROM @ta JOIN @tb ON a.Roll = b.Roll JOIN cte1 c ON c.rn < c1.rn AND b.Code = c.code WHERE a.code = c1.code ) THEN 1 ELSE 0 END FROM cte1 c1 JOIN cte2 c2 ON c1.rn = c2.rn + 1 ), cte3 AS ( SELECT Code , CASE WHEN Date = 0 THEN 1 ELSE SUM(Date) OVER ( ORDER BY rn ) END AS Date FROM cte2 ) SELECT * FROM cte3 

Conclusion:

 Code Date CS101 1 CS201 2 ME102 1 PT101 3 

EDIT:

cte1 will return:

 code rn CS101 1 CS201 2 ME102 3 PT101 4 

The main work is done in cte2 . This is a recursive generic table expression. First you take the top 1 line from cte1 :

 SELECT code , rn , 1 AS Date FROM cte1 WHERE rn = 1 

Then recursion progresses:

You join cte1 on cte2 and select the following rns (2, 3 ...) and check if there are any roles in CS201 that match the rollers of the previous codes ( CS101 ) in the first step, check if there are any rolls in ME102 , which correspond to rolls of previous codes ( CS101, CS201 ) in the second stage, etc. If exists, you return 1 else 0:

 code rn Date CS101 1 1 CS201 2 1 ME102 3 0 PT101 4 1 

The last cte3 does the following: if Date = 0, then return 1, otherwise return the sum of the dates in the previous lines, including the current line.

EDIT1:

Since my understanding was incorrect, this is another statement:

 WITH cte AS ( SELECT code , ROW_NUMBER() OVER ( ORDER BY Code ) AS rn FROM @t GROUP BY code ) SELECT co.Code, DENSE_RANK() OVER(ORDER BY ISNULL(o.Code, co.Code)) AS Date FROM cte co OUTER APPLY(SELECT TOP 1 ci.Code FROM cte ci WHERE ci.rn < co.rn AND NOT EXISTS(SELECT * FROM @t WHERE code = ci.code AND roll IN(SELECT roll FROM @t WHERE code = co.code)) ORDER BY ci.rn) o ORDER BY co.rn 

Conclusion:

 Code Date CS101 1 CS201 2 ME102 1 PT101 2 

EDIT2:

This is crazy, but here is a code that seems to work:

 WITH cte AS ( SELECT * , ROW_NUMBER() OVER ( PARTITION BY roll ORDER BY Code ) AS Date FROM @t ) SELECT Code , MAX(Date) AS Date FROM cte GROUP BY Code