How to obtain the maximum number of attempts made by the user_field to achieve or reach a code from the given set of codes (82,83).
That is if a user_field finds the code 82 or 83 for the first time, the number of attempts made by the user_field should print and so on..
Table_1 is the actual data table and Table_2 is the expected output table
Table 1:
User_Field | code
-------------------
100 | 13
100 | 16
100 | 13
100 | 82
98 | 52
35 | 13
98 | 82
35 | 83
35 | 16
35 | 82
and the expected output in Table_2:
User_field | attempts | first_matchcode_in_the_given_codeset
------------------------------------------------------------
100 | 4 | 82
98 | 2 | 82
35 | 2 | 83
TRY THIS....
DECLARE @TABLE1 TABLE
(
SeqNo INT,User_Field INT, code INT
)
INSERT INTO @TABLE1
SELECT 1,100 , 13 UNION ALL
SELECT 2,100 , 16 UNION ALL
SELECT 3, 100 , 13 UNION ALL
SELECT 4, 100 , 82 UNION ALL
SELECT 5, 98 , 52 UNION ALL
SELECT 6, 35 , 13 UNION ALL
SELECT 7, 98 , 82 UNION ALL
SELECT 8, 35 , 83 UNION ALL
SELECT 9 , 35 , 16 UNION ALL
SELECT 10, 35 , 82
;WITH CTE AS(
SELECT User_Field,Code,ROW_NUMBER() OVER( PARTITION BY User_Field ORDER BY SeqNo) Attempts FROM @TABLE1
)
, CTE2 AS
(
SELECT User_Field,Code,Attempts,ROW_NUMBER() OVER( PARTITION BY User_Field ORDER BY User_Field) Nos FROM CTE WHERE CODE IN (83,82)
)
SELECT User_Field,Code,Attempts
FROM CTE2
WHERE Nos =1
I think this will work for sqlserver 2000
SELECT T1.User_Field,
COUNT(T1.SeqNo) Attempts,
(SELECT CODE FROM @TABLE1 WHERE SeqNo = T2.SeqNo AND User_Field = T1.User_Field) CODE
FROM
@TABLE1 T1
INNER JOIN
(
SELECT User_Field,MIN(SeqNo) SeqNo
FROM @TABLE1 T1
WHERE CODE = 83 OR CODE = 82
GROUP BY User_Field
) T2 ON T2.User_Field = T1.User_Field AND T1.SeqNo <= T2.SeqNo
GROUP BY T1.User_Field,T2.SeqNo