Albaranes pendientes.

SELECT codcli + ' ' + (case when codemp='001' then 'Lasser' when codemp='003' then 'HiperAntena' when codemp='005' then 'Seguridad' else codemp END) as EMPRESA,
       dvc39 as CLIENTE,
       dvc39 + '-' + (case when codemp='001' then 'Lasser' when codemp='003' then 'HiperAntena' when codemp='005' then 'Seguridad' else codemp END) AS IDEXTERNOEMPRESACLIENTE,
       'Proforma' AS TIPOFACTURA,
       AGENTE1, (case when AGENTE6=0 then '' else LTRIM(STR(AGENTE6,10)) END) as AGENTE6, 
       NALBARAN, 
       NPEDIDO, NALMACEN,
       'Facturacion 1' AS TIPOFACTURACION,
       'Hiper-PVP' AS LISTAPRECIOS,
       FECHAFACTURA, 
       IMPORTEALBARAN
FROM
(
   SELECT C.codemp, C.codcli, V.dvc39,   
          C.codage AS AGENTE1, C.codage6 AS AGENTE6, 
          N.numalb AS NALBARAN, 
          N.numped AS NPEDIDO, N.numalm AS NALMACEN,          
          CONVERT(VARCHAR(10),N.fecha, 103) as FECHAFACTURA, 
          Sum(N.impalb) AS IMPORTEALBARAN
   FROM (RPS.dbo.faalbranc as C
      INNER JOIN RPS.dbo.faalbran as N ON (C.codemp = N.codemp) AND (C.tipcont = N.tipcont) AND (C.numalb = N.numalb) AND (C.codcli = N.codcli))
      INNER JOIN RPS.dbo.faclidv  as V ON (N.codcli = V.codcli) AND (N.codemp = V.codemp)
   WHERE  N.codemp='003' and isnull(N.numfac, '') = '' AND N.fecha>=5/1/2015 AND N.tipoalb<>'3' And N.tipoalb<>'2'
   GROUP BY C.codemp, C.codcli, V.dvc39,
            C.codage, C.codage6, N.numalb, N.numped, N.numalm, N.fecha
) AS SQL1


-- --------------------------------------------------------------------------------------------------------------------------------------- Inmuebles
-- -------------------------------------------------------------------------------------------------------------------------------------
Select  DISTINCT CLIENTE, CONSERJE, GARAJE, ALARMA, CCTV, PCIEXTINCION, PCIDETECCION, MONOXIDO, 
ELECTRICIDAD, ANTENA,PORTERO, VIDEOPORTERO, AIREACONDICIONADO, 
CCTVDVR, CCTVDVRCAPACIDAD, CCTVNCAMARASINTERIORES, CCTVNCAMARASEXTERIORES,
ALARMANCENTRAL, ALARMAMODELO, ALARMANDETECCTORES, ALARMANDETECTINT, ALARMANDETECTEXT, 
PCIEXTINCNBIES, PCIEXTINCGRUPOPRESION, PCIEXTINCCOLUMNASECA, PCIDETECNCENTRALES,
PCIDETECNDETECTORES, PCIDETECNPULSADORES, PCIDETECNSIRENAS, PCIDETECNEMERGENCIAS,
MONOXIDONCENTRALES, MONOXIDONDETECTORES, MONOXIDONCUADROS, MONOXIDONMOTORES, 
PUERTASN, PUERTASTIPO1, PUERTASTIPO2, NVIVIENDAS, NANTENAS, NEXITINTORES,
TIPOCABECERA, MARCA, NEQUIPOSSECUNDARIOS,
FI,TIPOFI, SATELITE, DIGITALCOMUNITARIO, MARCACABECERASATELITE,
TIPOCABLEADO, DISTRIBUCION, TIPO, TECNOLOGIA, NACCESOSGENERALES,
NPLACASSECUNDARIAS, CONSERJERIA,LECTORPROXIMIDAD, MESVENCIMIENTOPCI,
NCABECERAS, NCABECERASSATELITE, MARCAPORTERO, PCIDETECTIPO,PUERTAS
from RPS.dbo.ETL_INMUEBLES 
where isnull(etl_marca, '0') >= '$UETL$'  AND CLIENTE<>''