Solved

Cursor 101- all my points

Posted on 2003-10-21
20
201 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 69

Expert Comment

by:ScottPletcher
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 69

Expert Comment

by:ScottPletcher
ID: 9591523
OOPS, CORRECTIONS NEEDED, in progress now ...
0
 
LVL 69

Expert Comment

by:ScottPletcher
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 69

Expert Comment

by:ScottPletcher
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 69

Expert Comment

by:ScottPletcher
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 69

Expert Comment

by:ScottPletcher
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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 69

Expert Comment

by:ScottPletcher
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 69

Expert Comment

by:ScottPletcher
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 69

Expert Comment

by:ScottPletcher
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 69

Accepted Solution

by:
ScottPletcher earned 90 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now