question

rafaelfrancomoreno-7611 avatar image
0 Votes"
rafaelfrancomoreno-7611 asked ·

how to create case and where on sql server?

I have this sp, they ask me to insert a column at the end that says yes or no

if UUID_COMP_17! = '' And Imp_Cargo! = 0 is yes

if UUID_COMP_17 = '' and Imp_Cargo! = 0 is yes

and if UUID_COMP_17 = '' and Imp_Cargo = 0 is no

lines in bold add it but when executing it it shows me errors that the column Proc_Comp that I want to insert is invalid and that the provided values ​​do not match the definition of the table, I did it this way because according to me it is like that maybe it should do it separately as the cases that come there but I don't know how

CREATE PROCEDURE [dbo].[Sp_Consulta_sUP]
@PNumProveedor INT
,@PNumPago INT
,@PFechaIniMovto VARCHAR(8)
,@PFechaFinMovto VARCHAR(8)

AS
BEGIN

  • CONSECUTIVO 000003


  • CONSECUTIVO : 000003

  • BASE DE DATOS : USE AdmRecCFDIProdDB

  • MODULO : Reporte de UUIDS Complementos

  • OBJETIVO : Obtener UUIDS de Complementos


  • COMENTARIOS :



  • CONSECUTIVO AUTOR FECHA MODIFICACION


  • 000001 Victor Hugo Sanchez Morales 2019.09.09 Creacion

  • 000002 Ismael Ruiz Torres 2019.10.07 Modificación Se agregan columnas Nuevas--

  • Solicidas Por el usuario tomando encuenta la nueva columna de complemento 17

  • 000003 Ismael Ruiz Torres 2019.10.21 Modificación Se agregan NUEVOS PARAMETROS
    ---@PNumProveedor INT ,@PNumPago INT,@PFechaIniMovto VARCHAR(8),@PFechaFinMovto VARCHAR(8)


  • Pruebas Unitarias


----1500080095 552521
-- EXEC dbo.Sp_Consulta_sUP 40022,0,'',''
-- EXEC dbo.Sp_Consulta_sUP 0,1500013375,'',''
-- EXEC dbo.Sp_Consulta_sUP 0,0,'20190909','20190909'
-- EXEC dbo.Sp_Consulta_sUP 40022,0,'20190901','20200120'
-- EXEC dbo.Sp_Consulta_sUP 0,1500013375,'20190917','20191017'
-- EXEC dbo.Sp_Consulta_sUP 0,0,'',''

  •          EXEC dbo.Sp_Consulta 552521  
    
    
    

SET NOCOUNT ON -- Inhibe el env-o de DONE_IN_PROC al cliente
SET XACT_ABORT ON


  • VALIDA TABLA PARAMETRO



  • DECLARACION DE VARIABLES


---DECLARE


  • Variable de Trabajo


DECLARE @strParametros VARCHAR(500)


  • CREACION DE TABLAS TEMPORALES



IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#PagoCP_Prov'))
DROP TABLE #PagoCP_Prov

     CREATE TABLE #PagoCP_Prov (                                                                                 
                             Sociedad_Pago  VARCHAR(4) NOT NULL,  
                             Ejercicio_Pago SMALLINT NOT NULL,  
                             Id_Num_Pago    BIGINT NOT NULL,  
                          UUID_Comp      VARCHAR(36) NOT NULL,  
                             Id_Num_Prov    INT NOT NULL,  
                             Fec_Pago       DATETIME NOT NULL,  
                             Imp_Cargo      MONEY    NOT NULL,  
                             Imp_CargoProv  MONEY NOT NULL,  
                             Imp_Fact       MONEY NOT NULL,  
                             Imp_NC         MONEY NOT NULL,  
                             Imp_Total      MONEY NOT NULL,  
                             Bit_NC_OK      BIT NOT NULL,  
                             Id_Estatus     SMALLINT NOT NULL,  
                             Fec_Movto      DATETIME NOT NULL,  
                             UUID_Comp_17   VARCHAR(36) NOT NULL,  
 )  

------------------------------------------------------------------------------------------------

IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#PagoCP_ProvConRS'))
DROP TABLE #PagoCP_ProvConRS

     CREATE TABLE #PagoCP_ProvConRS   
 (   
                             Id_Num_Prov            INTEGER     NOT NULL  
                             ,Cant_Pagos             INTEGER     NOT NULL                                              
                             ,Imp_TotPago            MONEY       NOT NULL  
                             ,Cant_CPRecibido        INTEGER     NOT NULL         
                             ,Imp_TotPago_CPRecibido MONEY       NOT NULL                                                                            
                             ,Id_FEV_RazonSocial         SMALLDATETIME    NOT NULL   
                             ,FSV_RazonSocial        SMALLDATETIME    NOT NULL  
 )  


IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#PagoCP_ProvConRS_vFinal'))
DROP TABLE #PagoCP_ProvConRS_vFinal

     CREATE TABLE #PagoCP_ProvConRS_vFinal (   
                             Id_Num_Prov               INTEGER     NOT NULL  
                             ,Cant_Pagos               INTEGER     NOT NULL                                            
                             ,Imp_TotPago              MONEY       NOT NULL  
                             ,Cant_CPRecibido          INTEGER     NOT NULL       
                             ,Imp_TotPago_CPRecibido   MONEY       NOT NULL                                                                          
                             ,Id_FEV_RazonSocial           SMALLDATETIME   NOT NULL   
                             ,FSV_RazonSocial          SMALLDATETIME    NOT NULL   
                             ,RazonSocial                            VARCHAR(70)  NOT NULL   
                             ,RFC                                    CHAR(13)     NOT NULL                                                                                                    
                             )  

------------------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#PagoCP_ProvTotPagos'))
DROP TABLE #PagoCP_ProvTotPagos

     CREATE TABLE #PagoCP_ProvTotPagos (                                                                           
                             Id_Num_Prov         INTEGER     NOT NULL  
                             , Imp_TotPago         MONEY       NOT NULL  
                             , Cant_Pagos          INTEGER     NOT NULL  
                             )  


IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#PagoCP_ProvResumen'))
DROP TABLE #PagoCP_ProvResumen

     CREATE TABLE #PagoCP_ProvResumen (   
                             Id_Num_Prov               INTEGER     NOT NULL  
                             ,Cant_Pagos               INTEGER     NOT NULL                                            
                             ,Imp_TotPago              MONEY       NOT NULL  
                             ,Cant_CPRecibido          INTEGER     NOT NULL                                                                                       
                             ,Imp_TotPago_CPRecibido   MONEY       NOT NULL                                                                                          
 )  

------------------------------------------------------------------------------------------------

IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#PagoCP_ProvCP'))
DROP TABLE #PagoCP_ProvCP

     CREATE TABLE #PagoCP_ProvCP (                                                                        
 Id_Num_Prov         INTEGER     NOT NULL  
 , Imp_TotPago         MONEY       NOT NULL  
 , Cant_Pagos          INTEGER     NOT NULL                                                               
 )  

IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#PAgoCP_ProvDetalle'))
DROP TABLE #PAgoCP_ProvDetalle

       CREATE TABLE #PAgoCP_ProvDetalle(  
                             Sociedad_Pago  VARCHAR(4) NOT NULL,  
                             Ejercicio_Pago SMALLINT NOT NULL,  
                             Id_Num_Pago    BIGINT NOT NULL,  
                             UUID_Comp      VARCHAR(36) NOT NULL,  
                             Id_Num_Prov    INT NOT NULL,  
                             Fec_Pago       DATETIME NOT NULL,  
                             Imp_Cargo      MONEY    NOT NULL,  
                             Imp_CargoProv  MONEY NOT NULL,  
                             Imp_Fact       MONEY NOT NULL,  
                             Imp_NC         MONEY NOT NULL,  
                             Imp_Total      MONEY NOT NULL,  
                             Bit_NC_OK      BIT NOT NULL,  
                             Id_Estatus     SMALLINT NOT NULL,  
                             Fec_Movto      DATETIME NOT NULL,  
                             UUID_Comp_17   VARCHAR(36) NOT NULL,                                                                        
 )  


IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#Tmp_PagoCP'))
DROP TABLE #Tmp_PagoCP

     CREATE TABLE #Tmp_PagoCP(  
                             Id_Num_Prov2       INTEGER      NULL --Id_Num_Prov     
                             ,Periodo           VARCHAR(12)  NULL --Fec_Pago     
                             ,Cant_CP           INTEGER  NULL   
                             ,Imp_CPRecibido    MONEY        NULL   
                             ,Cant_SinCP        MONEY        NULL   
                             ,Imp_SinCP                           MONEY        NULL  
                             ,Cant_CP_17         INTEGER      NULL   
                             ,Total_Complementos INTEGER      NULL   
                             ,ImporteTot_CP_17   MONEY        NULL   
                        ,Sociedad_Pago      VARCHAR(4)   NOT NULL  
                             ,Ejercicio_Pago     SMALLINT     NOT NULL  
                             ,Id_Num_Pago        BIGINT       NOT NULL  
                             ,UUID_Comp          VARCHAR(36)  NOT NULL  
                             ,Imp_Cargo      MONEY NOT NULL  
                             ,Imp_CargoProv  MONEY NOT NULL  
                             ,Imp_Fact       MONEY NOT NULL  
                             ,Imp_NC         MONEY NOT NULL  
                             ,Imp_Total      MONEY NOT NULL  
                             ,Bit_NC_OK      BIT NOT NULL  
                             ,Id_Estatus     SMALLINT NOT NULL  
                             ,Fec_Movto      DATETIME NOT NULL  
                             ,UUID_Comp_17   VARCHAR(36) NOT NULL

,Proc_Comp varchar(36) not null
)

BEGIN TRY


----Cuerpo PROCEDIMIENTO-----------------------------------------------------------------------


------[4]---[Consultar dbo.CFDI_PagoComp e insertar en -->#PagoCP_Prov]----

--IF(@PNumProveedor <>0 OR @PNumPago <>0 OR @PFechaIniMovto <>'')
--BEGIN

INSERT INTO #PagoCP_Prov
SELECT
Sociedad_Pago
,Ejercicio_Pago
,Id_Num_Pago
,UUID_Comp
,Id_Num_Prov
,Fec_Pago
,Imp_Cargo
,Imp_CargoProv
,Imp_Fact
,Imp_NC
,Imp_Total
,Bit_NC_OK
,Id_Estatus
,Fec_Movto
,UUID_Comp_17
FROM dbo.CFDI_PagoComp cp ( NOLOCK )
WHERE (Id_Num_Prov = @PNumProveedor OR @PNumProveedor = 0)
AND (Id_Num_Pago = @PNumPago OR @PNumPago = 0)
AND (CAST(Fec_Pago AS DATE) BETWEEN @PFechaIniMovto AND @PFechaFinMovto OR @PFechaIniMovto = '' )
GROUP BY Sociedad_Pago ,Ejercicio_Pago ,Id_Num_Pago ,UUID_Comp ,Id_Num_Prov ,Fec_Pago ,Imp_Cargo ,Imp_CargoProv ,Imp_Fact ,Imp_NC ,Imp_Total ,Bit_NC_OK ,Id_Estatus ,Fec_Movto ,UUID_Comp_17

--END ------------------------[FINALIZA]
--ELSE
--BEGIN
--INSERT INTO #PagoCP_Prov
-- SELECT
-- Sociedad_Pago
-- ,Ejercicio_Pago
-- ,Id_Num_Pago
-- ,UUID_Comp
-- ,Id_Num_Prov
-- ,Fec_Pago
-- ,Imp_Cargo
-- ,Imp_CargoProv
-- ,Imp_Fact
-- ,Imp_NC
-- ,Imp_Total
-- ,Bit_NC_OK
-- ,Id_Estatus
-- ,Fec_Movto
-- ,UUID_Comp_17
-- FROM dbo.CFDI_PagoComp cp ( NOLOCK )
-- GROUP BY Sociedad_Pago ,Ejercicio_Pago ,Id_Num_Pago ,UUID_Comp ,Id_Num_Prov ,Fec_Pago ,Imp_Cargo ,Imp_CargoProv ,Imp_Fact ,Imp_NC ,Imp_Total ,Bit_NC_OK ,Id_Estatus ,Fec_Movto ,UUID_Comp_17

--END

------[5]---[Consultar #PagoCP_Prov e insertar en -->#PAgoCP_ProvDetalle]----

INSERT INTO #PAgoCP_ProvDetalle
SELECT
pcp.Sociedad_Pago
,pcp.Ejercicio_Pago
,pcp.Id_Num_Pago
,pcp.UUID_Comp
,pcp.Id_Num_Prov
,pcp.Fec_Pago
,pcp.Imp_Cargo
,pcp.Imp_CargoProv
,pcp.Imp_Fact
,pcp.Imp_NC
,pcp.Imp_Total
,pcp.Bit_NC_OK
,pcp.Id_Estatus
,pcp.Fec_Movto
,pcp.UUID_Comp_17
FROM
#PagoCP_Prov pcp ( NOLOCK )
JOIN AdmCxPProdDB.dbo.pagPago_CFDI p ( NOLOCK ) ON pcp.Id_Num_Prov = p.Id_Num_Prov
AND pcp.Id_Num_Pago = p.Num_Pago
AND pcp.Fec_Pago = p.Fec_Pago
JOIN AdmCxPProdDB.dbo.pagPago pa ( NOLOCK ) ON p.Id_Num_Prov = pa.Id_Num_Prov
AND p.Num_Pago = pa.Id_Num_Pago
AND p.Fec_Pago = pa.Fec_Pago

------[6]---[Consultar #PAgoCP_ProvDetalle e insertar en -->#PagoCP_ProvTotPagos]----
INSERT INTO #PagoCP_ProvTotPagos
SELECT
Id_Num_Prov,
SUM( Imp_Total) Imp_TotPago,
COUNT(*) Cant_Pagos
FROM #PAgoCP_ProvDetalle pd (NOLOCK )
GROUP BY Id_Num_Prov

------[7]---[Consultar #PagoCP_ProvCP e insertar en -->#PAgoCP_ProvDetalle]----
INSERT INTO #PagoCP_ProvCP
SELECT
Id_Num_Prov,
SUM( Imp_Total) Imp_TotPago,
COUNT(*) Cant_Pagos
FROM #PAgoCP_ProvDetalle pd (NOLOCK )
WHERE UUID_Comp != ''
GROUP BY Id_Num_Prov

-----[8]---[Consultar #PagoCP_ProvTotPagos e insertar en -->#PagoCP_ProvResumen]----

INSERT INTO #PagoCP_ProvResumen
SELECT
pt.Id_Num_Prov,
pt.Cant_Pagos,
pt.Imp_TotPago,
ISNULL( psc.Cant_Pagos , 0 ) Cant_CPRecibido,
ISNULL( psc.Imp_TotPago, 0 )
Imp_TotPago_CPRecibido
FROM
#PagoCP_ProvTotPagos pt
LEFT JOIN #PagoCP_ProvCP psc ON pt.Id_Num_Prov = psc.Id_Num_Prov

-----[9]---[Consultar #PagoCP_ProvResumen e insertar en -->#PagoCP_ProvConRS]----

INSERT INTO #PagoCP_ProvConRS
SELECT pcp.Id_Num_Prov,
Cant_Pagos,
Imp_TotPago,
Cant_CPRecibido,
Imp_TotPago_CPRecibido,
MAX( Id_FEV_RazonSocial) Id_FEV_RazonSocial,
MAX( FSV_RazonSocial ) FSV_RazonSocial
FROM #PagoCP_ProvResumen pcp
INNER JOIN FactECorpProdDB.dbo.Prov_RazonSocial_d p WITH (NOLOCK) ON pcp.Id_Num_Prov = p.Id_Num_Prov
and getdate() between Id_FEV_RazonSocial and FSV_RazonSocial
GROUP BY pcp.Id_Num_Prov, Cant_Pagos, Imp_TotPago, Cant_CPRecibido, Imp_TotPago_CPRecibido

-----[10]---[Consultar #PagoCP_ProvConRS e insertar en -->#PagoCP_ProvConRS_vFinal]----

     INSERT INTO #PagoCP_ProvConRS_vFinal                          

SELECT
--pcp.*,
pcp.Id_Num_Prov
,pcp.Cant_Pagos
,pcp.Imp_TotPago
,pcp.Cant_CPRecibido
,pcp.Imp_TotPago_CPRecibido
,pcp.Id_FEV_RazonSocial
,pcp.FSV_RazonSocial
,p.RazonSocial
,p.RFC
FROM
#PagoCP_ProvConRS pcp
INNER JOIN FactECorpProdDB.dbo.Prov_RazonSocial_d p WITH (NOLOCK) ON pcp.Id_Num_Prov = p.Id_Num_Prov
AND pcp.Id_FEV_RazonSocial = p.Id_FEV_RazonSocial
AND pcp.FSV_RazonSocial = p.FSV_RazonSocial
WHERE
GETDATE() +1 BETWEEN pcp.Id_FEV_RazonSocial
AND pcp.FSV_RazonSocial OR pcp.FSV_RazonSocial IS NULL

-----[11]---[Consultar ##PAgoCP_ProvDetalle e insertar en -->#Tmp_PagoCP]----
INSERT INTO #Tmp_PagoCP
SELECT
Id_Num_Prov --Id_Num _Prov
,SUBSTRING( CONVERT( CHAR(08), Fec_Pago, 112 ) , 1, 6 ) Periodo --Fec_Pago
,CASE
WHEN UUID_COMP != '' THEN COUNT()
ELSE 0
END Cant_CP
,CASE
WHEN UUID_COMP !='' THEN SUM( Imp_Total )
ELSE 0
END Imp_CPRecibido
,CASE
WHEN UUID_COMP = '' THEN COUNT(
)
ELSE 0
END Cant_SinCP
,CASE
WHEN UUID_COMP = '' THEN SUM( Imp_Total )
ELSE 0
END Imp_SinCP
,CASE
WHEN UUID_COMP_17 != '' THEN count()
ELSE 0
END Cant_CP_17
,CASE
WHEN UUID_COMP_17 != '' THEN count(
)
ELSE 0
END Total_Complementos
,CASE
WHEN UUID_COMP_17 !='' THEN SUM( Imp_Cargo )
ELSE 0
END ImporteTot_CP_17

,CASE
WHEN UUID_COMP_17 !='' AND Imp_Cargo != 0 THEN 'Si'
ELSE 0
END Proc_Comp ,CASE
WHEN UUID_COMP_17 ='' AND Imp_Cargo != 0 THEN 'Si'
ELSE 0
END Proc_Comp ,CASE
WHEN UUID_COMP_17 ='' AND Imp_Cargo = 0 THEN 'No'
ELSE 0
END Proc_Comp

,Sociedad_Pago
,Ejercicio_Pago
,Id_Num_Pago
,UUID_Comp
,Imp_Cargo
,Imp_CargoProv
,Imp_Fact
,Imp_NC
,Imp_Total
,Bit_NC_OK
,Id_Estatus
,Fec_Movto
,UUID_Comp_17
FROM #PAgoCP_ProvDetalle
GROUP BY Sociedad_Pago ,Ejercicio_Pago ,Id_Num_Pago,UUID_Comp,Id_Num_Prov,SUBSTRING( CONVERT( CHAR(08), Fec_Pago, 112 ) , 1, 6 ),Imp_Cargo,Imp_CargoProv,Imp_Fact,Imp_NC,Imp_Total,Bit_NC_OK,Id_Estatus,Fec_Movto,UUID_Comp_17

-----[12]---[Consultar #Tmp_PagoCP ]----------------------------------------------

             SELECT -- top 100    
                             TMP.Id_Num_Prov2 AS [Núm. de proveedor],   --, TMP.Id_Num_Prov        
                             TMP.Periodo  Periodo                                                                                --, TMP.Fec_Pago     
                             ,SUM( TMP.Cant_CP ) +  SUM( Cant_SinCP ) AS [Total de pagos ]  
                             ,SUM( Cant_SinCP ) AS [Pagos con CP]                                                 
                             ,SUM( TMP.Cant_CP ) AS 'Cant_CPRecibido 03'  
                             ,SUM( TMP.Cant_CP_17)  AS 'Cant.Comp. Recibidos 17'  
                             ,SUM( TMP.Total_Complementos+TMP.Cant_CP )  AS 'Cant.Comp. Recibidos 03 Y 17'                                                                                                
                             ,SUM( TMP.ImporteTot_CP_17)  AS 'Importe Total. 17'                                                
                             ,SUM( TMP.Cant_CP ) * 100 /( SUM( Cant_CP ) +  SUM( Cant_SinCP ) )  AS [Pagos sin CP ]  
                             ,SUM( TMP.Imp_CPRecibido ) + SUM( Imp_SinCP  )  AS [Importe total de pago 03]                        
                             ,SUM( TMP.Imp_CPRecibido )  AS [Importe de CP recibidos]  
                             ,SUM( TMP.Imp_SinCP  ) AS [Importe sin CP]  
                             ,SUM( TMP.Imp_CPRecibido ) * 100 / NULLIF( SUM( Imp_CPRecibido ) + SUM( Imp_SinCP  ),0)                                                                                                                
                             , TMP.Sociedad_Pago        
                             , TMP.Ejercicio_Pago       
                             , TMP.Id_Num_Pago                                  
                             , TMP.Imp_Total          
                             , TMP.Id_Estatus         
                             , TMP.Imp_Cargo       
                             , TMP.Imp_CargoProv   
                             , TMP.Imp_Fact         
                             , TMP.Imp_NC             
                             , TMP.Bit_NC_OK          
                             , TMP.Fec_Movto          
                             ,TMP.UUID_COMP  
                             ,TMP.UUID_COMP_17 

,TMP.Proc_Comp
FROM #Tmp_PagoCP TMP
WHERE --CAST(TMP.Fec_Movto AS DATE) >= '201909'
TMP.Periodo >= '201909'
GROUP BY TMP.Id_Num_Prov2,TMP.Periodo, TMP.Sociedad_Pago,TMP.Ejercicio_Pago,TMP.Id_Num_Pago,TMP.UUID_Comp,TMP.Imp_Total, TMP.Id_Estatus,TMP.Imp_Cargo,TMP.Imp_CargoProv,TMP.Imp_Fact,TMP.Imp_NC,TMP.Bit_NC_OK,TMP.Fec_Movto,UUID_COMP,UUID_COM P_17,TMP.Cant_CP_17,TMP.ImporteTot_CP_17, TMP.Total_Complementos ORDER BY Id_Num_Prov2,Periodo,Id_Num_Pago,Imp_Total,Id_Estatus

SET NOCOUNT OFF


  • FIN


RETURN 0
END TRY
BEGIN CATCH


  • MANEJO DE ERRORES


IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION
END

SELECT @strParametros = '@PNumProveedor=' + CONVERT(VARCHAR,@PNumProveedor)
SELECT @strParametros = + @strParametros + ',@PNumPago=' + CONVERT(VARCHAR,@PNumPago)
SELECT @strParametros = + @strParametros + ',@PFechaIniMovto=' + @PFechaIniMovto
SELECT @strParametros = + @strParametros + ',@PFechaFinMovto=' + @PFechaFinMovto


  • Graba y NO marca Error --


  • EXECUTE CFDI_LogError_iUP @pRaiserror = 0


  • Graba y SI marca Error --


EXECUTE CFDI_LogError_iUP @pRaiserror = 1, @pParametro = @strParametros

END CATCH -- BEGIN CREATE PROCEDURE
END -- BEGIN CREATE PROCEDURE

not-supported
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

DSPatrick avatar image
0 Votes"
DSPatrick answered ·

You'll be much better served by asking subject matter experts in dedicated forum over here.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql

(please don't forget to mark helpful replies as answer)

· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.