Обмен этапами Кибиком WD

view_kibicom_wd_order_diraction
select
    ISNULL(o.idorder,'')as idorder,
    ISNULL(LOWER(convert(varchar(36), o.guid)),'') as order_guid,
    ISNULL(o.name,'') as order_name,
    ISNULL(o.dtcre,'') as order_dtcre,
    ISNULL(o.comment,'') as order_comment,
    ISNULL(o.smbase,0) as order_smbase,

    ISNULL(s.name,'') as seller_name,
    ISNULL(LOWER(convert(varchar(36), s.guid)),'') as seller_guid,

    ISNULL(c.name,'') as customer_name,
    ISNULL(LOWER(convert(varchar(36), c.guid)),'') as customer_guid,

    ISNULL(o.address,'') as address_name,
    ISNULL(LOWER(convert(varchar(36), '')),'') as address_guid,

    ISNULL(man.name,'') as man_name,
    ISNULL(LOWER(convert(varchar(36), man.guid)),'') as man_guid,
    ISNULL(tech.name,'') as tech_name,
    ISNULL(LOWER(convert(varchar(36), tech.guid)),'') as tech_guid,

    (
        SELECT TOP 1 name
        FROM
            dbo.system
        WHERE      
            idsystem IN
            (
                SELECT     idprofsys
                FROM          dbo.orderitem
                WHERE      idorder = o.idorder AND deleted IS NULL
            )
    ) AS profsys_name,

    (
        SELECT TOP 1 name
        FROM
            dbo.system AS system_1
        WHERE
            idsystem IN
            (
                SELECT     idfurnsys
                FROM          dbo.orderitem AS orderitem_1
                WHERE      idorder = o.idorder AND deleted IS NULL
            )
    ) AS furnsys_name
    , d.iddiraction
    , od.idorderdiraction
    , d.name as diraction_name
    , ISNULL(LOWER(convert(varchar(36), d.guid)),'') as diraction_guid
    , od.plandate as diraction_plandate
    , od.factdate as diraction_factdate
    , od.comment as diraction_comment
    , 
    (
        select
            distinct 1
        from orderdiraction
        where  iddiraction=28 and idorder=o.idorder
    ) as is_real_order

from 
    orders o
    left join orderdiraction od on od.idorder=o.idorder and od.deleted is null
    left join diraction d on d.iddiraction=od.iddiraction
    left join
    (
        select  od.idorder, od.idorderdiraction, p.name, p.guid
        from 
            view_orderdiraction od
            left join view_orderdiractionpeople  odp on od.idorderdiraction=odp.idorderdiraction and odp.deleted is null
            left join people p on odp.idpeople=p.idpeople and p.deleted is null
        where 
            od.diraction_name='Менеджер'
            and od.idorder=od.idorder
            and od.deleted is null
    ) man on man.idorder=o.idorder
    left join
    (
        select  od.idorder, od.idorderdiraction, p.name, p.guid
        from 
            view_orderdiraction od
            left join view_orderdiractionpeople  odp on od.idorderdiraction=odp.idorderdiraction  and odp.deleted is null
            left join people p on odp.idpeople=p.idpeople and p.deleted is null
        where 
            od.diraction_name='Замер'
            and od.idorder=od.idorder
            and od.deleted is null

    ) tech on tech.idorder=o.idorder
    left join seller s on o.idseller=s.idseller and s.deleted is null
    left join customer c on o.idcustomer=c.idcustomer and c.deleted is null

where
    o.deleted is null
    and o.idorder=96785
venta-holding / wd_in_work

Обмен этапами Кибиком WD #8
