Count Attempts!

microsoft.public.sqlserver.programming

30 Sep 2005 6:20 AM

Adam Knight

Hi all,

I want to SELECT the most recent ATTEMPT_ID (in this case attempt_id=3)
and determine how many attempts (using COUNT) have IDENTICAL QUESTION LISTS
based on the ATTEMPT_RESULTS table?

IE:
Attempt 2 has an IDENTICAL QUESTION LIST (to 3) in the ATTEMPT_RESULTS
table using the DDL below.
Attempt 1 does not qaulify because it has an extra question 'id = 4'.

Based on the data below the result of this query should be 2.

I would appreciate any help in this, as i am not sure how to implement this
logic in SQL.

Thanks to those who responsd.

DDL:

CREATE TABLE [dbo].[attempts](
[attempt_id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_attempts] PRIMARY KEY CLUSTERED
([attempt_id] ASC) ON [PRIMARY]) ON [PRIMARY]

CREATE TABLE [dbo].[attempt_results](
[attempt_result_id] [int] IDENTITY(1,1) NOT NULL,
[attempt_id] [int] NULL,
[question_id] [int] NOT NULL,
CONSTRAINT [PK_attempt_results] PRIMARY KEY CLUSTERED
([attempt_result_id] ASC) ON [PRIMARY]) ON [PRIMARY]

INSERT INTO [attempts] ([name]) VALUES ('Temp 1')
INSERT INTO [attempts] ([name]) VALUES ('Temp 2')
INSERT INTO [attempts] ([name]) VALUES ('Temp 3')

INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,1)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,2)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,3)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,4)

INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,1)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,2)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,3)

INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,1)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,2)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,3)

30 Sep 2005 7:17 AM

John Bell

Hi Adam

Thanks for the DDL and example data. Maybe something like the following is
what you require:

SELECT r.[attempt_id]
FROM [dbo].[attempt_results] r
JOIN [dbo].[attempt_results] a ON a.[attempt_id] = 3
AND a.[attempt_id] <> r.[attempt_id]
AND a.[question_id] = r.[question_id]
GROUP BY r.[attempt_id]
HAVING count(*) = ( SELECT COUNT(question_id) as cnt
FROM [dbo].[attempt_results]
WHERE [attempt_id] = 3 )

John

Show quote

"Adam Knight" wrote:

> Hi all,
>
> I want to SELECT the most recent ATTEMPT_ID (in this case attempt_id=3)
> and determine how many attempts (using COUNT) have IDENTICAL QUESTION LISTS
> based on the ATTEMPT_RESULTS table?
>
> IE:
> Attempt 2 has an IDENTICAL QUESTION LIST (to 3) in the ATTEMPT_RESULTS
> table using the DDL below.
> Attempt 1 does not qaulify because it has an extra question 'id = 4'.
>
> Based on the data below the result of this query should be 2.
>
> I would appreciate any help in this, as i am not sure how to implement this
> logic in SQL.
>
> Thanks to those who responsd.
>
> DDL:
>
> CREATE TABLE [dbo].[attempts](
> [attempt_id] [int] IDENTITY(1,1) NOT NULL,
> [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_attempts] PRIMARY KEY CLUSTERED
> ([attempt_id] ASC) ON [PRIMARY]) ON [PRIMARY]
>
>
> CREATE TABLE [dbo].[attempt_results](
> [attempt_result_id] [int] IDENTITY(1,1) NOT NULL,
> [attempt_id] [int] NULL,
> [question_id] [int] NOT NULL,
> CONSTRAINT [PK_attempt_results] PRIMARY KEY CLUSTERED
> ([attempt_result_id] ASC) ON [PRIMARY]) ON [PRIMARY]
>
> INSERT INTO [attempts] ([name]) VALUES ('Temp 1')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 2')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 3')
>
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,3)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,4)
>
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,3)
>
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,3)
>
>
>
>

30 Sep 2005 7:40 AM

this should do:

e.g.
create function dbo.getlist(@attempt_id int)
returns sysname
as
begin
declare @s sysname
select @s=isnull(@s+'|','')+cast(question_id as sysname)
from attempt_results
where attempt_id=@attempt_id
order by question_id
return @s
end
go
select x.id,count(*) cnt
from(select max(attempt_id) id
    from attempt_results) x join (select distinct attempt_id id
                                  from attempt_results) y
    on dbo.getlist(x.id)=dbo.getlist(y.id)
group by x.id

--
-oj

Show quote

"Adam Knight" <a***@pertrain.com.au> wrote in message
news:%23WIiFcYxFHA.700@TK2MSFTNGP11.phx.gbl...
> Hi all,
>
> I want to SELECT the most recent ATTEMPT_ID (in this case attempt_id=3)
> and determine how many attempts (using COUNT) have IDENTICAL QUESTION
> LISTS based on the ATTEMPT_RESULTS table?
>
> IE:
> Attempt 2 has an IDENTICAL QUESTION LIST (to 3) in the ATTEMPT_RESULTS
> table using the DDL below.
> Attempt 1 does not qaulify because it has an extra question 'id = 4'.
>
> Based on the data below the result of this query should be 2.
>
> I would appreciate any help in this, as i am not sure how to implement
> this logic in SQL.
>
> Thanks to those who responsd.
>
> DDL:
>
> CREATE TABLE [dbo].[attempts](
> [attempt_id] [int] IDENTITY(1,1) NOT NULL,
> [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_attempts] PRIMARY KEY CLUSTERED
> ([attempt_id] ASC) ON [PRIMARY]) ON [PRIMARY]
>
>
> CREATE TABLE [dbo].[attempt_results](
> [attempt_result_id] [int] IDENTITY(1,1) NOT NULL,
> [attempt_id] [int] NULL,
> [question_id] [int] NOT NULL,
> CONSTRAINT [PK_attempt_results] PRIMARY KEY CLUSTERED
> ([attempt_result_id] ASC) ON [PRIMARY]) ON [PRIMARY]
>
> INSERT INTO [attempts] ([name]) VALUES ('Temp 1')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 2')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 3')
>
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,3)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,4)
>
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,3)
>
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,3)
>
>
>