bai4_csdl_nam

SELECT MAGV, CASE PHAI
WHEN 0 THEN 'THAY'+ HOTEN
WHEN 1 THEN 'CO' + HOTEN
END AS HT
FROM GIAOVIEN

-------------------------------------------
SELECT LEFT(HOTEN,CHARINDEX(' ',HOTEN)) AS HO_GV,
RIGHT(HOTEN,LEN(HOTEN)-CHARINDEX(' ',HOTEN)) AS TEN_GV
FROM GIAOVIEN
------------------------------------------------
SELECT DISTINCT A.MAGV,HOTEN
FROM GD_0506 A,GIAOVIEN B
WHERE A.MAGV=B.MAGV
AND LOPDAY LIKE '%DT%'
AND A.MAGV NOT IN
(SELECT MAGV FROM GD_0506
WHERE LOPDAY LIKE '%TH%'
OR LOPDAY NOT LIKE '%DT%')
--------------------------------------------------
SELECT HOTEN,LOPDAY
FROM GIAOVIEN A,GD_0506 B,CHUCDANH C
WHERE A.MAGV=B.MAGV
AND A.MACD=C.MACD
AND TENCD='GIANG VIEN'
AND LOPDAY LIKE '%DT%20%'
-----------------------------------------------------
SELECT HOTEN,COUNT(NGAY_TA) AS SOLANTA
FROM GIAOVIEN A,TAMUNG B
WHERE A.MAGV=B.MAGV
AND HOTEN LIKE '% VAN %'
GROUP BY B.MAGV,HOTEN
HAVING COUNT(NGAY_TA)>1
-----------------------------------------------------
SELECT HOTEN,NGAY_TA
FROM GIAOVIEN A,TAMUNG B
WHERE A.MAGV=B.MAGV
AND DATEPART(MONTH,NGAY_TA)=1
AND DATEPART(YEAR,NGAY_TA)=2006
---------------------------------------------------
SELECT HOTEN,NGAY_TA
FROM GIAOVIEN A,TAMUNG B
WHERE A.MAGV=B.MAGV
AND DATEDIFF(MONTH,NGAY_TA,GETDATE())<=3
---------------------------------------------
SELECT SUM(CASE
WHEN SISO<=80 THEN SOTIET
WHEN SISO<=120 THEN SOTIET *1.2
WHEN SISO<=150 THEN SOTIET *1.4
ELSE SOTIET *1.5
END)
FROM GIAOVIEN A,GD_0506 B
WHERE A.MAGV=B.MAGV
AND HOTEN LIKE '% NAM'
-------------------------
SELECT MAGV, SUM(CASE
WHEN SISO<=80 THEN SOTIET
WHEN SISO<=120 THEN SOTIET *1.2
WHEN SISO<=150 THEN SOTIET *1.4
ELSE SOTIET *1.5
END)AS TONGSTQC
INTO #CAU_4D
FROM GD_0506
GROUP BY MAGV
SELECT HOTEN, TONGSTQC,GIOCHUAN
FROM #CAU_4D A,GIAOVIEN B,CHUCDANH C
WHERE A.MAGV=B.MAGV
AND B.MACD=C.MACD
AND TONGSTQC>GIOCHUAN
-----------------------------
SELECT MAGV, SUM(CASE
WHEN SISO<=80 THEN SOTIET
WHEN SISO<=120 THEN SOTIET *1.2
WHEN SISO<=150 THEN SOTIET *1.4
ELSE SOTIET *1.5
END)AS TONGSTQC
INTO #CAU_4F1
FROM GD_0506
GROUP BY MAGV
SELECT MAGV, SUM(SOTIEN) AS TONGTA
INTO #CAU_4F2
FROM TAMUNG
GROUP BY MAGV
SELECT A.MAGV,CASE
WHEN (TONGSTQC>GIOCHUAN)AND TONGTA IS NULL THEN TIEN_1TIET*(TONGSTQC-GIOCHUAN)
WHEN (TONGSTQC>GIOCHUAN)AND TONGTA IS NOT NULL THEN TIEN_1TIET*(TONGSTQC-GIOCHUAN)-TONGTA
WHEN (TONGSTQC>GIOCHUAN)AND TONGTA IS NULL THEN 0
ELSE -TONGTA
END AS THUCLINH
FROM #CAU_4F1 A RIGHT OUTER JOIN GIAOVIEN B ON A.MAGV=B.MAGV JOIN CHUCDANH C ON B.MACD=C.MACD LEFT OUTER JOIN #CAU_4F2 D ON D.MAGV=A.MAGV