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?
 
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
0
 
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

0
 
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PletcherSenior DBACommented:
OOPS, CORRECTIONS NEEDED, in progress now ...
0
 
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
0
 
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
0
 
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
0
 
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
0
 
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
0
 
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-
0
 
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
0
 
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
0
 
owlseyAuthor Commented:
BTW- I try a couple differnet iterations of this query before i post a q- just not a SQL guy...
0
 
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
0
 
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
0
 
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
0
 
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
0
 
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
0
 
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
0
 
owlseyAuthor Commented:
thanks Scott- im awed by your skill

(a cursor would have been cool tho-heh)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.