Complicated query, Trying to avoid a cursor 2

[pabowen]

I have a database that stores class information. Each class has a different number of sessions, some 1 and some 5.

I need to run a query that shows if all the sessions for a course have been completed, but only if one of the courses falls within the defined time frame.

My (simplified) table structures look like this:
Class Table
UKey Code Year
1 abcd 2005
2 abcd 2005
3 abcd 2005
4 abcd 2004
5 abcd 2004
6 xyz 2005
7 xyz 2005

Status Table
Ukey Status EmployeeID StatusDate
1 C pb001 10/12/2005
2 C pb001 11/15/2005
3 C pb001 6/09/2006
2 C js003 1/34/2005
3 C js003 6/10/2006

If I am looking for completions within the past 30 days, with all statuses of 'C' (no nulls or other values)
I desire to see this:
Ukey Status EmployeeID StatusDate
1 C pb001 10/12/2005
2 C pb001 11/15/2005
3 C pb001 6/09/2006

Note that I need all corresponding records (same course code and year) for that person to be returned, even though only one occured within my window.

Also Note, that I did not return js003 records as she was missing one class.

There is a corresponding employee data table that holds typicall information that can be joined to.

I appreciate everyones help.

Thanks,
Patrick

 

[gmmastros]

Also Note, that I did not return js003 records as she was missing one class.

I don't see how we know that she was missing a class.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[pabowen]

Perhaps I didn't explain this well. The sessions for a class are defined as having the same course code and the same year.

for example:
1 abcd 2005
2 abcd 2005
3 abcd 2005
are all the sessions of the course abcd.

We know that js003 has not completed the classes as she does not have a record entry in the status table for:
1 abcd 2005

 

[gmmastros]

I originally didn't understand that a Course was defined as a unique combination of Code and year. I got it now. thanks.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Let's do this in steps:

First, the dummy data (for testing).

Declare @Class Table(UKey Integer, Code VarChar(10), Year Integer)

Insert Into @Class Values(1,'abcd',2005)
Insert Into @Class Values(2,'abcd',2005)
Insert Into @Class Values(3,'abcd',2005)
Insert Into @Class Values(4,'abcd',2004)
Insert Into @Class Values(5,'abcd',2004)
Insert Into @Class Values(6,'xyz',2005)
Insert Into @Class Values(7,'xyz',2005)

Declare @Status Table(Ukey Integer, Status VarChar(1), EmployeeId VarChar(20), StatusDate DateTime)
Insert Into @Status Values(1,'C','pb001','10/12/2005')
Insert Into @Status Values(2,'C','pb001','11/15/2005')
Insert Into @Status Values(3,'C','pb001','6/09/2006')
Insert Into @Status Values(2,'C','js003','1/3/2005')
Insert Into @Status Values(3,'C','js003','6/10/2006')

Looking at this data, we can immediately begin writing some [small]mildly[/small] interesting queries.

This query should tell us how many sessions each course has.

Select Code, Year, Count(1) As TotalSessions
From   @Class
Group By Code, Year

We can also write a query to tell us how many sessions that an employee attended.

Select EmployeeId, 
       Code, 
       Year, 
       Count(1) As SessionsAttended
From   @Class C 
       Inner join @Status S 
         On C.UKey = S.UKey
Group By EmployeeId, Code, Year

Now, the trick is to combine them so we know those employees that haven't attended all the sessions needed to complete the course. It's tricky, but not impossible.

Select EmployeeId,
       A.Code,
       A.Year,
       A.SessionsAttended,
       B.TotalSessions
From  (
[blue]      Select EmployeeId, 
             Code, 
             Year, 
             Count(1) As SessionsAttended
      From   @Class C 
             Inner join @Status S 
               On C.UKey = S.UKey
      Group By EmployeeId, Code, Year[/blue]
      ) As A
      Inner Join (
[purple]          Select Code, 
                 Year, 
                 Count(1) As TotalSessions
          From   @Class
          Group By Code, Year[/purple]
          ) As B 
         On  A.Code = B.Code 
         And A.Year = B.Year

Notice how the blue and purple parts of this query are the same as the previous queries. Each query has become part of a sub query, joined together to give more interesting results.

All we really need to do is to add a where clause on this query to filter the records that don't match (SessionsAttended vs. TotalSessions). Finally, we make this whole thing a subqeury, linking it back to the status table to get the final data you want.

Select S.UKey, S.EmployeeId, S.Status, S.StatusDate
From   (
       Select EmployeeId,
              A.Code,
              A.Year,
              A.SessionsAttended,
              B.TotalSessions
       From   (
              Select EmployeeId, 
                     Code, 
                     Year, 
                     Count(1) As SessionsAttended
              From   @Class C 
                     Inner join @Status S 
                       On C.UKey = S.UKey
              Group By EmployeeId, Code, Year
              ) As A
              Inner Join (
                  Select Code, 
                         Year, 
                         Count(1) As TotalSessions
                  From   @Class
                  Group By Code, Year
                  ) As B 
                  On  A.Code = B.Code 
                  And A.Year = B.Year
       Where A.SessionsAttended = B.TotalSessions
       ) As C
       Inner Join @Status S 
         On C.EmployeeId = S.EmployeeId

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

George,
I am sorry I couldn't put more than one star for this explanation. You deserve serveral ones, for explanation of the query.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[gmmastros]

Thanks Borislav.

I thought a query that used a subquery of 2 other subqueries joined together deserved an explanation.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

Yeah, But it was great one

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[pabowen]

Great Post. I will need to look at it a bit deeper to see if it will work 100% for me, but I appreciate the help.

Thanks,
Patrick