none
vistas RRS feed

Todas las respuestas

  • este es el ejemplo de la vista.

    -----VISTA 


    CREATE VIEW [sysdba].[ETREPORTECOBROCANALTIEMPOS]
    AS
    SELECT  DISTINCT
    tic.ALTERNATEKEYPREFIX + '-' + tic.ALTERNATEKEYSUFFIX AS Numerodeticket, 
    sus.NUMEROORDEN AS Ndeorden, 
    sus.NUMEROPOSICION AS Ndeposicion, 
    cont.ETTIPOIDENTIFICACION AS TipoIdent, 
    cont.ETNUMEROIDENTIFICACION AS NIdent, 
    CASE WHEN desert.ORDEN IS NOT NULL THEN 'Propenso' ELSE 'No propenso' END AS Desertor, 
    addrtic.address1 + ' ' + addrtic.city AS direccionmodi, 
    CASE WHEN cont.ETTIPOIDENTIFICACION = 'NIT' THEN acc.ACCOUNT ELSE cont.FIRSTNAME END AS Nombre, 
    cont.LASTNAME AS Apellido, ads.ADDRESS1, 
    ads.CITY, 
    ads.STATE, 
    sus.TELSUSCRIPCION AS Telefono, 
    sus.ETZONA AS Zonadeentrega, 
    sus.ETRUTA AS Ruta, 
    cont.MOBILE, 
    sus.CORREOSUSCRIPCION AS Mail, 
    pro.NAME AS Producto, 
    A.MEDIOPAGO AS Mediodepago, 
    tic.AREA AS Tema, 
    tic.CATEGORY AS Categoria, 
    tic.ISSUE AS Asunto, 
    tic.ETDIAGNOSTICO AS Diagnostico, 
    tic.ETSOLUCION AS Solucion, 
    pic.TEXT AS Estado, 
    CONVERT(varchar(max), tic.NOTES) AS ComentarioInterno, 
    pic2.TEXT AS fuente, 
    DATEADD(hour, 0, tic.RECEIVEDDATE) AS FechaCreacion, 
    usercre.USERNAME AS usuariocreacion, 
    DATEADD(hour, 0, tic.MODIFYDATE) AS Fechamodificacion, 
    usermod.USERNAME AS usuariomodifico, 
    Tiempoabierto AS TiempoabiertodelticketDias, 
    D .Tiempocerrado AS TiempocerradodelticketDias, 
    F.[Tiempo abierto en el último paso] AS Tiempoabiertoenelultimopaso, 
    [TiempoCierre últimopaso] AS TiempoCierreultimopaso, 
    m.SECCODEDESC AS ASSIGNEDTONAME, 
    usercerr.SECCODEDESC AS cerradopor, 
    ur.DESCRIPTION AS urgencia, 
    ticaso.ALTERNATEKEYPREFIX + '-' + ticaso.ALTERNATEKEYSUFFIX AS ticketasociado,
    tic.COMPLETEDDATE as completefecha,
    sus.DESCIPCIONETTIPOENTREGA,
    ofe.CODIGOVENDEDOR as CanalVenta,
    acc.ETSEGMENTO as segmento,
    sus.ETDESCRIPCIONEDESTRUCT AS EDICION_ESTRUCTURADA,
    ofe.ETCAMPAIGNSAP AS CAMPAIGNSAP,

    (SELECT        TOP 1 O.CODIGOVENDEDOR 
    FROM sysdba.ETOFERTA O 
    WHERE O.NUMEROORDEN = sus.NUMEROORDEN 
    AND O.NUMEROPOSICION = sus.NUMEROPOSICION
    ORDER BY O.FECHAINICIO) AS CANAL_VENTA

    FROM            sysdba.TICKET AS tic WITH (NOLOCK) 
    INNER JOIN sysdba.CONTACT AS cont WITH (NOLOCK) ON tic.CONTACTID = cont.CONTACTID 
    INNER JOIN sysdba.ACCOUNT AS acc WITH (NOLOCK) ON cont.ACCOUNTID = acc.ACCOUNTID 
    LEFT JOIN sysdba.ETDESERTORES AS desert WITH (NOLOCK) ON desert.CONTACTID = cont.CONTACTID 
    /**LO NUEVO**/ 
    LEFT JOIN sysdba.ADDRESS AS addrtic WITH (NOLOCK) ON addrtic.ADDRESSID = tic.ADDRESSID 
    AND addrtic.ENTITYID = tic.TICKETID 
    /**LO NUEVO**/ 
    INNER JOIN sysdba.ETSUSCRIPCION AS sus WITH (NOLOCK) ON tic.ETSUSCRIPCIONID = sus.ETSUSCRIPCIONID 
    INNER JOIN sysdba.ETSUSCRIPCIONADDRESS AS sua WITH (NOLOCK) ON sus.ETSUSCRIPCIONID = sua.ETSUSCRIPCIONID --//Se cambia a inner y se agrega un UNION para 

    los no existentes LEFT OUTER JOIN

    LEFT JOIN sysdba.ADDRESS AS ads WITH (NOLOCK) ON ads.addressid = sua.ADDRESSID 
    LEFT JOIN sysdba. PRODUCT AS pro WITH (NOLOCK) ON pro.PRODUCTID = sus.PRODUCTID 
    LEFT OUTER JOIN sysdba.PICKLIST pic WITH (NOLOCK) ON tic.STATUSCODE = pic.ITEMID 
    LEFT OUTER JOIN sysdba.PICKLIST AS pic2 WITH (NOLOCK) ON pic2.ITEMID = tic.VIACODE 
    INNER JOIN sysdba.USERINFO AS usercre WITH (NOLOCK) ON tic.CREATEUSER = usercre.USERID 
    INNER JOIN sysdba.USERINFO AS usermod WITH (NOLOCK) ON tic.MODIFYUSER = usermod.USERID 
    LEFT JOIN sysdba.seccode AS usercerr WITH (NOLOCK) ON tic.COMPLETEDBYID = usercerr.SECCODEID 
    LEFT OUTER JOIN sysdba.TICKET ticaso WITH (NOLOCK) ON tic.ETTICKETASOCIADOID = ticaso.TICKETID 
    LEFT OUTER JOIN (SELECT ofe.MEDIOPAGO, ofe.ETSUSCRIPCIONID, ofe.ETOFERTAID
    FROM sysdba.ETOFERTA AS ofe WITH (NOLOCK)
    WHERE ofe.PRINCIPAL = 'X') AS A ON sus.ETOFERTAID = A.ETOFERTAID 
    /*left outer join (select MAX(en.FECHAENTREGA)as fecha,en.ETOFERTAID
    from sysdba.ETENVIO en
    where en.PRINCIPAL = 'X'
    and en.CERTIFICADO = 'F'
    group by en.ETOFERTAID)as enf on sus.ETOFERTAID = enf.ETOFERTAID*/ 
    --Se comentarea ya que la Zona y ruta se toma de la suscripcion
    /*LEFT OUTER JOIN (SELECT env.ETOFERTAID, env.ZONAENTREGA, env.RUTA, env.SUBPRODUCTO, env.FECHAENTREGA
    FROM sysdba.ETENVIO AS env
    WHERE env.PRINCIPAL = 'X' AND env.CERTIFICADO = 'F' AND env.DESCRIPCION = '') AS B 
    ON sus.ETOFERTAID = B.ETOFERTAID AND pro.NAME = b.SUBPRODUCTO /*and enf.fecha = b.FECHAENTREGA*/ */LEFT OUTER JOIN

    (SELECT DATEDIFF(day, RECEIVEDDATE, getdate()) AS Tiempoabierto, assigneddate, neededbydate, TICKETID
    FROM sysdba.TICKET WITH (NOLOCK) 
    INNER JOIN sysdba.PICKLIST AS P WITH (NOLOCK) ON P.ITEMID = TICKET.STATUSCODE
    WHERE p.TEXT = 'Abierto') AS C ON tic.TICKETID = C.TICKETID 
    LEFT OUTER JOIN (SELECT DATEDIFF(day, RECEIVEDDATE, COMPLETEDDATE) AS Tiempocerrado, TICKETID
    FROM sysdba.TICKET WITH (NOLOCK) 
    INNER JOIN sysdba.PICKLIST AS P WITH (NOLOCK) ON P.ITEMID = TICKET.STATUSCODE
    WHERE p.TEXT = 'Cerrado') AS D ON tic.TICKETID = D .TICKETID 
    LEFT OUTER JOIN (SELECT DATEDIFF(day, DATEADD(hour, 0, MAX(ACTUALSTART)), GETDATE()) 
    AS [Tiempo abierto en el último paso], ENTITYID
    FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK)
    WHERE STATUS = 'Active'
    GROUP BY ENTITYID) AS F ON tic.TICKETID = F.ENTITYID 
    LEFT OUTER JOIN (SELECT MAX(DATEDIFF(day, DATEADD(hour, 0, ACTUALSTART), 
    ACTUALEND)) AS [TiempoCierre últimopaso], ENTITYID
    FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK)
    WHERE STATUS = 'Completed'
    GROUP BY ENTITYID) AS G ON tic.TICKETID = G.ENTITYID 
    LEFT JOIN (SELECT ASSIGNEDTOID FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK)
    WHERE ACTUALEND = (SELECT max(ACTUALEND) FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK))) AS R 
    ON tic.TICKETID = G.ENTITYID 
    LEFT JOIN (SELECT ASSIGNEDTOID FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK) 
    WHERE ACTUALEND = (SELECT max(ACTUALEND)
    FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK))) AS RR 
    ON tic.TICKETID = F.ENTITYID 
    LEFT JOIN sysdba.SECCODE S WITH (NOLOCK) ON R.ASSIGNEDTOID = s.seccodeid 
    LEFT JOIN sysdba.SECCODE K WITH (NOLOCK) ON RR.ASSIGNEDTOID = K.seccodeid 
    LEFT OUTER JOIN sysdba.SECCODE M WITH (NOLOCK) ON tic.ASSIGNEDTOID = m.SECCODEID 
    LEFT OUTER JOIN sysdba.URGENCY AS ur WITH (nolock) ON tic.URGENCYID = ur.URGENCYID 
    LEFT OUTER JOIN sysdba.ETOFERTA AS ofe ON ofe.ETOFERTAID = (SELECT top 1 ETOFERTAID --Join para obtener el primer canal de venta sociado
    FROM sysdba.ETOFERTA 
    where NUMEROORDEN = sus.NUMEROORDEN
    and NUMEROPOSICION = sus.NUMEROPOSICION
    and convert(date,tic.CREATEDATE) 
    between convert(date,FECHAINICIO)  and convert(date,FECHAFIN) 
    )
    WHERE sua.TIPO = 'Entrega'
    AND sua.PRINCIPAL = 'X' 
    AND tic.RECEIVEDDATE >= '2015-01-01 00:00:00'
    --and tic.ALTERNATEKEYPREFIX + '-' + tic.ALTERNATEKEYSUFFIX  ='001-00-207408'

    UNION  all

    SELECT  DISTINCT
    tic.ALTERNATEKEYPREFIX + '-' + tic.ALTERNATEKEYSUFFIX AS Numerodeticket, 
    '' AS Ndeorden, '' AS Ndeposicion, 
    cont.ETTIPOIDENTIFICACION AS TipoIdent, 
    cont.ETNUMEROIDENTIFICACION AS NIdent, 
    CASE WHEN desert.ORDEN IS NOT NULL THEN 'Propenso' ELSE 'No propenso' END AS Desertor, 
    addrtic.address1 + ' ' + addrtic.city AS direccionmodi, 
    CASE WHEN cont.ETTIPOIDENTIFICACION = 'NIT' THEN acc.ACCOUNT ELSE cont.FIRSTNAME END AS Nombre, 
    cont.LASTNAME AS Apellido, 
    '' AS ADDRESS1, 
    '' AS CITY, 
    '' AS State, 
    '' AS Telefono, 
    '' AS Zonadeentrega, 
    '' AS Ruta, 
    '' AS MOBILE, 
    '' AS Mail, 
    '' AS Producto, 
    '' AS Mediodepago, 
    tic.AREA AS Tema, 
    tic.CATEGORY AS Categoria, 
    tic.ISSUE AS Asunto, 
    tic.ETDIAGNOSTICO AS Diagnostico, 
    tic.ETSOLUCION AS Solucion, 
    pic.TEXT AS Estado, 
    CONVERT(varchar(max), tic.NOTES) AS ComentarioInterno, 
    pic2.TEXT AS fuente, 
    DATEADD(hour, 0, tic.RECEIVEDDATE) AS FechaCreacion, 
    usercre.USERNAME AS usuariocreacion, 
    DATEADD(hour, 0, tic.MODIFYDATE) AS Fechamodificacion, 
    usermod.USERNAME AS usuariomodifico, 
    Tiempoabierto AS TiempoabiertodelticketDias, 
    D .Tiempocerrado AS TiempocerradodelticketDias, 
    F.[Tiempo abierto en el último paso] AS Tiempoabiertoenelultimopaso, 
    [TiempoCierre últimopaso] AS TiempoCierreultimopaso, 
    m.SECCODEDESC AS ASSIGNEDTONAME, 
    usercerr.SECCODEDESC AS cerradopor, 
    ur.DESCRIPTION AS urgencia, 
    ticaso.ALTERNATEKEYPREFIX + '-' + ticaso.ALTERNATEKEYSUFFIX AS ticketasociado,
    tic.COMPLETEDDATE as completefecha,
    '' as DESCIPCIONETTIPOENTREGA,
    '' as CanalVenta,
    acc.ETSEGMENTO as segmento,
    '' AS EDICION_ESTRUCTURADA,
    '' AS CAMPAIGNSAP,
    '' AS CANAL_VENTA
    FROM sysdba.PICKLIST AS pic WITH (NOLOCK) 
    INNER JOIN sysdba.TICKET AS tic WITH (NOLOCK) ON pic.ITEMID = tic.STATUSCODE 
    AND tic.ETSUSCRIPCIONID is null
    INNER JOIN sysdba.CONTACT AS cont WITH (NOLOCK) ON tic.CONTACTID = cont.CONTACTID 
    INNER JOIN sysdba.ACCOUNT AS acc WITH (NOLOCK) ON cont.ACCOUNTID = acc.ACCOUNTID 
    LEFT JOIN sysdba.ETDESERTORES AS desert WITH (NOLOCK) ON desert.CONTACTID = cont.CONTACTID 
    /**LO NUEVO**/ 
    LEFT JOIN sysdba.ADDRESS AS addrtic WITH (NOLOCK) ON addrtic.ADDRESSID = tic.ADDRESSID AND addrtic.ENTITYID = tic.TICKETID 
    /**LO NUEVO**/ 
    INNER JOIN sysdba.USERINFO AS usercre WITH (NOLOCK) ON tic.CREATEUSER = usercre.USERID 
    INNER JOIN sysdba.USERINFO AS usermod WITH (NOLOCK) ON tic.MODIFYUSER = usermod.USERID 
    LEFT JOIN sysdba.ETTICKETCOBROCANAL AS tco WITH (NOLOCK) ON tco.TICKETID = tic.TICKETID 
    LEFT JOIN sysdba.TICKET tic2 WITH (NOLOCK) ON tic2.TICKETID = tco.TICKETORIGENID 
    LEFT OUTER JOIN sysdba.PICKLIST AS pic2 WITH (NOLOCK) ON pic2.ITEMID = tic.VIACODE 
    LEFT OUTER JOIN sysdba.seccode AS usercerr WITH (NOLOCK) ON tic.COMPLETEDBYID = usercerr.SECCODEID 
    LEFT OUTER JOIN sysdba.TICKET ticaso WITH (NOLOCK) ON tic.ETTICKETASOCIADOID = ticaso.TICKETID 
    LEFT JOIN (SELECT DISTINCT max(DATEDIFF(day, RECEIVEDDATE, getdate())) AS Tiempoabierto, TICKETID
    FROM sysdba.TICKET WITH (NOLOCK) 
    INNER JOIN sysdba.PICKLIST AS P ON P.ITEMID = TICKET.STATUSCODE
    WHERE p.TEXT = 'Abierto'
    GROUP BY TICKETID) AS C ON tic.TICKETID = C.TICKETID 
    LEFT JOIN (SELECT DISTINCT DATEDIFF(day, RECEIVEDDATE, COMPLETEDDATE) AS Tiempocerrado, TICKETID
    FROM sysdba.TICKET WITH (NOLOCK) 
    INNER JOIN sysdba.PICKLIST AS P WITH (NOLOCK) ON P.ITEMID = TICKET.STATUSCODE
    WHERE p.TEXT = 'Cerrado') AS D ON tic.TICKETID = D .TICKETID 
    LEFT JOIN (SELECT DISTINCT MAX(DATEDIFF(day, DATEADD(hour, 0, ACTUALSTART), GETDATE())) AS [Tiempo abierto en el último paso], ENTITYID
    /*,ASSIGNEDTOID*/ FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK)
    WHERE STATUS = 'Active'
    GROUP BY ENTITYID, ASSIGNEDTOID) AS F ON tic.TICKETID = F.ENTITYID 
    LEFT JOIN (SELECT DISTINCT MAX(DATEDIFF(day, DATEADD(hour, 0, ACTUALSTART), ACTUALEND)) AS [TiempoCierre últimopaso], ENTITYID
    FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK)
    WHERE STATUS = 'Completed'
    GROUP BY ENTITYID) AS G ON tic.TICKETID = G.ENTITYID 
    LEFT JOIN (SELECT ASSIGNEDTOID FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK)
    WHERE STATUS = 'Completed' AND ACTUALEND = (SELECT max(ACTUALEND)
    FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK))) AS R ON tic.TICKETID = G.ENTITYID 
    LEFT JOIN (SELECT ASSIGNEDTOID FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK) WHERE STATUS = 'Active' AND ACTUALEND =
    (SELECT max(ACTUALEND) FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK))) AS RR ON tic.TICKETID = F.ENTITYID 
    LEFT JOIN sysdba.SECCODE K WITH (NOLOCK) ON RR.ASSIGNEDTOID = K.seccodeid 
    LEFT JOIN sysdba.SECCODE S WITH (NOLOCK) ON R.ASSIGNEDTOID = s.seccodeid 
    LEFT OUTER JOIN sysdba.SECCODE M WITH (NOLOCK) ON tic.ASSIGNEDTOID = m.SECCODEID 
    LEFT OUTER JOIN sysdba.URGENCY AS ur WITH (nolock) ON tic.URGENCYID = ur.URGENCYID
    WHERE tic.TICKETID NOT IN (SELECT c.TICKETID FROM sysdba.ETSUSCRIPCION AS a WITH (NOLOCK) 
    INNER JOIN sysdba.TICKET AS c WITH (NOLOCK) ON a.ETSUSCRIPCIONID = c.ETSUSCRIPCIONID) 
    AND tic.RECEIVEDDATE >= '2015-01-01 00:00:00'
    -- and tic.ALTERNATEKEYPREFIX + '-' + tic.ALTERNATEKEYSUFFIX  ='001-00-207408'
    --Se agrega UNION para mostrar los registros que no cruzan con ETSUSCRIPCIONADDRESS

    UNION ALL

    SELECT DISTINCT
    tic.ALTERNATEKEYPREFIX + '-' + tic.ALTERNATEKEYSUFFIX AS Numerodeticket, 
    sus.NUMEROORDEN AS Ndeorden, 
    sus.NUMEROPOSICION AS Ndeposicion, 
    cont.ETTIPOIDENTIFICACION AS TipoIdent, 
    cont.ETNUMEROIDENTIFICACION AS NIdent, 
    CASE WHEN desert.ORDEN IS NOT NULL THEN 'Propenso' ELSE 'No propenso' END AS Desertor, 
    addrtic.address1 + ' ' + addrtic.city AS direccionmodi, 
    CASE WHEN cont.ETTIPOIDENTIFICACION = 'NIT' THEN acc.ACCOUNT ELSE cont.FIRSTNAME END AS Nombre, 
    cont.LASTNAME AS Apellido, 
    '' as ADDRESS1, 
    '' as CITY, 
    '' as STATE, 
    sus.TELSUSCRIPCION AS Telefono, 
    sus.ETZONA AS Zonadeentrega, 
    sus.ETRUTA AS Ruta, 
    cont.MOBILE, 
    sus.CORREOSUSCRIPCION AS Mail, 
    pro.NAME AS Producto, 
    A.MEDIOPAGO AS Mediodepago, 
    tic.AREA AS Tema, 
    tic.CATEGORY AS Categoria, 
    tic.ISSUE AS Asunto, 
    tic.ETDIAGNOSTICO AS Diagnostico, 
    tic.ETSOLUCION AS Solucion, 
    pic.TEXT AS Estado, 
    CONVERT(varchar(max), tic.NOTES) AS ComentarioInterno, 
    pic2.TEXT AS fuente, 
    DATEADD(hour, 0, tic.RECEIVEDDATE) AS FechaCreacion, 
    usercre.USERNAME AS usuariocreacion, 
    DATEADD(hour, 0, tic.MODIFYDATE) AS Fechamodificacion, 
    usermod.USERNAME AS usuariomodifico, 
    Tiempoabierto AS TiempoabiertodelticketDias, 
    D.Tiempocerrado AS TiempocerradodelticketDias, 
    F.[Tiempo abierto en el último paso] AS Tiempoabiertoenelultimopaso, 
    [TiempoCierre últimopaso] AS TiempoCierreultimopaso, 
    m.SECCODEDESC AS ASSIGNEDTONAME, 
    usercerr.SECCODEDESC AS cerradopor, 
    ur.DESCRIPTION AS urgencia, 
    ticaso.ALTERNATEKEYPREFIX + '-' + ticaso.ALTERNATEKEYSUFFIX AS ticketasociado,
    tic.COMPLETEDDATE as completefecha,
    sus.DESCIPCIONETTIPOENTREGA,
    ofe.CODIGOVENDEDOR as CanalVenta,
    acc.ETSEGMENTO as segmento,
    sus.ETDESCRIPCIONEDESTRUCT AS EDICION_ESTRUCTURADA,
    ofe.ETCAMPAIGNSAP AS CAMPAIGNSAP,

    (SELECT        TOP 1 O.CODIGOVENDEDOR 
    FROM sysdba.ETOFERTA O 
    WHERE O.NUMEROORDEN = sus.NUMEROORDEN 
    AND O.NUMEROPOSICION = sus.NUMEROPOSICION
    ORDER BY O.FECHAINICIO) AS CANAL_VENTA

    FROM sysdba.TICKET AS tic WITH (NOLOCK) 
    INNER JOIN sysdba.CONTACT AS cont WITH (NOLOCK) ON tic.CONTACTID = cont.CONTACTID 
    INNER JOIN sysdba.ACCOUNT AS acc WITH (NOLOCK) ON cont.ACCOUNTID = acc.ACCOUNTID 
    LEFT JOIN sysdba.ETDESERTORES AS desert WITH (NOLOCK) ON desert.CONTACTID = cont.CONTACTID 
    /**LO NUEVO**/ 
    LEFT JOIN sysdba.ADDRESS AS addrtic WITH (NOLOCK) ON addrtic.ADDRESSID = tic.ADDRESSID AND addrtic.ENTITYID = tic.TICKETID 
    /**LO NUEVO**/ 
    INNER JOIN sysdba.ETSUSCRIPCION AS sus WITH (NOLOCK) ON sus.ETSUSCRIPCIONID = tic.ETSUSCRIPCIONID 
    LEFT JOIN sysdba.ETSUSCRIPCIONADDRESS AS sua WITH (NOLOCK) ON sus.ETSUSCRIPCIONID = sua.ETSUSCRIPCIONID
    --LEFT JOIN sysdba.ADDRESS AS ads WITH (NOLOCK) ON ads.addressid = sua.ADDRESSID 
    LEFT JOIN sysdba.PRODUCT AS pro WITH (NOLOCK) ON pro.PRODUCTID = sus.PRODUCTID 
    LEFT JOIN sysdba.PICKLIST pic WITH (NOLOCK) ON tic.STATUSCODE = pic.ITEMID 
    LEFT JOIN sysdba.PICKLIST AS pic2 WITH (NOLOCK) ON pic2.ITEMID = tic.VIACODE 
    INNER JOIN sysdba.USERINFO AS usercre WITH (NOLOCK) ON tic.CREATEUSER = usercre.USERID 
    INNER JOIN sysdba.USERINFO AS usermod WITH (NOLOCK) ON tic.MODIFYUSER = usermod.USERID 
    LEFT JOIN sysdba.seccode AS usercerr WITH (NOLOCK) ON tic.COMPLETEDBYID = usercerr.SECCODEID 
    LEFT OUTER JOIN sysdba.TICKET ticaso WITH (NOLOCK) ON tic.ETTICKETASOCIADOID = ticaso.TICKETID 
    LEFT OUTER JOIN (SELECT ofe.MEDIOPAGO, ofe.ETSUSCRIPCIONID, ofe.ETOFERTAID
    FROM sysdba.ETOFERTA AS ofe WITH (NOLOCK)
    WHERE ofe.PRINCIPAL = 'X') AS A ON sus.ETOFERTAID = A.ETOFERTAID 
    /*left outer join (select MAX(en.FECHAENTREGA)as fecha,en.ETOFERTAID from sysdba.ETENVIO en
    where en.PRINCIPAL = 'X' and en.CERTIFICADO = 'F' group by en.ETOFERTAID)as enf on sus.ETOFERTAID = enf.ETOFERTAID*/ 
    --Se comentarea ya que la Zona y ruta se toma de la suscripcion
    /*LEFT OUTER JOIN (SELECT        env.ETOFERTAID, env.ZONAENTREGA, env.RUTA, env.SUBPRODUCTO, env.FECHAENTREGA
    FROM            sysdba.ETENVIO AS env
    WHERE        env.PRINCIPAL = 'X' AND env.CERTIFICADO = 'F' AND env.DESCRIPCION = '') AS B ON sus.ETOFERTAID = B.ETOFERTAID AND 
    pro.NAME = b.SUBPRODUCTO /*and enf.fecha = b.FECHAENTREGA*/ */
    LEFT OUTER JOIN (SELECT DATEDIFF(day, RECEIVEDDATE, getdate()) AS Tiempoabierto, assigneddate, neededbydate, TICKETID
    FROM sysdba.TICKET WITH (NOLOCK) 
    INNER JOIN sysdba.PICKLIST AS P WITH (NOLOCK) ON P.ITEMID = TICKET.STATUSCODE
    WHERE p.TEXT = 'Abierto') AS C ON tic.TICKETID = C.TICKETID 
    LEFT OUTER JOIN (SELECT DATEDIFF(day, RECEIVEDDATE, COMPLETEDDATE) AS Tiempocerrado, TICKETID
    FROM sysdba.TICKET WITH (NOLOCK) INNER JOIN
    sysdba.PICKLIST AS P WITH (NOLOCK) ON P.ITEMID = TICKET.STATUSCODE
    WHERE p.TEXT = 'Cerrado') AS D ON tic.TICKETID = D .TICKETID 
    LEFT OUTER JOIN (SELECT DATEDIFF(day, DATEADD(hour, 0, MAX(ACTUALSTART)), GETDATE()) AS [Tiempo abierto en el último paso], ENTITYID
    FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK)
    WHERE STATUS = 'Active'
    GROUP BY ENTITYID) AS F ON tic.TICKETID = F.ENTITYID 
    LEFT OUTER JOIN (SELECT MAX(DATEDIFF(day, DATEADD(hour, 0, ACTUALSTART), ACTUALEND)) AS [TiempoCierre últimopaso], ENTITYID
    FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK)
    WHERE STATUS = 'Completed'
    GROUP BY ENTITYID) AS G ON tic.TICKETID = G.ENTITYID 
    LEFT JOIN (SELECT ASSIGNEDTOID FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK)
    WHERE ACTUALEND =(SELECT   max(ACTUALEND)
    FROM   sysdba.SLXWFENTITYNODE WITH (NOLOCK))) AS R ON tic.TICKETID = G.ENTITYID 
    LEFT JOIN (SELECT ASSIGNEDTOID FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK)
    WHERE ACTUALEND =(SELECT   max(ACTUALEND)
    FROM sysdba.SLXWFENTITYNODE WITH (NOLOCK))) AS RR ON tic.TICKETID = F.ENTITYID 
    LEFT JOIN sysdba.SECCODE S WITH (NOLOCK) ON R.ASSIGNEDTOID = s.seccodeid 
    LEFT JOIN sysdba.seccode K WITH (NOLOCK) ON RR.ASSIGNEDTOID = K.seccodeid 
    LEFT OUTER JOIN sysdba.seccode M WITH (NOLOCK) ON tic.ASSIGNEDTOID = m.SECCODEID 
    LEFT OUTER JOIN sysdba.URGENCY AS ur WITH (nolock) ON tic.URGENCYID = ur.URGENCYID 
    LEFT OUTER JOIN sysdba.ETOFERTA AS ofe ON ofe.ETOFERTAID = (SELECT top 1 ETOFERTAID --Join para obtener el primer canal de venta sociado
    FROM sysdba.ETOFERTA 
    where NUMEROORDEN = sus.NUMEROORDEN
    and NUMEROPOSICION = sus.NUMEROPOSICION
    and convert(date,tic.CREATEDATE) between convert

    (date,FECHAINICIO)  and convert(date,FECHAFIN) 
    )
    WHERE        tic.RECEIVEDDATE >= '2015-01-01 00:00:00' and sua.ETSUSCRIPCIONID is null
    --and tic.ALTERNATEKEYPREFIX + '-' + tic.ALTERNATEKEYSUFFIX  ='001-00-207408'






    GO

    lunes, 16 de julio de 2018 15:36
  • A que se refiere cuando escribe: almacenarla en un storeprocedure
    lunes, 16 de julio de 2018 18:18