Minigrip de México

ALTER VIEW viewOrdenesActivas

AS

SELECT

    Orden        = o.OrderNumber,

    OC           = o.CustPONumber,

    FechaOrd     = o.OrderDate,

    Periodo      = dbo.getMesDateTime(o.OrderDate),

    Cliente      = UPPER(c.CustomerName),

    Tipo         = o.TransactionType,

    Item         = o.ItemStatus,

    Partidas     = ISNULL(Rel.Partidas,0)

FROM

    [TORI-FLEX]..tblOrder o WITH (NOLOCK)

        INNER JOIN

    [TORI-FLEX]..tblCustomer c WITH (NOLOCK)

        ON c.CustomerNumber = o.CustomerNumber

        LEFT JOIN

    (

        SELECT

            Partidas    = count(*),

            Orden        = r.OrderNumber 

        FROM 

            [TORI-FLEX]..tblRelease r WITH (NOLOCK)

        GROUP BY

            r.OrderNumber    

    ) AS Rel

        ON Rel.Orden = o.OrderNumber

WHERE

    o.TransactionType = 'Open Order' AND

    o.ItemStatus      = 'ActiveOrder'

GO