Delete tbl_QuyetToanThueThuNhap_Detail

 From tbl_QuyetToanThueThuNhap_Detail qt

  INNER JOIN (Select cMaNhanVien, cMaCoCauToChuc, Max(dNgayHieuLuc) dNgayHieuLuc From tbh_QuaTrinhChuyenCongTac cc

    Where dNgayHieuLuc in (Select Max(dNgayHieuLuc) From tbh_QuaTrinhChuyenCongTac cc2

         Where cc2.cMaNhanVien = cc.cMaNhanVien And dNgayHieuLuc<= @NgayKTThang)

     AND nMaQuaTrinhChuyenCongTac in (Select Max(nMaQuaTrinhChuyenCongTac) From tbh_QuaTrinhChuyenCongTac cc2

         Where cc2.cMaNhanVien = cc.cMaNhanVien And dNgayHieuLuc<= @NgayKTThang)

    GROUP BY cMaNhanVien, cMaCoCauToChuc ) cctl

   ON qt.MaNhanVien = cctl.cMaNhanVien

  INNER JOIN tbh_CoCauToChuc cctc ON cctl.cMaCoCauToChuc = cctc.cMaCoCauToChuc

  INNER JOIN tbh_NhanVien nv ON nv.MaNhanVien = qt.MaNhanVien AND Nam = @Nam

 Where  (qt.Nam = @Nam)

  AND (qt.MaNhanVien = @MaNhanVien OR @MaNhanVien = '')

  AND (cctc.cMaLoaiCoCauToChuc = @MaNhomPhongBan OR @MaNhomPhongBan = '')

  AND (cctc.cMaCoCauToChuc = @MaCoCauToChuc OR @MaCoCauToChuc = '')

  AND (nv.MaLoaiNhanVien = @MaLoaiNhanVien or @MaLoaiNhanVien =0)

  AND (nv.MaHinhThucLamViec = @MaHinhThucLamViec Or @MaHinhThucLamViec ='')

  AND (Year(nv.NgayNghiViec) >= @Nam OR nv.NgayNghiViec is NULL)  --Không xóa những nhân viên đã nghỉ việc

  AND (DaQuyetToan = 0 OR DaQuyetToan is NULL)

+++++++++++++++++++++++++++++++++++++++++++++++++------------------------------------------------++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

DELETE tbl_QuyetToanThueThuNhap_Detai

WHERE ROWID IN

( SELECT qtcctlcctcnv.ROWID

  FROM tbl_QuyetToanThueThuNhap_Detai qt

         JOIN ( SELECT cMaNhanVien ,

                       cMaCoCauToChuc ,

                       MAX(dNgayHieuLuc) dNgayHieuLuc 

                FROM tbh_QuaTrinhChuyenCongTac cc

                 WHERE dNgayHieuLuc IN ( SELECT MAX(dNgayHieuLuc)

                                         FROM tbh_QuaTrinhChuyenCongTac cc2

                                          WHERE cc2.cMaNhanVien = cc.cMaNhanVien

                                                  AND dNgayHieuLuc <= v_NgayKTThang )

                         AND nMaQuaTrinhChuyenCongTac IN ( SELECT MAX(nMaQuaTrinhChuyenCongTac)

                                                           FROM tbh_QuaTrinhChuyenCongTac cc2

                                                            WHERE cc2.cMaNhanVien = cc.cMaNhanVien

                                                                    AND dNgayHieuLuc <= v_NgayKTThang )

                  GROUP BY cMaNhanVien,cMaCoCauToChuc ) cctl

          ON qt.MaNhanVien = cctl.cMaNhanVien

         JOIN tbh_CoCauToChuc cctc

          ON cctl.cMaCoCauToChuc = cctc.cMaCoCauToChuc

         JOIN tbh_NhanVien nv

          ON nv.MaNhanVien = qt.MaNhanVien

         AND Nam = v_Nam

 WHERE ( qt.Nam = v_Nam )

         AND ( qt.MaNhanVien = v_MaNhanVien

         OR v_MaNhanVien IS NULL )

         AND ( cctc.cMaLoaiCoCauToChuc = v_MaNhomPhongBan

         OR v_MaNhomPhongBan IS NULL )

         AND ( cctc.cMaCoCauToChuc = v_MaCoCauToChuc

         OR v_MaCoCauToChuc IS NULL )

         AND ( nv.MaLoaiNhanVien = v_MaLoaiNhanVien

         OR v_MaLoaiNhanVien = 0 )

         AND ( nv.MaHinhThucLamViec = v_MaHinhThucLamViec

         OR v_MaHinhThucLamViec IS NULL )

         AND ( sqlserver_utilities.year_(nv.NgayNghiViec) >= v_Nam

         OR nv.NgayNghiViec IS NULL--Không xóa những nhân viên đã nghỉ việc

        )

         AND ( DaQuyetToan = 0

         OR DaQuyetToan IS NULL ) );