cuong0202 > 26-02-13, 05:27 PM
SELECT Tonct.MaHang, tonct.TenHang, sum(Tonct.Tondk) AS TonDau, sum(Tonct.Nhaptk) AS Nhap, sum(Tonct.Xuattk) AS Xuat, (sum(Tonct.Tondk)+sum(Tonct.Nhaptk)- sum(Tonct.Xuattk)) AS TonCuoi
FROM (Select dk.MaHang, dk.TenHang, Tondk, 0 as Nhaptk, 0 as Xuattk From
(Select a.MaHang, a.TenHang, (Sum(a.Nhap) - Sum(a.Xuat)) AS Tondk From
(Select N.MaHangnhap, H.TenHang , Sum(N.SoLuongnhap) as Nhap, 0 as Xuat From Qallnhap N, Qdanhmuchanghoa H Where N.MaHangnhap = H.MaHang and N.NgayNhap<[Ngaydk] Group By N.Mahangnhap, H.TenHang
UNION
(Select X.MaHangxuat, H.TenHang , 0 as Nhap, sum (X.SoLuongxuat) as Xuat From Qallxuat X, Qdanhmuchanghoa H Where X.MaHangxuat = H.MaHang and X.NgayXuat<[Ngaydk] Group By X.Mahangxuat, H.TenHang)) a
GROUP BY a.MaHang, a.TenHang
HAVING (Sum(a.Nhap - a.Xuat))<>0) dk
Union
Select Mahang, Tenhang, 0 as Tondk, 0 as Nhaptk, 0 as Xuattk From Qdanhmuchanghoa
Union
Select N.MaHangnhap, H.TenHang , 0 as Tondk, Sum(N.SoLuongnhap) as Nhaptk, 0 as Xuattk From Qallnhap N, Qdanhmuchanghoa H Where N.MaHangnhap = H.MaHang and N.NgayNhap>=[Ngaydk] and N.NgayNhap<=[Ngayck] Group By N.Mahangnhap, H.TenHang
Union
Select X.MaHangxuat, H.TenHang, 0 as Tondk , 0 as Nhaptk, sum (X.SoLuongxuat) as Xuattk From Qallxuat X, Qdanhmuchanghoa H Where X.MaHangxuat = H.MaHang and X.NgayXuat>=[Ngaydk] and X.NgayXuat<=[Ngayck] Group By X.Mahangxuat, H.TenHang
) AS Tonct
GROUP BY Tonct.MaHang, Tonct.TenHang
HAVING (sum(Tonct.Tondk)+sum(Tonct.Nhaptk)- sum(Tonct.Xuattk))<>0;