Xử lý dữ liệu Sivip.Web

Kiểm tra sai lệch giữa bảng R00$... và bảng CT00

SELECT * FROM ct00 WHERE stt_rec NOT IN (SELECT stt_rec FROM r00$202001) AND MONTH(ngay_ct) = 1 AND YEAR(ngay_ct) = 2020
SELECT * FROM ct00 WHERE stt_rec NOT IN (SELECT stt_rec FROM r00$202002) AND MONTH(ngay_ct) = 2 AND YEAR(ngay_ct) = 2020
SELECT  *  FROM ct00 WHERE stt_rec NOT IN (SELECT stt_rec FROM r00$202003) AND MONTH(ngay_ct) = 3 AND YEAR(ngay_ct) = 2020
SELECT * FROM ct00 WHERE stt_rec NOT IN (SELECT stt_rec FROM r00$202004) AND MONTH(ngay_ct) = 4 AND YEAR(ngay_ct) = 2020
SELECT  *  FROM ct00 WHERE stt_rec NOT IN (SELECT stt_rec FROM r00$202005) AND MONTH(ngay_ct) = 5 AND YEAR(ngay_ct) = 2020
SELECT * FROM ct00 WHERE stt_rec NOT IN (SELECT stt_rec FROM r00$202006) AND MONTH(ngay_ct) = 6 AND YEAR(ngay_ct) = 2020 
SELECT  * FROM ct00 WHERE stt_rec NOT IN (SELECT stt_rec FROM r00$202007) AND MONTH(ngay_ct) = 7 AND YEAR(ngay_ct) = 2020 
SELECT  *  FROM ct00 WHERE stt_rec NOT IN (SELECT stt_rec FROM r00$202008) AND MONTH(ngay_ct) = 8 AND YEAR(ngay_ct) = 2020 
SELECT  * FROM ct00 WHERE stt_rec NOT IN (SELECT stt_rec FROM r00$202009) AND MONTH(ngay_ct) = 9 AND YEAR(ngay_ct) = 2020 
SELECT  * FROM ct00 WHERE stt_rec NOT IN (SELECT stt_rec FROM r00$202010) AND MONTH(ngay_ct) = 10 AND YEAR(ngay_ct) = 2020 
SELECT  * FROM ct00 WHERE stt_rec NOT IN (SELECT stt_rec FROM r00$202011) AND MONTH(ngay_ct) = 11 AND YEAR(ngay_ct) = 2020 
SELECT * FROM ct00 WHERE stt_rec NOT IN (SELECT stt_rec FROM r00$202012) AND MONTH(ngay_ct) = 12 AND YEAR(ngay_ct) = 2020

Kiểm tra bảng R00$.. bị đúp dữ liệu dẫn đến số liệu báo cáo gấp đôi so với chứng từ

SELECT stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr FROM r00$202001 GROUP BY stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr HAVING count(stt_rec)> 1
SELECT stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr FROM r00$202002 GROUP BY stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr HAVING count(stt_rec)> 1
SELECT stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr FROM r00$202003 GROUP BY stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr HAVING count(stt_rec)> 1
SELECT stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr FROM r00$202004 GROUP BY stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr HAVING count(stt_rec)> 1
SELECT stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr FROM r00$202005 GROUP BY stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr HAVING count(stt_rec)> 1
SELECT stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr FROM r00$202006 GROUP BY stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr HAVING count(stt_rec)> 1
SELECT stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr FROM r00$202007 GROUP BY stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr HAVING count(stt_rec)> 1
SELECT stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr FROM r00$202008 GROUP BY stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr HAVING count(stt_rec)> 1
SELECT stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr FROM r00$202009 GROUP BY stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr HAVING count(stt_rec)> 1
SELECT stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr FROM r00$202010 GROUP BY stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr HAVING count(stt_rec)> 1
SELECT stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr FROM r00$202011 GROUP BY stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr HAVING count(stt_rec)> 1
SELECT stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr FROM r00$202012 GROUP BY stt_rec, ma_dvcs, ma_ct, ngay_ct, so_ct, dien_giai_h, tk, tk_du, ps_no, ps_co, ma_kh, ma_vv, ma_nk, ma_sp, ma_bp, so_lsx, line_nbr HAVING count(stt_rec)> 1