select
case
when a.cContract < b.cContract then N'사후'
else N'사전
end
from dbo.tCodeMaster as a
left join dbo.tWorkLog as b
on b.cProjectCode = a.cProjectCode

;with 프로젝트(코드,프로젝트명) As
(
      Select 'A','A프로젝트' Union All 
      Select 'B','B프로젝트' Union All 
      Select 'C','C프로젝트'
)
,작업시간(프로젝트코드,작업일,작업자) As
(
      Select 'A','2015-01-01','a' Union All
      Select 'A','2015-01-02','a' Union All
      Select 'A','2015-01-03','a' Union All
      Select 'A','2015-01-04','a' Union All
      Select 'A','2015-01-05','a' Union All
      Select 'A','2015-01-06','a' Union All
      Select 'A','2015-01-07','a' Union All
      Select 'A','2015-01-08','a' Union All
      Select 'B','2015-01-01','b' Union All
      Select 'B','2015-01-02','b' Union All
      Select 'B','2015-01-03','b' Union All
      Select 'B','2015-01-04','b' Union All
      Select 'B','2015-01-05','b' Union All
      Select 'C','2015-01-01','b' Union All
      Select 'C','2015-01-02','b' Union All
      Select 'C','2015-01-03','b' Union All
      Select 'C','2015-01-04','b' Union All
      Select 'C','2015-01-05','b'
)
,계약(계약코드,프로젝트코드,계약일) As
(
      Select 'S-1','A','2015-01-04' Union All
      Select 'S-2','C','2015-01-02'
)
      Select
             a.코드
            ,b.작업일
            ,b.작업자
            ,IsNull(c.계약일,'') As 계약일
            ,(
                  Case When b.작업일 < IsNull(c.계약일,'9999-12-31') Then '사전'
                       Else '사후'
                  End
             )
              As 단계
        From 프로젝트 a
                        Inner Join 작업시간 b On a.코드 = b.프로젝트코드
                        Left Outer Join 계약 c On a.코드 = c.프로젝트코드
    Order By a.코드 Asc
            ,b.작업일 Asc