Cursor 101- all my points

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

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.

OOPS, CORRECTIONS NEEDED, in progress now ...

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

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

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

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

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

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-

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

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

BTW- I try a couple differnet iterations of this query before i post a q- just not a SQL guy...

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

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

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

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

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

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

thanks Scott- im awed by your skill

(a cursor would have been cool tho-heh)