SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO


CREATE VIEW [dbo].[FACT_M3byShif]
AS
SELECT SUM(ISNULL(I.Uf_ZVN_M3PerM, 0) * ISNULL(M.qty, 0) * ISNULL(dbo.ZVN_GetPileLength(M.item), 0)) M3,
       a.CaSanXuat,
       a.XuongSanXuat,
       a.NgaySanXuat,
       a.site_ref
FROM ERP.SyteLine_Apps.dbo.matltran_mst M WITH (NOLOCK)
    JOIN ERP.SyteLine_Apps.dbo.item_mst I WITH (NOLOCK)
        ON M.site_ref = I.site_ref
           AND M.item = I.item
    LEFT JOIN
    (
        SELECT -- j.job Lenh,

            j.item,
            CAST(jt.trans_date AS DATE) NgaySanXuat,
            jt.Uf_ZVN_Machine XuongSanXuat,
            jt.shift CaSanXuat,
            SUM(jt.qty_complete + jt.qty_scrapped) SLSanXuatERP,
            j.site_ref
        FROM ERP.SyteLine_Apps.dbo.jobtran_mst jt
            JOIN ERP.SyteLine_Apps.dbo.job_mst j
                ON j.job = jt.job
                   AND j.suffix = jt.suffix
                   AND j.site_ref = jt.site_ref
        WHERE jt.posted = 1
        GROUP BY j.item,
                 CAST(jt.trans_date AS DATE),
                 jt.Uf_ZVN_Machine,
                 jt.shift,
                 j.site_ref
    ) a
        ON a.site_ref = M.site_ref
           AND a.item = M.item
WHERE YEAR(a.NgaySanXuat) >= 2019
      AND a.CaSanXuat IS NOT NULL
      AND a.XuongSanXuat IS NOT NULL
GROUP BY a.CaSanXuat,
         a.XuongSanXuat,
         a.NgaySanXuat,
         a.site_ref;
GO