Database

KipkoechE wrote:

create a table call marks at the click of a button

DECLARE @TestID INT, @RemCount INT, @irow INT;

IF OBJECT_ID('tempdb..#UpdateAgent') IS NOT NULL DROP TABLE #UpdateAgent;
SELECT TestID, CNT AS RemCount
    ,  ROW_NUMBER() OVER(ORDER BY TestID) AS IDX
INTO #UpdateAgent
FROM #MyTestCounts;

SELECT @MIDX = MAX(IDX) FROM #UpdateAgent

WHILE @irow <= @MIDX
BEGIN
    SELECT @TestID = TestID, @RemCount = RemCount
    FROM #UpdateAgent
    WHERE IDX = @irow;

    IF @RemCount < 0 SET @RemCount = 0;

    UPDATE WQueueCheck
    SET TestID         = @TestID
    FROM #MyTestBase
    INNER JOIN (
            SELECT TOP (@RemCount) TQB.IDX
            FROM #MyTestBase TQB
            WHERE TestID IS NULL
            ORDER BY NEWID() ) X
        ON #MyTestBase.IDX = X.IDX;

    SET @irow += 1;
END

CREATE TABLE numbersequence (Number  int  not null);
INSERT INTO numbersequence(Number)
SELECT  TOP 100 row_number() over(order by t1.number) as N
FROM    master..spt_values t1 
CROSS JOIN master..spt_values t2
;

select  testid
       ,ROW_NUMBER() OVER(order by testid) AS rn
from    MyTestCounts tc,numbersequence n
where   tc.cnt >= n.number

with counts as (
    select  testid
           ,ROW_NUMBER() OVER(order by testid) AS rn
    from    MyTestCounts tc,numbersequence n
    where   tc.cnt >= n.number
    )
,tqb as (
    SELECT  TOP (select sum(cnt) from MyTestCounts) TQB.IDX
           ,ROW_NUMBER() OVER(order by testid) AS rn
    FROM    MyTestBase TQB
    WHERE   TestID IS NULL
    ORDER BY NEWID()
    )
select  IDX,TestID
from    counts c
join    tqb t
    on  c.rn = t.rn

UPDATE #MyTestBase
SET TestID = (SELECT COUNT(1) NumOfPoint
			  FROM #MyTestBase AS mtb
			  WHERE mtb.ExampleID = #MyTestBase.ExampleID
			  GROUP BY mtb.ExampleID)