Row By Row ( Without Cursor or Loops)

Question

Here I have sample data of students having RollNumbers and their coursecodes.

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

Result should be Coursecode and their exam date

e.g (Sort Out Distinct Coursecodes)

First I am picking CS201 and assigning that coursecode a date; placing it in a temporary table,then I picked CS101 and will check in temporary table whether the RollNumbers of this Coursecode matches any other RollNumber of other Coursecode in the temporary table.

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

My code:

• #temp3 contains all data (CourseCodes, RollNumbers)
• #mytemp1 ( Output Data)

and cursor contains the Distinct 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 is working fine but taking too much time( 4110222 records)

Any help would be appreciated

Answer 1

Here is some code. I believe that you have error in output and CS101 should precede 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     @t a
                                                    JOIN @t b 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

Output:

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. It is recursive common table expression. First you take top 1 row from cte1:

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

Then the recursion progresses:

You are joining cte1 on cte2 and pick following rns (2, 3...) and check if there are any rolls in CS201 that match rolls in previous codes(CS101) in first step, check if there any rolls in ME102 that match rolls in previous codes(CS101, CS201) in second step etc. If exists you return 1 else 0:

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

Last cte3 does the following: if Date = 0 then return 1, else return sum of Dates in previous rows including current row.

EDIT1:

Since my understanding was incorrect here is one more 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     

Output:

Code    Date
CS101   1
CS201   2
ME102   1
PT101   2

EDIT2:

This is insane but, here is 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