USE [FrontAlma]
GO
/****** Object:  StoredProcedure [dbo].[ex_Calc_Costo_GrupoTarifa_NoCobradas_Fechas]    Script Date: 08/09/2012 11:14:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




/* 
 select * from hteltarg
 declare @r numeric(18,2)
 execute ex_Calc_Costo_GrupoTarifa_NoCobradas_Fechas 'CELULAR','01/01/2006','01/31/2006',@r output
 select @r
*/

ALTER  PROCEDURE [dbo].[ex_Calc_Costo_GrupoTarifa_NoCobradas_Fechas] ( @grupo_tarifa varchar(40),
						    @f_desde      datetime, 
			               	   	    @f_hasta      datetime,
			               	   	    @result       numeric(19,2) output
					  	   )
AS
BEGIN
  declare @id_targ int

  Select @id_targ = dbo.ex_Get_Id_Targ(@grupo_tarifa),
	 @result   = 0

  Select @result = Sum( CASE WHEN carg.id_llam is null THEN COALESCE(llam.costo,0) ELSE 0.00 END )    
    From htellam llam
	Inner join htelexte exte on llam.id_exte = exte.id_exte 
	Inner join htelarea area on llam.id_area = area.id_area 
	Inner join hteltarg targ on area.id_targ = targ.id_targ
	Left join htelcarg carg on llam.id_llam = carg.id_llam and 
				    carg.cobrado = 1
    Where DateDiff(day, @f_desde, llam.fecha) >= 0 	and
	  DateDiff(day, @f_hasta, llam.fecha) <= 0 	and
	  ( @id_targ is null or targ.id_targ = @id_targ )
   
   Select @result = Coalesce(@result,0)             
END















