Cursor 101- all my points

ok-

1 have 2 tables, scores & scorelements, used to track training.  Scores has unlimited user records for each course, per 'comp_id'- ex- chapter 1 quiz.  the 'comp_id' vary per course, but all i need is to check that each comp_id is completed per course.

tbl Scores
usr_id,crs_id,comp_id,score,date
usr1,crs1,comp1,100,10.1.03
usr1,crs1,comp2,100,10.1.03
usr1,crs1,comp3,100,10.1.03
usr2,crs1,comp1,100,10.1.03
usr2,crs1,comp1,100,10.1.03
usr2,crs1,comp1,100,10.1.03

tbl ScoreElements
crs_id,comp_id,type
crs1,comp1,13
crs1,comp2,13
crs1,comp3,13
crs2,comp1,13....etc

ok- so usr2 shouldnt be returned becuase he has records for only for compid1- usr1 should be returned because he has records for all ids for crs1.  this is why i think i need a cursor- but im new to them.

also, count(*) wont work because usr can have multiple records for each comp_id, users can retake the course as many times as needed.

help?

ideal return would be (based on example above):
usrid,crsid,score,date,completed?
usr1,crs1,avg(Scores.score),Last(Scores.date),Y

thanks,
duncan
owlseyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HilaireCommented:
Hi duncan

need clarification on the following :

How can we know how many comps in a course to decide whether course is completed or not ?
This has to be stored in a table
like course_content, or else ??


Cheers

Hilaire

Scott PletcherSenior DBACommented:
I think you can use COUNT as long as you GROUP BY properly.  Here is an example; haven't tested it yet but will do so ASAP:


SELECT usr_id, crs_id, COUNT(*) AS uniqCompCount
FROM Scores
INNER JOIN (
      SELECT crs_id, comp_id, COUNT(*) AS uniqCompCount
      FROM ScoreElements
      GROUP BY crs_id, comp_id
) AS se ON Scores.crs_id = se.crs_id AND Scores.comp_id = se.comp_id AND
      Scores.uniqCompCount = se.uniqCompCount      
GROUP BY usr_id, crs_id, comp_id


Btw, you should avoid cursor(s) if possible because of the performance disadvantage.
Scott PletcherSenior DBACommented:
OOPS, CORRECTIONS NEEDED, in progress now ...
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Scott PletcherSenior DBACommented:
OK, please try this version:


SELECT S.usr_id, S.crs_id
FROM (
      SELECT usr_id, crs_id, comp_id, COUNT(*) AS uniqCompCount
      FROM Scores
      GROUP BY usr_id, crs_id, comp_id
) AS S
INNER JOIN (
    SELECT crs_id, comp_id, COUNT(*) AS uniqCompCount
    FROM ScoreElements
    GROUP BY crs_id, comp_id
) AS SE ON S.crs_id = SE.crs_id AND S.comp_id = SE.comp_id AND
    S.uniqCompCount = SE.uniqCompCount
owlseyAuthor Commented:
Hilaire-

the course/comp id definition is stored in scoreElements; but the format & number per course vary per course.

Scott-

Thanks- ill try this now

thanks to both for your quick response- ive been trying this for weeks now

thanks,
duncan
Scott PletcherSenior DBACommented:
OOPS, just noticed something, one small but important change needs made, specifically "S.uniqCompCount = SE.uniqCompCount" should be "    S.uniqCompCount >= SE.uniqCompCount" to allow for the course being taken multiple times:


SELECT S.usr_id, S.crs_id
FROM (
     SELECT usr_id, crs_id, comp_id, COUNT(*) AS uniqCompCount
     FROM Scores
     GROUP BY usr_id, crs_id, comp_id
) AS S
INNER JOIN (
    SELECT crs_id, comp_id, COUNT(*) AS uniqCompCount
    FROM ScoreElements
    GROUP BY crs_id, comp_id
) AS SE ON S.crs_id = SE.crs_id AND S.comp_id = SE.comp_id AND
    S.uniqCompCount >= SE.uniqCompCount
owlseyAuthor Commented:
ok-ran this:
SELECT S.USERLOGONID, S.COURSENUMBER
FROM (SELECT USERLOGONID, COURSENUMBER, COMPONENTID, COUNT(*) AS uniqCompCount
            FROM Scores
            GROUP BY USERLOGONID, COURSENUMBER, COMPONENTID
           ) AS S
INNER JOIN
         (SELECT COURSENUMBER, COMPONENTID, COUNT(*) AS uniqCompCount
          FROM ScoreElements
          GROUP BY COURSENUMBER, COMPONENTID
          )
AS SE ON S.COURSENUMBER = SE.COURSENUMBER
AND S.COMPONENTID = SE.COMPONENTID
AND S.uniqCompCount = SE.uniqCompCount

got 5407 rows- should be round 1500

i noticed several rows for a usr with the same usr/crs# combo- so i think this is an err

thanks,
duncan
Scott PletcherSenior DBACommented:
DOUBLE D'OH!  Overlooked something else.  I think (and hope and pray) that I have it this time.  On the good side, this code has fewer columns and is somewhat easier to understand.  Although, if you're not familiar with derived tables, that is using a SELECT in a FROM or INNER JOIN, you might want to look that up in Books Online and/or play around with them in Query Analyzer.  Essentially they just allow you to dynamically build and use a "subtable" or work table from within a query.


SELECT S.usr_id, S.crs_id
FROM (
      SELECT usr_id, crs_id, COUNT(DISTINCT comp_id) AS distinctCompCount
      FROM Scores
      GROUP BY usr_id, crs_id
) AS S
INNER JOIN (
    SELECT crs_id, COUNT(*) AS distinctCompCount
    FROM ScoreElements
    GROUP BY crs_id
) AS SE ON S.crs_id = SE.crs_id AND S.distinctCompCount >= SE.distinctCompCount
owlseyAuthor Commented:
ok- couple of points, but this looks better than what ive been able to do on my own(thanks)

1- how can i retrieve & avg(scores) for this?
2- how can i get the last date on scores?
3- how can i get all comp_id's in scoreelements why coursetype = "13"?

thanks,
duncan

this thats it-
Scott PletcherSenior DBACommented:
Sorry, overlooked that bit in your first post (:blushing:)
Maybe something like below.  As coded, this averages all scores for a COURSE, NOT per comp_id.  Similarly, it is the less date per COURSE, NOT per comp_id.  If you need them per comp_id, you can add another derived table and inner join to it (not sure right now if there is a faster way).  Gotta' go to lunch right now, but will check in again after lunch.


SELECT S.usr_id, S.crs_id, S.maxDate, S.avgScore
FROM (
      SELECT usr_id, crs_id, MAX(date) AS maxDate,
            AVG(score) AS avgScore, COUNT(DISTINCT comp_id) AS distinctCompCount
      FROM Scores
      GROUP BY usr_id, crs_id
) AS S
INNER JOIN (
    SELECT crs_id, COUNT(*) AS distinctCompCount
    FROM ScoreElements
      WHERE type = 13  -- or '13', if non-numeric
    GROUP BY crs_id
) AS SE ON S.crs_id = SE.crs_id AND S.distinctCompCount >= SE.distinctCompCount
owlseyAuthor Commented:
thanks- think this is it, but i got some wierd results..

how can you exclude comp_ids in scoreelements of '00000000' & "COMPLETE"(user defined & therefore not accurate)

SELECT S.USERLOGONID, S.COURSENUMBER, S.maxDate, S.avgScore
FROM (
     SELECT USERLOGONID, COURSENUMBER, MAX(DATE_TIME) AS maxDate,
          AVG(SCORE) AS avgScore, COUNT(DISTINCT COMPONENTID) AS distinctCompCount
     FROM Scores
     GROUP BY USERLOGONID, COURSENUMBER
) AS S
INNER JOIN (
    SELECT COURSENUMBER, COUNT(*) AS distinctCompCount
    FROM ScoreElements
    WHERE COURSETYPE = 13
   <B> AND COMPONENTID != "00000000"
    AND COMPONENTID != "COMPLETE"</B>
    GROUP BY COURSENUMBER
    ) AS SE
ON S.COURSENUMBER = SE.COURSENUMBER
AND S.distinctCompCount >= SE.distinctCompCount
owlseyAuthor Commented:
BTW- I try a couple differnet iterations of this query before i post a q- just not a SQL guy...
Scott PletcherSenior DBACommented:
That should do it really (minus the tags, of course :-)).  Was there a problem with this query?:


SELECT S.USERLOGONID, S.COURSENUMBER, S.maxDate, S.avgScore
FROM (
     SELECT USERLOGONID, COURSENUMBER, MAX(DATE_TIME) AS maxDate,
          AVG(SCORE) AS avgScore, COUNT(DISTINCT COMPONENTID) AS distinctCompCount
     FROM Scores
     GROUP BY USERLOGONID, COURSENUMBER
) AS S
INNER JOIN (
    SELECT COURSENUMBER, COUNT(*) AS distinctCompCount
    FROM ScoreElements
    WHERE COURSETYPE = 13
    AND COMPONENTID != "00000000"
    AND COMPONENTID != "COMPLETE"
    GROUP BY COURSENUMBER
    ) AS SE
ON S.COURSENUMBER = SE.COURSENUMBER
AND S.distinctCompCount >= SE.distinctCompCount
Scott PletcherSenior DBACommented:
That should do it really (minus the tags, of course :-)).  Was there a problem with this query?:


SELECT S.USERLOGONID, S.COURSENUMBER, S.maxDate, S.avgScore
FROM (
     SELECT USERLOGONID, COURSENUMBER, MAX(DATE_TIME) AS maxDate,
          AVG(SCORE) AS avgScore, COUNT(DISTINCT COMPONENTID) AS distinctCompCount
     FROM Scores
     GROUP BY USERLOGONID, COURSENUMBER
) AS S
INNER JOIN (
    SELECT COURSENUMBER, COUNT(*) AS distinctCompCount
    FROM ScoreElements
    WHERE COURSETYPE = 13
    AND COMPONENTID != "00000000"
    AND COMPONENTID != "COMPLETE"
    GROUP BY COURSENUMBER
    ) AS SE
ON S.COURSENUMBER = SE.COURSENUMBER
AND S.distinctCompCount >= SE.distinctCompCount
owlseyAuthor Commented:
both s.compid & se.compid can have '0x8"; s can also have 'complete'

Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'AND'.
Server: Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'AS'.

---------------------------------------------------------------
SELECT
   S.USERLOGONID, S.COURSENUMBER, S.maxDate, S.avgScore
FROM
  (
      SELECT USERLOGONID, COURSENUMBER, MAX(DATE_TIME) AS maxDate,AVG(SCORE) AS avgScore, COUNT(DISTINCT COMPONENTID) AS distinctCompCount
      FROM Scores
      AND COMPONENTID != "00000000"
      OR COMPONENTID != "COMPLETE"
      GROUP BY USERLOGONID, COURSENUMBER
   )
AS S
INNER JOIN (
      SELECT COURSENUMBER, COUNT(*) AS distinctCompCount
      FROM ScoreElements
      WHERE COURSETYPE = 13
      AND COMPONENTID != "00000000"
      GROUP BY COURSENUMBER
         )
AS SE
ON S.COURSENUMBER = SE.COURSENUMBER
AND S.distinctCompCount >= SE.distinctCompCount
Scott PletcherSenior DBACommented:
The first "AND" should be a WHERE (sorry, I was up too late last night, I think I need a nap :-) ).


SELECT
   S.USERLOGONID, S.COURSENUMBER, S.maxDate, S.avgScore
FROM
  (
     SELECT USERLOGONID, COURSENUMBER, MAX(DATE_TIME) AS maxDate,AVG(SCORE) AS avgScore, COUNT(DISTINCT COMPONENTID) AS distinctCompCount
     FROM Scores
     WHERE COMPONENTID != "00000000"  -- **changed
     OR COMPONENTID != "COMPLETE"
     GROUP BY USERLOGONID, COURSENUMBER
   )
AS S
INNER JOIN (
     SELECT COURSENUMBER, COUNT(*) AS distinctCompCount
     FROM ScoreElements
     WHERE COURSETYPE = 13
     AND COMPONENTID != "00000000"
     GROUP BY COURSENUMBER
        )
AS SE
ON S.COURSENUMBER = SE.COURSENUMBER
AND S.distinctCompCount >= SE.distinctCompCount
owlseyAuthor Commented:
SELECT
   S.USERLOGONID, S.COURSENUMBER, S.maxDate, S.avgScore
FROM
  (
     SELECT USERLOGONID, COURSENUMBER, MAX(DATE_TIME) AS maxDate,AVG(SCORE) AS avgScore, COUNT(DISTINCT COMPONENTID) AS distinctCompCount
     FROM Scores
     WHERE COMPONENTID != "00000000"
     OR COMPONENTID != "COMPLETE"
     GROUP BY USERLOGONID, COURSENUMBER
   )
AS S
INNER JOIN (
     SELECT COURSENUMBER, COUNT(*) AS distinctCompCount
     FROM ScoreElements
     WHERE COURSETYPE = 13
     AND COMPONENTID != "00000000"
     GROUP BY COURSENUMBER
        )
AS SE
ON S.COURSENUMBER = SE.COURSENUMBER
AND S.distinctCompCount >= SE.distinctCompCount

------------------------------------------------------------------------

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name '00000000'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'COMPLETE'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '00000000'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'COMPLETE'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '00000000'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'COMPLETE'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '00000000'.

ack- im researching this now, too-
duncan
Scott PletcherSenior DBACommented:
Always use single quotes in SQL Server ('), not double quotes ("), around literal values.


SELECT
   S.USERLOGONID, S.COURSENUMBER, S.maxDate, S.avgScore
FROM
  (
     SELECT USERLOGONID, COURSENUMBER, MAX(DATE_TIME) AS maxDate,AVG(SCORE) AS avgScore, COUNT(DISTINCT COMPONENTID) AS distinctCompCount
     FROM Scores
     WHERE COMPONENTID != '00000000'  -- **changed
     OR COMPONENTID != 'COMPLETE'
     GROUP BY USERLOGONID, COURSENUMBER
   )
AS S
INNER JOIN (
     SELECT COURSENUMBER, COUNT(*) AS distinctCompCount
     FROM ScoreElements
     WHERE COURSETYPE = 13
     AND COMPONENTID != '00000000'
     GROUP BY COURSENUMBER
        )
AS SE
ON S.COURSENUMBER = SE.COURSENUMBER
AND S.distinctCompCount >= SE.distinctCompCount

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
owlseyAuthor Commented:
it seems to work- thanks! i am unsure because an alternate report yields about 300 more records- but that could be due to a faulty qry on their side....

so just to review- this query counts each disinct comp_id per usr/crs- then compares this count to the scoreelements tbl- w/o 0x8 or complete- for all '13' crs types & counts each distinct entry per course- and returns the record id s count is >= se count- i think that makes sense@!

thanks- d
owlseyAuthor Commented:
thanks Scott- im awed by your skill

(a cursor would have been cool tho-heh)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.