Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Cursor 101- all my points

Posted on 2003-10-21
20
Medium Priority
?
231 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:owlsey
  • 10
  • 9
20 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 9591472
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9591486
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9591523
OOPS, CORRECTIONS NEEDED, in progress now ...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9591568
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
 

Author Comment

by:owlsey
ID: 9591633
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9591652
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
 

Author Comment

by:owlsey
ID: 9591732
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9591733
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
 

Author Comment

by:owlsey
ID: 9591861
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9592052
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
 

Author Comment

by:owlsey
ID: 9592484
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
 

Author Comment

by:owlsey
ID: 9592648
BTW- I try a couple differnet iterations of this query before i post a q- just not a SQL guy...
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9592716
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9592721
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
 

Author Comment

by:owlsey
ID: 9592991
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9593020
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
 

Author Comment

by:owlsey
ID: 9593350
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 360 total points
ID: 9593512
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
 

Author Comment

by:owlsey
ID: 9595861
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
 

Author Comment

by:owlsey
ID: 9595920
thanks Scott- im awed by your skill

(a cursor would have been cool tho-heh)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question