DECLARE @Proj TABLE
    (
        CreateDate DATETIME,
        Item NVARCHAR(50),
        ProjNum NVARCHAR(30),
        ResourceId NVARCHAR(50),
        SITE NVARCHAR(10)
    );
    INSERT INTO @Proj
    (
        CreateDate,
        Item,
        ProjNum,
        ResourceId,
        SITE
    )
    SELECT MAX(CAST(b.CreateDate AS DATE)) CreateDate,
           b.Item,
           IIF(b.Item IN ( 'AA150300C100N1800', 'AA150300C100B0700' ), 'PJ15030001', ISNULL(b.ProjNum, CO.Uf_ProjNum)) ProjNum,
           b.ResourceId,
           S.site
    FROM ERP.SyteLine_Apps.dbo.ZVN_ProjProduction_mst b WITH (NOLOCK)
        LEFT JOIN ERP.SyteLine_Apps.dbo.site S WITH (NOLOCK)
            ON S.Uf_VendNum = b.ResourceId
        LEFT JOIN ERP.SyteLine_Apps.dbo.coitem_mst I
            ON I.item = b.Item
               AND I.site_ref = b.SiteRef
        LEFT JOIN ERP.SyteLine_Apps.dbo.co_mst CO
            ON CO.site_ref = I.site_ref
               AND CO.co_num = I.co_num
    WHERE LEFT(b.Item, 2) IN ( 'AA', 'AC', 'AN' )
          AND b.ProjNum =
          (
              SELECT TOP 1
                     c.ProjNum
              FROM ERP.SyteLine_Apps.dbo.ZVN_ProjProduction_mst c WITH (NOLOCK)
                  LEFT JOIN ERP.SyteLine_Apps.dbo.site S WITH (NOLOCK)
                      ON S.Uf_VendNum = b.ResourceId
              WHERE b.Item = c.Item
                    AND S.site = @site --LIKE 'AA173036A050B1050' AND

              GROUP BY c.ProjNum
              ORDER BY c.ProjNum DESC
          )
    GROUP BY b.Item,
             b.ProjNum,
             ResourceId,
             S.site,
             Uf_ProjNum;