SQL with multiple when have null

First, this is your query:

select s.StudentId, s.Studentname,
       (case (select top 1 Assignmentstatus
              from Assignment a2
              where a2.StudentId = s.StudentId
              order by date desc
             )
            when 0 then 1 else 0
        end) as AssignmentSubmitted 
from student s left join
     Assignment a 
     on a.studentId = s.StudentId;

The outer join to assignment is not necessary, so you probably really want:

select s.StudentId, s.Studentname,
       (case (select top 1 Assignmentstatus
              from Assignment a2
              where a2.StudentId = s.StudentId
              order by date desc
             )
            when 0 then 1 else 0
        end) as AssignmentSubmitted 
from student s;

Your inner join is comparing the most recent value of AssignmentStatus in Assignment for a given student to 0. For neither student is the most recent status 0. In one case it is "1". In the other "Null", so it will always return 0. A SQL Fiddle is here.

Presumably, you want the status if available. I would be inclined to write this query using outer apply:

select s.StudentId, s.Studentname, coalesce(a.Assignmentstatus, 0) as Assignmentsubmitted
from student s outer apply
     (select top 1 Assignmentstatus
      from Assignment a2
      where a2.StudentId = s.StudentId
      order by date desc
     ) a;

You can use a case instead of coalesce() if the actual logic is more complicated.

You don't need to use a CASE statement. Instead you can replace NULL with 0 using ISNULL:

SELECT Student.StudentId,
       Student.Studentname,
       ISNULL(Assignment.AssignmentStatus, 0) AS AssignmentStatus
FROM Student 
LEFT JOIN Assignment
    ON Assignment.StudentId = Student.StudentId

It seems you that you want to get the latest Assignmentstatus to any Student, don't you?

How about like that?

select t1.Studentname, 
       t2.Assignmentstatus, 
       t2.date 
  from table1 t1, table2 t2
 where t1.StudentId = t2.StudentId 
   and t2.date = (Select max(date) from table2 where StudentId = t1.StudentId)

http://www.sqlfiddle.com/#!2/5af1a1/1

Assuming AssignmentStatus only ever increases (e.g. doesn't ever go back to 'submitted' after being returned) then you could use something like:

SELECT s.StudentId
     , s.Studentname
     , ISNULL(MAX(a.AssignmentStatus), 0) AS AssignmentStatus
FROM Student s
     LEFT JOIN Assignment a ON a.StudentId = s.StudentId
group by s.StudentId, s.Studentname