Tính tồn kho đầu kỳ, kiểm tra có tồn kho thì trừ tiêu hao. (6 người xem)

[chipiu3001]

Chào các anh chị em.
Mình có 3 bảng data trong SQL server như sau:

Table:
- XN ( xuất nhập)
- TDK ( Tồn đầu các tháng)
- BOM ( tỉ lệ tiêu hao NVL)

Yêu cầu :
1. - Tính tồn đầu kỳ( vào đầu các tháng, ghi dữ liệu vào table TDK)- Dùng lịch thực thi trong SQL server để chạy.
TDK tháng 2= xuất nhập tháng 1 + TDK tháng 1
2. Xử lý tiêu hao NVL cho DH1 SL 50pcs ( Ngày 17/2/24 xử lý)

Kiểm tra lần lượt tồn kho của NVL của DH1 ( NVL1,2,3,4)
- Nếu còn tồn kho sẽ ghi vào Table XN
- Nếu chỉ 1 NVL hết tồn kho sẽ báo lỗi, Hoàn tác lại giao dịch.

Nhờ các ace giúp câu lệnh SQL giúp mình.
Mình cảm ơn nhiều.

 

[chipiu3001]

Nhờ ace giúp mình với ạ.

 

[VetMini]

Cái này thì dùng Join Update. Nhưng gọi "ace" là bất lịch sự. Tôi không chỉ thêm đâu.

 

[chipiu3001]

Vâng. Nếu anh không tính đến yếu tố kiến thức mà soi đến từng chi tiết nhỏ vậy thì em xin lỗi ạ. Cảm ơn anh.

 

[hoanganhvo2612]

Mình thấy tạo bảng tồn đầu các tháng là không cần thiết vì có thể có những giao dịch trong quá khứ nó thay đổi thì dữ liệu tồn đầu không đúng mà phải biết để cập nhật lại. Mình chỉ cần tồn đầu khi bắt đầu sử dụng thôi.
Trong trường hợp của bạn thì mình nghĩ xử lý từng bước như thế này:
- Tìm các dòng có NX trong tháng 2 rồi tính tổng nhập, xuất trong tháng đó
- lấy tồn đầu kỳ là dữ liệu tháng 1 bảng TDK
- left join bước 1 với bước 2

select NX.material, max(TDK.Qty) + sum(iif(status = 'IN',NX.Qty, -NX.Qty)
from NX
left join TDK on TDK.Material = NX.Material
where NX.ngay between '2024-02-01' and '2024-02-29' and tdk.ngay = '2024-01-01''
group by NX.material

 

[chipiu3001]

Mình thấy tạo bảng tồn đầu các tháng là không cần thiết vì có thể có những giao dịch trong quá khứ nó thay đổi thì dữ liệu tồn đầu không đúng mà phải biết để cập nhật lại. Mình chỉ cần tồn đầu khi bắt đầu sử dụng thôi.
Trong trường hợp của bạn thì mình nghĩ xử lý từng bước như thế này:
- Tìm các dòng có NX trong tháng 2 rồi tính tổng nhập, xuất trong tháng đó
- lấy tồn đầu kỳ là dữ liệu tháng 1 bảng TDK
- left join bước 1 với bước 2

select NX.material, max(TDK.Qty) + sum(iif(status = 'IN',NX.Qty, -NX.Qty)
from NX
left join TDK on TDK.Material = NX.Material
where NX.ngay between '2024-02-01' and '2024-02-29' and tdk.ngay = '2024-01-01''
group by NX.material

Chào bạn.
Cảm ơn bạn đã đưa ra ý kiến giúp đỡ mình.
Ý kiến của bạn rất đúng trong việc có phát sinh giao dịch trong quá khứ thì phải tính lại tồn đầu. Tuy nhiên:
Mình nghĩ cần tồn đầu kỳ mỗi tháng ( 00:00 ngày mồng 1 hàng tháng sẽ tự động chạy tồn đầu kỳ tháng đó - Bằng lịch sql server thiết lập) mục đích để sau nếu dữ liệu xuất nhập thời gian vài năm quá lớn mình xóa dữ liệu cũ đi sẽ không bị ảnh hưởng đến xuất nhập tồn hiện tại.
- Nếu không có tồn đầu kỳ hàng tháng, hàng quý thì mỗi khi xử lý tình huống cần kiểm tra tồn hiện tại phải tính lại lệnh tồn xuất nhập từ đầu ( vài năm trước) đến hiện tại => Như vậy hệ thống chạy sẽ rất lâu ( Mình nghĩ là vậy)
Hoặc bạn cho mình thêm ý tưởng với.
- Khi có giao dịch vào tháng cũ ( như điều chỉnh kiểm kê tháng 1 , Ngày ghi vào hệ thống là 31/1 ) thì sẽ có lệch chạy thủ công( người dùng gửi lệnh) để chạy lại tồn đầu kỳ tháng 2.
Không biết ý tưởng trên của mình có OK không?
Lưu ý: Phần mềm không cho phép chỉnh sửa giao dịch từ 2 tháng trước ( Hiện tại là tháng 2-2024 thì không được phép chỉnh sửa dữ liệu tháng 12-2023), Chỉ được chỉnh sửa dữ liệu ở tháng liền kề trước đó.
Bạn giúp mình.
* Tính tồn đầu kỳ:
00:01 ngày 1 hàng tháng sẽ chạy lệnh.
- Kiểm tra tháng hiện tại ( Ví dụ tháng 2)
- Lấy tồn đầu kỳ tháng trước đó( tháng1) + xuất nhập tháng trước đó ( tháng 1)
- Ghi dữ liệu tồn đầu ( Tháng 2) kỳ vào Table TDK ( Trước ghi xử lý xóa dữ liệu tồn đầu kỳ ( Tháng 2- Nếu có tồn tại)

* Tính tồn hiện tại

Mình cảm ơn nhiều.

 

[hoanganhvo2612]

Chào bạn.
Cảm ơn bạn đã đưa ra ý kiến giúp đỡ mình.
Ý kiến của bạn rất đúng trong việc có phát sinh giao dịch trong quá khứ thì phải tính lại tồn đầu. Tuy nhiên:
Mình nghĩ cần tồn đầu kỳ mỗi tháng ( 00:00 ngày mồng 1 hàng tháng sẽ tự động chạy tồn đầu kỳ tháng đó - Bằng lịch sql server thiết lập) mục đích để sau nếu dữ liệu xuất nhập thời gian vài năm quá lớn mình xóa dữ liệu cũ đi sẽ không bị ảnh hưởng đến xuất nhập tồn hiện tại.
- Nếu không có tồn đầu kỳ hàng tháng, hàng quý thì mỗi khi xử lý tình huống cần kiểm tra tồn hiện tại phải tính lại lệnh tồn xuất nhập từ đầu ( vài năm trước) đến hiện tại => Như vậy hệ thống chạy sẽ rất lâu ( Mình nghĩ là vậy)
Hoặc bạn cho mình thêm ý tưởng với.
- Khi có giao dịch vào tháng cũ ( như điều chỉnh kiểm kê tháng 1 , Ngày ghi vào hệ thống là 31/1 ) thì sẽ có lệch chạy thủ công( người dùng gửi lệnh) để chạy lại tồn đầu kỳ tháng 2.
Không biết ý tưởng trên của mình có OK không?
Lưu ý: Phần mềm không cho phép chỉnh sửa giao dịch từ 2 tháng trước ( Hiện tại là tháng 2-2024 thì không được phép chỉnh sửa dữ liệu tháng 12-2023), Chỉ được chỉnh sửa dữ liệu ở tháng liền kề trước đó.
Bạn giúp mình.
* Tính tồn đầu kỳ:
00:01 ngày 1 hàng tháng sẽ chạy lệnh.
- Kiểm tra tháng hiện tại ( Ví dụ tháng 2)
- Lấy tồn đầu kỳ tháng trước đó( tháng1) + xuất nhập tháng trước đó ( tháng 1)
- Ghi dữ liệu tồn đầu ( Tháng 2) kỳ vào Table TDK ( Trước ghi xử lý xóa dữ liệu tồn đầu kỳ ( Tháng 2- Nếu có tồn tại)

* Tính tồn hiện tại

Mình cảm ơn nhiều.

Nếu phần mềm không cho chỉnh giao dịch quá 2 tháng thì dùng bảng TDK là đúng, trong trường hợp tính TDK thì bạn cứ tính lại TDK cho 2 tháng dựa vào gợi ý T-SQL bên trên của mình là được.

 

[chipiu3001]

Nếu phần mềm không cho chỉnh giao dịch quá 2 tháng thì dùng bảng TDK là đúng, trong trường hợp tính TDK thì bạn cứ tính lại TDK cho 2 tháng dựa vào gợi ý T-SQL bên trên của mình là được.

Cảm ơn bạn.
Bạn giúp mình ý thứ 2 của bài #1
Mình cảm ơn

 

[hoanganhvo2612]

Cảm ơn bạn.
Bạn giúp mình ý thứ 2 của bài #1
Mình cảm ơn

ý 2 là phải check trc khi insert chứ? không thì phải có ràng buộc không cho chèn giá trị âm vào bảng NX.

 

[chipiu3001]

ý 2 là phải check trc khi insert chứ? không thì phải có ràng buộc không cho chèn giá trị âm vào bảng NX.

Đúng rồi bạn. Phải check tất cả các NVL đủ tồn kho thì mới được insert.

 

[chipiu3001]

hoanganhvo2612​

Bạn xem giúp mình code này có rủi do gì không. Mình chạy thì thấy nó ra đúng số liệu.
Mình cảm ơn bạn

View attachment 299093

BEGIN TRANSACTION;

    DECLARE @PreviousMonth NVARCHAR(7);
    SET @PreviousMonth = FORMAT(DATEADD(MONTH, -1, GETDATE()), 'MM-yyyy'); --Tháng trước tháng hiện tại

    DELETE FROM TDK WHERE THANG = FORMAT(GETDATE(), 'MM-yyyy'); -- Xóa dữ liệu TDK tháng hiện tại

    INSERT INTO TDK (THANG, MA_NVL, SL)
    SELECT
        FORMAT(GETDATE(), 'MM-yyyy') AS THANG,
        XN.MA_NVL,
        SUM(CASE WHEN XN.TT = 'IN' THEN XN.SL ELSE -XN.SL END)
            + ISNULL((SELECT SUM(SL) FROM TDK WHERE THANG = @PreviousMonth AND MA_NVL = XN.MA_NVL), 0) AS SL
    FROM
        XN
    WHERE
        FORMAT(XN.NGAY, 'MM-yyyy') = @PreviousMonth  -- Lấy dữ liệu từ tháng trước
    GROUP BY
        XN.MA_NVL;

COMMIT TRANSACTION;

 

[hoanganhvo2612]

hoanganhvo2612​

Bạn xem giúp mình code này có rủi do gì không. Mình chạy thì thấy nó ra đúng số liệu.
Mình cảm ơn bạn

View attachment 299093

BEGIN TRANSACTION;

    DECLARE @PreviousMonth NVARCHAR(7);
    SET @PreviousMonth = FORMAT(DATEADD(MONTH, -1, GETDATE()), 'MM-yyyy'); --Tháng trước tháng hiện tại

    DELETE FROM TDK WHERE THANG = FORMAT(GETDATE(), 'MM-yyyy'); -- Xóa dữ liệu TDK tháng hiện tại

    INSERT INTO TDK (THANG, MA_NVL, SL)
    SELECT
        FORMAT(GETDATE(), 'MM-yyyy') AS THANG,
        XN.MA_NVL,
        SUM(CASE WHEN XN.TT = 'IN' THEN XN.SL ELSE -XN.SL END)
            + ISNULL((SELECT SUM(SL) FROM TDK WHERE THANG = @PreviousMonth AND MA_NVL = XN.MA_NVL), 0) AS SL
    FROM
        XN
    WHERE
        FORMAT(XN.NGAY, 'MM-yyyy') = @PreviousMonth  -- Lấy dữ liệu từ tháng trước
    GROUP BY
        XN.MA_NVL;

COMMIT TRANSACTION;

Cái này bạn phải tự kiểm tra lại thôi, bạn thử select ra trước xem nó trả về đúng không đã.

 

[chipiu3001]

Bị lỗi bạn ạ. NVL có trong bảng TDK mà không có trong bảng XN thì nó không ra.
Mình sửa như dưới thấy OK rồi.

DECLARE @PreviousMonth NVARCHAR(7);
SET @PreviousMonth = FORMAT(DATEADD(MONTH, -1, GETDATE()), 'MM-yyyy'); --Tháng trước tháng hiện tại

DELETE FROM TDK WHERE THANG = FORMAT(GETDATE(), 'MM-yyyy'); -- Xóa dữ liệu TDK tháng hiện tại

INSERT INTO TDK (THANG, MA_NVL, SL)
SELECT
    FORMAT(GETDATE(), 'MM-yyyy') AS THANG,
    NVL.MA_NVL,
    ISNULL(SUM(CASE WHEN XN.TT = 'IN' THEN XN.SL ELSE -XN.SL END),0)
        + ISNULL((SELECT SUM(SL) FROM TDK WHERE THANG = @PreviousMonth AND MA_NVL = NVL.MA_NVL), 0) AS SL
FROM
    (SELECT DISTINCT MA_NVL FROM XN
     UNION
     SELECT DISTINCT MA_NVL FROM TDK) AS NVL  -- Lấy tất cả các MA_NVL từ cả hai bảng
LEFT JOIN
    XN ON FORMAT(XN.NGAY, 'MM-yyyy') = @PreviousMonth AND NVL.MA_NVL = XN.MA_NVL
GROUP BY
    NVL.MA_NVL;

Còn ý 2 trong #1. Bạn giúp mình, hoặc gợi ý giúp mình nha
Cảm ơn bạn.

 

[hoanganhvo2612]

Bị lỗi bạn ạ. NVL có trong bảng TDK mà không có trong bảng XN thì nó không ra.
Mình sửa như dưới thấy OK rồi.

DECLARE @PreviousMonth NVARCHAR(7);
SET @PreviousMonth = FORMAT(DATEADD(MONTH, -1, GETDATE()), 'MM-yyyy'); --Tháng trước tháng hiện tại

DELETE FROM TDK WHERE THANG = FORMAT(GETDATE(), 'MM-yyyy'); -- Xóa dữ liệu TDK tháng hiện tại

INSERT INTO TDK (THANG, MA_NVL, SL)
SELECT
    FORMAT(GETDATE(), 'MM-yyyy') AS THANG,
    NVL.MA_NVL,
    ISNULL(SUM(CASE WHEN XN.TT = 'IN' THEN XN.SL ELSE -XN.SL END),0)
        + ISNULL((SELECT SUM(SL) FROM TDK WHERE THANG = @PreviousMonth AND MA_NVL = NVL.MA_NVL), 0) AS SL
FROM
    (SELECT DISTINCT MA_NVL FROM XN
     UNION
     SELECT DISTINCT MA_NVL FROM TDK) AS NVL  -- Lấy tất cả các MA_NVL từ cả hai bảng
LEFT JOIN
    XN ON FORMAT(XN.NGAY, 'MM-yyyy') = @PreviousMonth AND NVL.MA_NVL = XN.MA_NVL
GROUP BY
    NVL.MA_NVL;

Còn ý 2 trong #1. Bạn giúp mình, hoặc gợi ý giúp mình nha
Cảm ơn bạn.

sao bạn không dùng bảng danh mục mà lại union vậy? bình thường thiết kế phải có bảng danh mục.
ý 2 thì bạn dùng bảng tạm
- bước 1 tính toán NVL cần vào bảng tạm
- Bước 2 check trong bảng TDK
- bc3 update lai tdk
declare @bangtam as table(nvl, soluong)
insert @bangtam
select nvl, soluongSP * TL
from bangBom
where code = 'xxx'

if not exists (select 1 from tdk join @bangtam bt where tdk.nvl = bt.nvl and tdk.sl < bt.sl)
begin
insert bangnx
select xx
from @bangtam

call procupdatetdk
end if

 

[chipiu3001]

sao bạn không dùng bảng danh mục mà lại union vậy? bình thường thiết kế phải có bảng danh mục.
ý 2 thì bạn dùng bảng tạm
- bước 1 tính toán NVL cần vào bảng tạm
- Bước 2 check trong bảng TDK
- bc3 update lai tdk
declare @bangtam as table(nvl, soluong)
insert @bangtam
select nvl, soluongSP * TL
from bangBom
where code = 'xxx'

if not exists (select 1 from tdk join @bangtam bt where tdk.nvl = bt.nvl and tdk.sl < bt.sl)
begin
insert bangnx
select xx
from @bangtam

call procupdatetdk
end if

Đang loay hoay giải quyết vấn đề mà lại quên cái gốc ban đâu là tạo bảng danh mục để dàng buộc mã trong XN, TDK. Hix, hài thật. lại đi union


B2: sao lại kiểm tra trong bảng TDK nhỉ ( Bảng này là lưu tồn kho đầu kỳ mà) . Phải kiểm tra tồn hiện tại chứ bạn.
Có phải lại phải làm 1 bảng tạm tồn hiện tại, xong so sánh với cái bảng tạm tiêu hao ( như bước 3 bạn hướng dẫn không)
Mình cảm ơn

 

[hoanganhvo2612]

Đang loay hoay giải quyết vấn đề mà lại quên cái gốc ban đâu là tạo bảng danh mục để dàng buộc mã trong XN, TDK. Hix, hài thật. lại đi union


B2: sao lại kiểm tra trong bảng TDK nhỉ ( Bảng này là lưu tồn kho đầu kỳ mà) . Phải kiểm tra tồn hiện tại chứ bạn.
Có phải lại phải làm 1 bảng tạm tồn hiện tại, xong so sánh với cái bảng tạm tiêu hao ( như bước 3 bạn hướng dẫn không)
Mình cảm ơn

uhm, Bạn phải có tồn hiện tại của các NVL, tính toán same như tồn đầu kỳ thôi.

 

[chipiu3001]

uhm, Bạn phải có tồn hiện tại của các NVL, tính toán same như tồn đầu kỳ thôi.

Ý mình là vẫn phải tạo 2 bảng tạm
- Bảng tiêu hao theo BOM
- Bảng tồn hiện tại
Sau đó so sánh à bạn.
Mình làm theo bạn hướng dẫn
Cảm ơn bạn nhé.

 

[chipiu3001]

hoanganhvo2612​

Bạn cho mình xin số tài khoản ( Hoặc zalo pay) .
Mình gửi bạn chút uống cafe thay lời cảm ơn.
Dù rằng mình nghĩ là sẽ có nhiều cách tốt hơn đến từ các anh chị em trong diễn đàn. Nhưng bạn là người giúp đỡ nhiệt tình. 2 lần đăng bài bạn đều nhiệt tình chỉ giúp.
Cảm ơn bạn

 

[hoanganhvo2612]

hoanganhvo2612​

Bạn cho mình xin số tài khoản ( Hoặc zalo pay) .
Mình gửi bạn chút uống cafe thay lời cảm ơn.
Dù rằng mình nghĩ là sẽ có nhiều cách tốt hơn đến từ các anh chị em trong diễn đàn. Nhưng bạn là người giúp đỡ nhiệt tình. 2 lần đăng bài bạn đều nhiệt tình chỉ giúp.
Cảm ơn bạn

Bạn cần hỗ trợ thêm thì liên hệ mình qua gmail: anchanchan0402@gmail.com

 

[VetMini]

Vâng. Nếu anh không tính đến yếu tố kiến thức mà soi đến từng chi tiết nhỏ vậy thì em xin lỗi ạ. Cảm ơn anh.

Hỏi thì phải chịu khó. Xưng hô kém lịch sự rồi bảo người ta soi mói.

sao bạn không dùng bảng danh mục mà lại union vậy? bình thường thiết kế phải có bảng danh mục.
ý 2 thì bạn dùng bảng tạm
- bước 1 tính toán NVL cần vào bảng tạm
- Bước 2 check trong bảng TDK
- bc3 update lai tdk
...

Bảng tạm: dùng CTE
Ghi: cứ ghi hết mọi thứ. Sau khi ghi xong, xét lại cái nào không muốn giữ thì xóa.

Tuy nhiên, thiết kế CSDL kiểu này là quá ấu trĩ. Dữ liệu từ bảng này là tính toán từ bảng khác! SQL Server mạnh đủ để có thể thiết kế chuẩn hơn. Đáng lẽ mấy cái tồn đâu kỳ cuối kỳ thì tạo View chuẩn hơn.