select * from dbo.GetBalance(@DateTime)

select * from dbo.ViewMonthlyBalance
where dateOperation between @dateStart and @dateEnd

ID int identity( 1 , 1 ) PK, 
UserId int, 
Summ real, 
Operation bit, 
DateOperation datetime default(GetDate())

CREATE TABLE [dbo].[UserBalance](
	[Id] [int] NOT NULL,
	[UserId] [int] NOT NULL,
	[Total] [real] NOT NULL,
	[DateCounting] [datetime] NOT NULL CONSTRAINT [DF_UserBalance_DateCounting]  DEFAULT (getdate()),
 CONSTRAINT [PK_UserBalance] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ALTER function [dbo].[GetBalance] (@userid int, @dateCalc datetime)
returns real
as
begin
   declare @lastTopUp real
   declare @balance real
   declare @withdrawal real
   set @lastTopUp =  0  
   set @withdrawal =  0 
   set @balance =  0 
   declare @lastTopUpDate datetime

   -- получаем последнее пополнение 
   select top  1  
   @lastTopUp = isnull(Summ, 0 ) + isnull(TotalInCurrentDate, 0 ), 
   @lastTopUpDate = DateCreate
   from dbo.UserOperations with (nolock)
   where Operation =  1  and DateCreate <= @dateCalc and UserId = @userid
   order by DateCreate desc

   -- получаем все списания, после последнего пополнения 
   select 
   @withdrawal = isnull(sum(Summ), 0 )
   from dbo.UserOperations with (nolock)
   where Operation =  0  and DateCreate >= @lastTopUpDate and UserId = @userid
   group by Operation 

   -- ну и собственно считаем баланс 
   set @balance = @lastTopUp - @withdrawal
   if @balance is null 
      return  0  
   return @balance
end
 

---

-- списание 
ALTER proc [dbo].[UserPayment]
@userId int,
@amount real 
as
begin 
begin transaction
    declare @balance real
    declare @ok bit 
    set @ok =  0 
    select @balance = dbo.GetBalance (@userId, getdate()) 
    if @balance >  0  and @balance > @amount
    begin
        set @ok =  1  
		INSERT INTO 
		[dbo].[UserOperations]
		   ([UserId]
		   ,[Operation]
		   ,[Summ]
		   ,[TotalInCurrentDate]
		   ,[DateCreate])
		VALUES
		   (@userId
		   , 0 
		   ,@amount
		   ,dbo.GetBalance (@userId, getdate())
		   ,getdate())
     end 
     select @balance = dbo.GetBalance (@userId, getdate()) 
	 update dbo.Users set CurrentBalance = @balance,
     Enabled = case when @balance >  0  then  1  else  0  end 
     where Id = @userid  
     select @balance as [balance], @ok as [executed], 
         case when @ok =  1  then  0  else  1  end as notEnoughMoney
commit transaction
end 
----

--- пополнение 

ALTER proc [dbo].[TopUpAccount]
@userId int,
@amount real 
as
begin 
begin transaction
	INSERT INTO 
	[dbo].[UserOperations]
	   ([UserId]
	   ,[Operation]
	   ,[Summ]
	   ,[TotalInCurrentDate]
	   ,[DateCreate])
	VALUES
	   (@userId
	   , 1 
	   ,@amount
	   ,dbo.GetBalance (@userId, getdate())
	   ,getdate())
     declare @balance real
     select @balance = dbo.GetBalance (@userId, getdate())
     update dbo.Users set CurrentBalance = @balance,
     Enabled = case when @balance >  0  then  1  else  0  end 
     where Id = @userid  
     select @balance
commit transaction
end 
---

CREATE TABLE [dbo].[UserOperations](
	[Id] [int] IDENTITY( 1 , 1 ) NOT NULL,
	[UserId] [int] NULL,
	[Operation] [bit] NOT NULL CONSTRAINT [DF_UserOperations_Operation]  DEFAULT (( 0 )),
	[Summ] [real] NOT NULL,
	[TotalInCurrentDate] [real] NOT NULL,
	[DateCreate] [datetime] NOT NULL CONSTRAINT [DF_UserOperations_DateCreate]  DEFAULT (getdate()),
 CONSTRAINT [PK_UserOperations] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[PbxCommands](
	[Id] [int] NOT NULL,
	[Command] [char]( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL,
	[Rate] [real] NULL,
	[Amount] [real] NULL,
	[CountyId] [int] NULL,
	[FromNumber] [char]( 50 ) COLLATE Cyrillic_General_CI_AS NULL,
	[ToNumber] [char]( 50 ) COLLATE Cyrillic_General_CI_AS NULL,
	[DateCommand] [datetime] NULL,
	[Response] [bit] NULL,
 CONSTRAINT [PK_PbxCommands] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]