Hi,

I am trying to work out how to perform a query to extract 3 records from a
large DB but where the records are concurrent.

Basically, we have a system that assesses our suppliers. Each assessment is
stored in a table called Assessments. They are then awarded a Gold, Silver
or Bronze status depending on how well they did. The lower the score the
better.

I've now been asked to provide a report to show all Suppliers who have had 3
concurrent bronze (denoted by a score of over 20) assessments.

I am not sure how to do this - does anyone have any advice?

Many thanks
Craig.


_____________________________________________________________________
Kingfield Heath Ltd. Email Disclaimer

Confidentiality : This email and its attachments are intended for the
above-named only and may be confidential. If they have come to you in
error you must take no action based on them, nor must you copy or
show them to anyone; please reply to this email and highlight the
error.

Security Warning : Please note that this email has been created in
the knowledge that the internet is not a 100% secure communications
medium. We advise that you understand and observe this lack of
security when emailing us.

Viruses : Although we have taken steps to ensure that this email and
attachments are free from any virus, we advise that, in keeping with
good computing practice, the recipient should ensure they are
actually virus free.
_____________________________________________________________________

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Craig Flannigan" <ckf@k...>
Subject: [asp_databases] SQL problem


: I am trying to work out how to perform a query to extract 3 records from a
: large DB but where the records are concurrent.
:
: Basically, we have a system that assesses our suppliers. Each assessment
is
: stored in a table called Assessments. They are then awarded a Gold, Silver
: or Bronze status depending on how well they did. The lower the score the
: better.
:
: I've now been asked to provide a report to show all Suppliers who have had
3
: concurrent bronze (denoted by a score of over 20) assessments.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This is probably something that you might want to look at doing in your
business layer. SQL is inherently unsuited to this type of query (as you can
tell by the difficulty you are having in developing a query to do what you
want!), since SQL is a set-based languages, and sets are inherently
unordered.

Do you want to get those companies that have scored three concurrent bronzes
in the last three periods? Or any three consecutive periods? (I think you
meant "consecutive", not "concurrent"). And how do you want to deal with the
situation where a company has had more than one set of three consecutive
bronzes eg:

B B B S B B B

Also, what if the company had 5 consecutive bronzes? How many times does
this count? (Period 1 + Period 2 + Period 3, Period 2 + Period 3 + Period 4,
Period 3 + Period 4 + Period 5)?

etc. Without giving a complete case statement, you are going to have
difficulty getting an answer from the list. Also, if you really want to do
this in the database, you'd better post what DBMS you are using, since I
think you're going to have to use some of the procedural enhancements that
your DBMS provides (T-SQL, pl-sql etc)

Cheers
Ken

Hi Ken,

Thanks for your reply.

You're right, I did mean consecutive, rather than concurrent!

Basically the assessments are performed as and when a supplier arrives at
our warehouse. This could be weekly, or monthly. There are no set dates, so
really it would be to show 3 consecutive bronze assessments.

The Bronze assessments are a warning to a supplier that they are not
performing to our standards. 3 bronze assessments triggers a warning letter,
5 kicks them out altogether.

This is the reason I've been asked to find suppliers with 3 consecutive
bronze assessments.

I assumed this would be too much strain on a web server having to work out
consecutive assessments for 800 suppliers.

Regards
Craig.


-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: 07 August 2002 08:59
To: ASP Databases
Subject: [asp_databases] Re: SQL problem


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Craig Flannigan" <ckf@k...>
Subject: [asp_databases] SQL problem


: I am trying to work out how to perform a query to extract 3 records from a
: large DB but where the records are concurrent.
:
: Basically, we have a system that assesses our suppliers. Each assessment
is
: stored in a table called Assessments. They are then awarded a Gold, Silver
: or Bronze status depending on how well they did. The lower the score the
: better.
:
: I've now been asked to provide a report to show all Suppliers who have had
3
: concurrent bronze (denoted by a score of over 20) assessments.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This is probably something that you might want to look at doing in your
business layer. SQL is inherently unsuited to this type of query (as you can
tell by the difficulty you are having in developing a query to do what you
want!), since SQL is a set-based languages, and sets are inherently
unordered.

Do you want to get those companies that have scored three concurrent bronzes
in the last three periods? Or any three consecutive periods? (I think you
meant "consecutive", not "concurrent"). And how do you want to deal with the
situation where a company has had more than one set of three consecutive
bronzes eg:

B B B S B B B

Also, what if the company had 5 consecutive bronzes? How many times does
this count? (Period 1 + Period 2 + Period 3, Period 2 + Period 3 + Period 4,
Period 3 + Period 4 + Period 5)?

etc. Without giving a complete case statement, you are going to have
difficulty getting an answer from the list. Also, if you really want to do
this in the database, you'd better post what DBMS you are using, since I
think you're going to have to use some of the procedural enhancements that
your DBMS provides (T-SQL, pl-sql etc)

Cheers
Ken




_____________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet.
_____________________________________________________________________


_____________________________________________________________________
Kingfield Heath Ltd. Email Disclaimer

Confidentiality : This email and its attachments are intended for the
above-named only and may be confidential. If they have come to you in
error you must take no action based on them, nor must you copy or
show them to anyone; please reply to this email and highlight the
error.

Security Warning : Please note that this email has been created in
the knowledge that the internet is not a 100% secure communications
medium. We advise that you understand and observe this lack of
security when emailing us.

Viruses : Although we have taken steps to ensure that this email and
attachments are free from any virus, we advise that, in keeping with
good computing practice, the recipient should ensure they are
actually virus free.
_____________________________________________________________________

So you really want to see if the /last/ three assessments for each company
were bronze (ie you don't want a situation where you are continually
generating warning letters every time you run this query, for a company that
received three bronze assessments in a row 2 years ago, but has been
achieving silver/gold ever since).

That makes it easier to do, since you can limit the set to the last three
assessments, then test to see if all those assessments where bronze (ie over
20 points). Something like (there is a probably a more efficient way to
write this, but it does seem to work!)

SELECT -- Get the company's ID
    CompanyID
FROM
    Assessments a
WHERE
    AssessmentID IN
    (
        SELECT -- Select those assessments where the Score was over 20
            AssessmentID
        FROM
            Assessments b
        WHERE
            a.CompanyID = b.CompanyID
        AND
            b.Score > 20
        AND AssessmentID IN
        (
            SELECT -- Get Top 3 Assessments for the given company
                TOP 3 AssessmentID
            FROM
                Assessments c
            WHERE
                c.CompanyID = b.CompanyID
        )
    )
GROUP BY
    a.CompanyID
HAVING
    COUNT(a.CompanyID) > 2
ORDER BY
    CompanyID

You might want to ask on one of the SQL lists, as someone with a better
knowledge of SQL will probably be able to optimise it.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Craig Flannigan" <ckf@k...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, August 07, 2002 6:31 PM
Subject: [asp_databases] Re: SQL problem


: Hi Ken,
:
: Thanks for your reply.
:
: You're right, I did mean consecutive, rather than concurrent!
:
: Basically the assessments are performed as and when a supplier arrives at
: our warehouse. This could be weekly, or monthly. There are no set dates,
so
: really it would be to show 3 consecutive bronze assessments.
:
: The Bronze assessments are a warning to a supplier that they are not
: performing to our standards. 3 bronze assessments triggers a warning
letter,
: 5 kicks them out altogether.
:
: This is the reason I've been asked to find suppliers with 3 consecutive
: bronze assessments.
:
: I assumed this would be too much strain on a web server having to work out
: consecutive assessments for 800 suppliers.
:
: Regards
: Craig.
:
:
: -----Original Message-----
: From: Ken Schaefer [mailto:ken@a...]
: Sent: 07 August 2002 08:59
: To: ASP Databases
: Subject: [asp_databases] Re: SQL problem
:
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: "Craig Flannigan" <ckf@k...>
: Subject: [asp_databases] SQL problem
:
:
: : I am trying to work out how to perform a query to extract 3 records from
a
: : large DB but where the records are concurrent.
: :
: : Basically, we have a system that assesses our suppliers. Each assessment
: is
: : stored in a table called Assessments. They are then awarded a Gold,
Silver
: : or Bronze status depending on how well they did. The lower the score the
: : better.
: :
: : I've now been asked to provide a report to show all Suppliers who have
had
: 3
: : concurrent bronze (denoted by a score of over 20) assessments.
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:
: This is probably something that you might want to look at doing in your
: business layer. SQL is inherently unsuited to this type of query (as you
can
: tell by the difficulty you are having in developing a query to do what you
: want!), since SQL is a set-based languages, and sets are inherently
: unordered.
:
: Do you want to get those companies that have scored three concurrent
bronzes
: in the last three periods? Or any three consecutive periods? (I think you
: meant "consecutive", not "concurrent"). And how do you want to deal with
the
: situation where a company has had more than one set of three consecutive
: bronzes eg:
:
: B B B S B B B
:
: Also, what if the company had 5 consecutive bronzes? How many times does
: this count? (Period 1 + Period 2 + Period 3, Period 2 + Period 3 + Period
4,
: Period 3 + Period 4 + Period 5)?
:
: etc. Without giving a complete case statement, you are going to have
: difficulty getting an answer from the list. Also, if you really want to do
: this in the database, you'd better post what DBMS you are using, since I
: think you're going to have to use some of the procedural enhancements that
: your DBMS provides (T-SQL, pl-sql etc)
:
: Cheers
: Ken

Hi Ken,

Basically I've been asked to "Show the Suppliers who have scored 3
consecutive 'Bronze' assessments"

They haven't specified anything else, but I'd say that it basically would be
the last 3 assessments. I'd append a flag to each assessment to state that a
warning has gone out, so not to send another.

I will try your SQL - thanks for your help so far.


Regards
Craig.


-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: 08 August 2002 02:43
To: ASP Databases
Subject: [asp_databases] Re: SQL problem


So you really want to see if the /last/ three assessments for each company
were bronze (ie you don't want a situation where you are continually
generating warning letters every time you run this query, for a company that
received three bronze assessments in a row 2 years ago, but has been
achieving silver/gold ever since).

That makes it easier to do, since you can limit the set to the last three
assessments, then test to see if all those assessments where bronze (ie over
20 points). Something like (there is a probably a more efficient way to
write this, but it does seem to work!)

SELECT -- Get the company's ID
    CompanyID
FROM
    Assessments a
WHERE
    AssessmentID IN
    (
        SELECT -- Select those assessments where the Score was over 20
            AssessmentID
        FROM
            Assessments b
        WHERE
            a.CompanyID = b.CompanyID
        AND
            b.Score > 20
        AND AssessmentID IN
        (
            SELECT -- Get Top 3 Assessments for the given company
                TOP 3 AssessmentID
            FROM
                Assessments c
            WHERE
                c.CompanyID = b.CompanyID
        )
    )
GROUP BY
    a.CompanyID
HAVING
    COUNT(a.CompanyID) > 2
ORDER BY
    CompanyID

You might want to ask on one of the SQL lists, as someone with a better
knowledge of SQL will probably be able to optimise it.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Craig Flannigan" <ckf@k...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, August 07, 2002 6:31 PM
Subject: [asp_databases] Re: SQL problem


: Hi Ken,
:
: Thanks for your reply.
:
: You're right, I did mean consecutive, rather than concurrent!
:
: Basically the assessments are performed as and when a supplier arrives at
: our warehouse. This could be weekly, or monthly. There are no set dates,
so
: really it would be to show 3 consecutive bronze assessments.
:
: The Bronze assessments are a warning to a supplier that they are not
: performing to our standards. 3 bronze assessments triggers a warning
letter,
: 5 kicks them out altogether.
:
: This is the reason I've been asked to find suppliers with 3 consecutive
: bronze assessments.
:
: I assumed this would be too much strain on a web server having to work out
: consecutive assessments for 800 suppliers.
:
: Regards
: Craig.
:
:
: -----Original Message-----
: From: Ken Schaefer [mailto:ken@a...]
: Sent: 07 August 2002 08:59
: To: ASP Databases
: Subject: [asp_databases] Re: SQL problem
:
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: "Craig Flannigan" <ckf@k...>
: Subject: [asp_databases] SQL problem
:
:
: : I am trying to work out how to perform a query to extract 3 records from
a
: : large DB but where the records are concurrent.
: :
: : Basically, we have a system that assesses our suppliers. Each assessment
: is
: : stored in a table called Assessments. They are then awarded a Gold,
Silver
: : or Bronze status depending on how well they did. The lower the score the
: : better.
: :
: : I've now been asked to provide a report to show all Suppliers who have
had
: 3
: : concurrent bronze (denoted by a score of over 20) assessments.
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:
: This is probably something that you might want to look at doing in your
: business layer. SQL is inherently unsuited to this type of query (as you
can
: tell by the difficulty you are having in developing a query to do what you
: want!), since SQL is a set-based languages, and sets are inherently
: unordered.
:
: Do you want to get those companies that have scored three concurrent
bronzes
: in the last three periods? Or any three consecutive periods? (I think you
: meant "consecutive", not "concurrent"). And how do you want to deal with
the
: situation where a company has had more than one set of three consecutive
: bronzes eg:
:
: B B B S B B B
:
: Also, what if the company had 5 consecutive bronzes? How many times does
: this count? (Period 1 + Period 2 + Period 3, Period 2 + Period 3 + Period
4,
: Period 3 + Period 4 + Period 5)?
:
: etc. Without giving a complete case statement, you are going to have
: difficulty getting an answer from the list. Also, if you really want to do
: this in the database, you'd better post what DBMS you are using, since I
: think you're going to have to use some of the procedural enhancements that
: your DBMS provides (T-SQL, pl-sql etc)
:
: Cheers
: Ken




_____________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet.
_____________________________________________________________________


_____________________________________________________________________
Kingfield Heath Ltd. Email Disclaimer

Confidentiality : This email and its attachments are intended for the
above-named only and may be confidential. If they have come to you in
error you must take no action based on them, nor must you copy or
show them to anyone; please reply to this email and highlight the
error.

Security Warning : Please note that this email has been created in
the knowledge that the internet is not a 100% secure communications
medium. We advise that you understand and observe this lack of
security when emailing us.

Viruses : Although we have taken steps to ensure that this email and
attachments are free from any virus, we advise that, in keeping with
good computing practice, the recipient should ensure they are
actually virus free.
_____________________________________________________________________

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Craig Flannigan" <ckf@k...>
Subject: [asp_databases] Re: SQL problem


: Basically I've been asked to "Show the Suppliers who have scored 3
: consecutive 'Bronze' assessments"
:
: They haven't specified anything else, but I'd say that it basically would
be
: the last 3 assessments. I'd append a flag to each assessment to state that
a
: warning has gone out, so not to send another.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

:
: I will try your SQL - thanks for your help so far.
:
:
: Regards
: Craig.
:
:
: -----Original Message-----
: From: Ken Schaefer [mailto:ken@a...]
: Sent: 08 August 2002 02:43
: To: ASP Databases
: Subject: [asp_databases] Re: SQL problem
:
:
: So you really want to see if the /last/ three assessments for each company
: were bronze (ie you don't want a situation where you are continually
: generating warning letters every time you run this query, for a company
that
: received three bronze assessments in a row 2 years ago, but has been
: achieving silver/gold ever since).
:
: That makes it easier to do, since you can limit the set to the last three
: assessments, then test to see if all those assessments where bronze (ie
over
: 20 points). Something like (there is a probably a more efficient way to
: write this, but it does seem to work!)
:
: SELECT -- Get the company's ID
:     CompanyID
: FROM
:     Assessments a
: WHERE
:     AssessmentID IN
:     (
:         SELECT -- Select those assessments where the Score was over 20
:             AssessmentID
:         FROM
:             Assessments b
:         WHERE
:             a.CompanyID = b.CompanyID
:         AND
:             b.Score > 20
:         AND AssessmentID IN
:         (
:             SELECT -- Get Top 3 Assessments for the given company
:                 TOP 3 AssessmentID
:             FROM
:                 Assessments c
:             WHERE
:                 c.CompanyID = b.CompanyID
:         )
:     )
: GROUP BY
:     a.CompanyID
: HAVING
:     COUNT(a.CompanyID) > 2
: ORDER BY
:     CompanyID
:
: You might want to ask on one of the SQL lists, as someone with a better
: knowledge of SQL will probably be able to optimise it.
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: "Craig Flannigan" <ckf@k...>
: To: "ASP Databases" <asp_databases@p...>
: Sent: Wednesday, August 07, 2002 6:31 PM
: Subject: [asp_databases] Re: SQL problem
:
:
: : Hi Ken,
: :
: : Thanks for your reply.
: :
: : You're right, I did mean consecutive, rather than concurrent!
: :
: : Basically the assessments are performed as and when a supplier arrives
at
: : our warehouse. This could be weekly, or monthly. There are no set dates,
: so
: : really it would be to show 3 consecutive bronze assessments.
: :
: : The Bronze assessments are a warning to a supplier that they are not
: : performing to our standards. 3 bronze assessments triggers a warning
: letter,
: : 5 kicks them out altogether.
: :
: : This is the reason I've been asked to find suppliers with 3 consecutive
: : bronze assessments.
: :
: : I assumed this would be too much strain on a web server having to work
out
: : consecutive assessments for 800 suppliers.
: :
: : Regards
: : Craig.
: :
: :
: : -----Original Message-----
: : From: Ken Schaefer [mailto:ken@a...]
: : Sent: 07 August 2002 08:59
: : To: ASP Databases
: : Subject: [asp_databases] Re: SQL problem
: :
: :
: : ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: : From: "Craig Flannigan" <ckf@k...>
: : Subject: [asp_databases] SQL problem
: :
: :
: : : I am trying to work out how to perform a query to extract 3 records
from
: a
: : : large DB but where the records are concurrent.
: : :
: : : Basically, we have a system that assesses our suppliers. Each
assessment
: : is
: : : stored in a table called Assessments. They are then awarded a Gold,
: Silver
: : : or Bronze status depending on how well they did. The lower the score
the
: : : better.
: : :
: : : I've now been asked to provide a report to show all Suppliers who have
: had
: : 3
: : : concurrent bronze (denoted by a score of over 20) assessments.
: :
: : ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: :
: : This is probably something that you might want to look at doing in your
: : business layer. SQL is inherently unsuited to this type of query (as you
: can
: : tell by the difficulty you are having in developing a query to do what
you
: : want!), since SQL is a set-based languages, and sets are inherently
: : unordered.
: :
: : Do you want to get those companies that have scored three concurrent
: bronzes
: : in the last three periods? Or any three consecutive periods? (I think
you
: : meant "consecutive", not "concurrent"). And how do you want to deal with
: the
: : situation where a company has had more than one set of three consecutive
: : bronzes eg:
: :
: : B B B S B B B
: :
: : Also, what if the company had 5 consecutive bronzes? How many times does
: : this count? (Period 1 + Period 2 + Period 3, Period 2 + Period 3 +
Period
: 4,
: : Period 3 + Period 4 + Period 5)?
: :
: : etc. Without giving a complete case statement, you are going to have
: : difficulty getting an answer from the list. Also, if you really want to
do
: : this in the database, you'd better post what DBMS you are using, since I
: : think you're going to have to use some of the procedural enhancements
that
: : your DBMS provides (T-SQL, pl-sql etc)
: :
: : Cheers
: : Ken
:
:
:
:
: _____________________________________________________________________
: This e-mail has been scanned for all viruses by Star Internet.
: _____________________________________________________________________
:
:
: _____________________________________________________________________
: Kingfield Heath Ltd. Email Disclaimer
:
: Confidentiality : This email and its attachments are intended for the
: above-named only and may be confidential. If they have come to you in
: error you must take no action based on them, nor must you copy or
: show them to anyone; please reply to this email and highlight the
: error.
:
: Security Warning : Please note that this email has been created in
: the knowledge that the internet is not a 100% secure communications
: medium. We advise that you understand and observe this lack of
: security when emailing us.
:
: Viruses : Although we have taken steps to ensure that this email and
: attachments are free from any virus, we advise that, in keeping with
: good computing practice, the recipient should ensure they are
: actually virus free.
: _____________________________________________________________________
:

oop, I'll try again...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Craig Flannigan" <ckf@k...>
Subject: [asp_databases] Re: SQL problem


: Basically I've been asked to "Show the Suppliers who have scored 3
: consecutive 'Bronze' assessments"
:
: They haven't specified anything else, but I'd say that it basically would
be
: the last 3 assessments. I'd append a flag to each assessment to state that
a
: warning has gone out, so not to send another.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

But the assessments are stored on an assessment-by-assessment basis - how
are you going to know which records to flag?

If you do need only the last three assessments to be used in calculating
whether to generate a message, the SQL posted should be sufficient...

Cheers
Ken

Good point.

I'll try the SQL now.

Thanks.


-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: 08 August 2002 08:38
To: ASP Databases
Subject: [asp_databases] Re: SQL problem


oop, I'll try again...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Craig Flannigan" <ckf@k...>
Subject: [asp_databases] Re: SQL problem


: Basically I've been asked to "Show the Suppliers who have scored 3
: consecutive 'Bronze' assessments"
:
: They haven't specified anything else, but I'd say that it basically would
be
: the last 3 assessments. I'd append a flag to each assessment to state that
a
: warning has gone out, so not to send another.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

But the assessments are stored on an assessment-by-assessment basis - how
are you going to know which records to flag?

If you do need only the last three assessments to be used in calculating
whether to generate a message, the SQL posted should be sufficient...

Cheers
Ken



_____________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet.
_____________________________________________________________________


_____________________________________________________________________
Kingfield Heath Ltd. Email Disclaimer

Confidentiality : This email and its attachments are intended for the
above-named only and may be confidential. If they have come to you in
error you must take no action based on them, nor must you copy or
show them to anyone; please reply to this email and highlight the
error.

Security Warning : Please note that this email has been created in
the knowledge that the internet is not a 100% secure communications
medium. We advise that you understand and observe this lack of
security when emailing us.

Viruses : Although we have taken steps to ensure that this email and
attachments are free from any virus, we advise that, in keeping with
good computing practice, the recipient should ensure they are
actually virus free.
_____________________________________________________________________

By the way, does it matter that I'm only using Access for this?!  (Not my
choice!)

(takes cover ready for a barrage of insults)

SQL Dummy question:
I am also using two tables, Supplier and Assessments. Why is there
'Assessment b' and 'Assessment c'?

Regards.
Craig.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT -- Get the company's ID
    CompanyID
FROM
    Assessments a
WHERE
    AssessmentID IN
    (
        SELECT -- Select those assessments where the Score was over 20
            AssessmentID
        FROM
            Assessments b
        WHERE
            a.CompanyID = b.CompanyID
        AND
            b.Score > 20
        AND AssessmentID IN
        (
            SELECT -- Get Top 3 Assessments for the given company
                TOP 3 AssessmentID
            FROM
                Assessments c
            WHERE
                c.CompanyID = b.CompanyID
        )
    )
GROUP BY
    a.CompanyID
HAVING
    COUNT(a.CompanyID) > 2
ORDER BY
    CompanyID

You might want to ask on one of the SQL lists, as someone with a better
knowledge of SQL will probably be able to optimise it.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Craig Flannigan" <ckf@k...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, August 07, 2002 6:31 PM
Subject: [asp_databases] Re: SQL problem


: Hi Ken,
:
: Thanks for your reply.
:
: You're right, I did mean consecutive, rather than concurrent!
:
: Basically the assessments are performed as and when a supplier arrives at
: our warehouse. This could be weekly, or monthly. There are no set dates,
so
: really it would be to show 3 consecutive bronze assessments.
:
: The Bronze assessments are a warning to a supplier that they are not
: performing to our standards. 3 bronze assessments triggers a warning
letter,
: 5 kicks them out altogether.
:
: This is the reason I've been asked to find suppliers with 3 consecutive
: bronze assessments.
:
: I assumed this would be too much strain on a web server having to work out
: consecutive assessments for 800 suppliers.
:
: Regards
: Craig.
:
:
: -----Original Message-----
: From: Ken Schaefer [mailto:ken@a...]
: Sent: 07 August 2002 08:59
: To: ASP Databases
: Subject: [asp_databases] Re: SQL problem
:
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: "Craig Flannigan" <ckf@k...>
: Subject: [asp_databases] SQL problem
:
:
: : I am trying to work out how to perform a query to extract 3 records from
a
: : large DB but where the records are concurrent.
: :
: : Basically, we have a system that assesses our suppliers. Each assessment
: is
: : stored in a table called Assessments. They are then awarded a Gold,
Silver
: : or Bronze status depending on how well they did. The lower the score the
: : better.
: :
: : I've now been asked to provide a report to show all Suppliers who have
had
: 3
: : concurrent bronze (denoted by a score of over 20) assessments.
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:
: This is probably something that you might want to look at doing in your
: business layer. SQL is inherently unsuited to this type of query (as you
can
: tell by the difficulty you are having in developing a query to do what you
: want!), since SQL is a set-based languages, and sets are inherently
: unordered.
:
: Do you want to get those companies that have scored three concurrent
bronzes
: in the last three periods? Or any three consecutive periods? (I think you
: meant "consecutive", not "concurrent"). And how do you want to deal with
the
: situation where a company has had more than one set of three consecutive
: bronzes eg:
:
: B B B S B B B
:
: Also, what if the company had 5 consecutive bronzes? How many times does
: this count? (Period 1 + Period 2 + Period 3, Period 2 + Period 3 + Period
4,
: Period 3 + Period 4 + Period 5)?
:
: etc. Without giving a complete case statement, you are going to have
: difficulty getting an answer from the list. Also, if you really want to do
: this in the database, you'd better post what DBMS you are using, since I
: think you're going to have to use some of the procedural enhancements that
: your DBMS provides (T-SQL, pl-sql etc)
:
: Cheers
: Ken




_____________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet.
_____________________________________________________________________


_____________________________________________________________________
Kingfield Heath Ltd. Email Disclaimer

Confidentiality : This email and its attachments are intended for the
above-named only and may be confidential. If they have come to you in
error you must take no action based on them, nor must you copy or
show them to anyone; please reply to this email and highlight the
error.

Security Warning : Please note that this email has been created in
the knowledge that the internet is not a 100% secure communications
medium. We advise that you understand and observe this lack of
security when emailing us.

Viruses : Although we have taken steps to ensure that this email and
attachments are free from any virus, we advise that, in keeping with
good computing practice, the recipient should ensure they are
actually virus free.
_____________________________________________________________________

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Craig Flannigan" <ckf@k...>
Subject: [asp_databases] Re: SQL problem


: By the way, does it matter that I'm only using Access for this?!  (Not my
: choice!)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I don't think the SQL will bomb (you may need to put some extra paranthesis
in though) - however, Access has no query optimiser built in. Beyond
constructing indexes there is nothing you, or the DB, can do to optimise the
generation of results. With correlated queries like the one below, Access'
performance can be dismal - I've seen queries on 30,000 records take nearly
60 minutes to run that would take 20-30 seconds in SQL Server.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: SQL Dummy question:
: I am also using two tables, Supplier and Assessments. Why is there
: 'Assessment b' and 'Assessment c'?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The tables are aliased to themselves. So, in the outer query we are getting
the CompanyID from the Assessment's table but limiting the AssessmentIDs to
those returned by the inner query. The inner query is also running on the
Assessments table, so we need to give it a different alias...

Cheers
Ken