Confusion in building sql query in sql server

My table looks exactly like below

School              Course              Date             Status

SCH1            SCH1CRS1        2011-01-15           S

                SCH1CRS1        2011-01-17           C

SCH1            SCH1CRS2        2011-01-15           S

SCH2开发者_如何学JAVA            SCH2CRS1        2011-01-15           S

                SCH2CRS1        2011-01-21           C

SCH2             SCH2CRS2        2011-01-22          S

               SCH2CRS2          2011-01-25          C

Status S=Started and C=Completed and date is start date and completed date. When a course started, user enters schoolID, courseID,Date and status, But when a course is completed, user enters courseID, Date and status. User does not enter SchoolID. From the table we can understand that SCH1CRS1 and SCH1CRS2 belongs to SCH1. What i need to know is, In a particular school, on a particular date, how many courses started but not completed.

You can see in SCH1, on 2011-01-15 two courses have started, but only one is completed. It doesn't matter when the course is completed. All i need to know is completed or not. In SCH2, two courses started on different dates, but both are completed.

Result table should be like this

Input Date: 2011-01-15

School             Not completed  

SCH1                       1

SCH2                       0

Input Date: 2011-01-22

School           Not Completed

SCH2                      0 

I think this is a messy table and database design rules are not followed. But this is what i have . No option.

Thanks

You have to find the completed course related to the started course, and if there is no completed course, count 1.

SELECT startedCourse.School AS School, 
       SUM(CASE WHEN completedCourse IS NULL THEN 1 ELSE 0 END) AS NotCompleted
FROM MyTable startedCourse
    LEFT OUTER JOIN MyTable completedCourse
        ON startedCourse.Status = 'S'
        AND completedCourse.Status = 'C'
        AND startedCourse.Course = completedCourse.Course
WHERE startedCourse.Date = ?
GROUP BY startedCourse.School

from the top of my head :

select c_started.courseID, c_started.courseDate as StartDate, c_completed.courseDate as CompletedDate, 
case 
 when c_completed.courseDate IS NULL then 'incomplete'
 Else 'completed'
end as courseStatus
from tbCourses as c_started
left join tbCourses as c_completed on c_started.courseID = c_completed.courseID 
and c_started.courseDate < c_completed.courseDate
where c_started.Status = 'S'

Would give you something like the following :

courseID -- StartDate -- CompletedDate -- courseStatus

course1 -- 2010-01-12 -- 2010-02-25 -- completed

course2 -- 2011-01-21 -- NULL -- incomplete

course3 -- 2010-10-15 -- NULL -- incomplete

How about this:

declare @InputDate datetime = '20110115';

with cCourseList as (

select  t1.School, t1.Course, DateStarted = t1.Date,
        t2.DateCompleted
from    tbl t1
outer apply 
        (select  top(1) DateCompleted = t2.Date
        from    tbl t2
        where   t2.Course = t1.Course
            and t2.Status = 'C'
        order by t2.Date) t2
where   t1.Status = 'S'

)

select  cl.School, 
        [Not Completed] = sum(case when cl.DateCompleted is null then 1 else 0 end)
from    cCourseList cl
where   cl.DateStarted <= @InputDate
group by cl.School;