if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BAI_DATA_IMPORT_SUMMARY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BAI_DATA_IMPORT_SUMMARY]
GO

CREATE TABLE [dbo].[BAI_DATA_IMPORT_SUMMARY] (
	[BAI_IMPORT_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[POSTING_DATE] [datetime] NOT NULL ,
	[BANK_ACCOUNT_ID] [numeric](18, 0) NOT NULL ,
	[VALUE_DATE] [datetime] NULL ,
	[BALANCE_FLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TRANSACTION_FLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ACCOUNT_CLOSED] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[BAI_COMPUTED_FLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LAST_LOADED_TIMESTAMP] [datetime] NULL 
) ON [PRIMARY]
GO

Procedure

--spBankImportAccountSummary '09/12/2008','09/12/2008'      
      
Alter PROCEDURE spBankImportAccountSummary              
                        (                    
                         @StartPostingDate varchar(10)='',                    
                         @EndPostingDate varchar(10)=''                             
              )                                     
--*******************************************************************************************                                    
--Stored Procedure:          spBankAccountSummary                                    
--Purpose:                     This Procedure will get all the Accounts summary                                  
                      
--Input:                       @PostingDate(varchar)                                    
--Output:                      Data Table                
--Developer:                                                       
--Creation Date: 12 June 2008                    
--*******************************************************************************************                                    
AS                                      
                    
Begin                                    
                    
declare @TempTable TABLE                     
(                    
DateValue Int IDENTITY(1, 1) NOT NULL,                    
LAST_LOADED_TIMESTAMP datetime NOT NULL ,  
BAI_COMPUTED_FLAG char(1),                   
ACCOUNT_NUMBER_RECEIVED int,                    
ACCOUNT_NUMBER_NO_TRANSACTION int,                    
ACCOUNT_NUMBER_NOT_RECEIVED int,                
P int,                
C int                    
)                    
                    
                    
insert into @TempTable(LAST_LOADED_TIMESTAMP,BAI_COMPUTED_FLAG)                    
select distinct LAST_LOADED_TIMESTAMP,BAI_COMPUTED_FLAG                   
from BAI_DATA_IMPORT_SUMMARY                    
where Convert(Varchar,LAST_LOADED_TIMESTAMP,101) >= Convert(Varchar,@StartPostingDate,101)                    
AND Convert(Varchar,LAST_LOADED_TIMESTAMP,101) <= Convert(Varchar,@EndPostingDate,101)                    
                     
declare @Total int                    
declare @i int                    
set @Total =0                    
set @i =1                    
select @Total=count(*) from @TempTable                    
                    
declare @AccountReceived int                    
declare @AccountNoTransaction int                    
declare @AccontNotReceived int                
declare @P int                
declare @C int                    
                    
 while(@i<=@Total)                    
                    
  begin                    
                    
                     
set @AccountReceived = 0                
set @AccountNoTransaction = 0                
set @AccontNotReceived = 0                
set @P = 0                
set @C = 0                        
--select Convert(Varchar,POSTING_DATE,101) FROM @TempTable WHERE DateValue = @i                    
                    
 SELECT @AccountReceived = count(1) -- count(A.BANK_ACCOUNT_ID)                    
 FROM BANK_ACCOUNTS A, BANKS B, BANK_BALANCE_DETAIL C,                               
 BAI_DATA_IMPORT_SUMMARY D WHERE A.BANK_KEY = B.BANK_KEY AND A.BANK_ACCOUNT_ID = C.BANK_ACCOUNT_ID AND                              
 C.BANK_ACCOUNT_ID = D.BANK_ACCOUNT_ID AND A.STATUS_FLAG='A'                             
 AND Convert(Varchar,C.POSTING_DATE,101) = Convert(Varchar,D.POSTING_DATE,101)                             
 AND D.LAST_LOADED_TIMESTAMP = (select LAST_LOADED_TIMESTAMP FROM @TempTable WHERE DateValue = @i)  
 AND D.BAI_COMPUTED_FLAG=(select BAI_COMPUTED_FLAG FROM @TempTable WHERE DateValue = @i)                    
                    
--Select @AccountReceived                    
                    
SELECT @AccountNoTransaction = count(1) --count(A.BANK_ACCOUNT_ID)                    
   FROM BANK_ACCOUNTS A, BANKS B, BANK_BALANCE_DETAIL C,                                 
   BAI_DATA_IMPORT_SUMMARY D WHERE A.BANK_KEY = B.BANK_KEY AND A.BANK_ACCOUNT_ID = C.BANK_ACCOUNT_ID AND                                
   C.BANK_ACCOUNT_ID = D.BANK_ACCOUNT_ID AND Convert(Varchar,C.POSTING_DATE,101)=Convert(Varchar,D.POSTING_DATE,101)                            
   AND A.STATUS_FLAG='A' AND D.BALANCE_FLAG='Y' AND TRANSACTION_FLAG='N' AND                                 
   ACCOUNT_CLOSED = 'N'                      
   AND D.LAST_LOADED_TIMESTAMP = (select LAST_LOADED_TIMESTAMP FROM @TempTable WHERE DateValue = @i)      
 AND D.BAI_COMPUTED_FLAG=(select BAI_COMPUTED_FLAG FROM @TempTable WHERE DateValue = @i)                    
                
                    
--Select @AccountNoTransaction                    
                    
                    
SELECT @AccontNotReceived = count(1) --count(A.BANK_ACCOUNT_ID)                     
   FROM BANK_ACCOUNTS A, BANKS B                    
   WHERE A.BANK_KEY = B.BANK_KEY And A.STATUS_FLAG='A'                          
   And A.BANK_ACCOUNT_ID NOT IN (Select BANK_ACCOUNT_ID From BAI_DATA_IMPORT_SUMMARY WHERE                               
   LAST_LOADED_TIMESTAMP =(select LAST_LOADED_TIMESTAMP FROM @TempTable WHERE DateValue = @i))                      
                    
--SELECT @AccontNotReceived                
/*                
 SELECT @P =  count(A.ACCOUNT_NUMBER)                    
 FROM BANK_ACCOUNTS A, BANKS B, BANK_BALANCE_DETAIL C,                               
 BAI_DATA_IMPORT_SUMMARY D WHERE A.BANK_KEY = B.BANK_KEY AND A.BANK_ACCOUNT_ID = C.BANK_ACCOUNT_ID AND                              
 C.BANK_ACCOUNT_ID = D.BANK_ACCOUNT_ID AND A.STATUS_FLAG='A'                             
 AND Convert(Varchar,C.POSTING_DATE,101) = Convert(Varchar,D.POSTING_DATE,101)                             
 AND D.LAST_LOADED_TIMESTAMP = (select LAST_LOADED_TIMESTAMP FROM @TempTable WHERE DateValue = @i)                    
AND UPPER(D.BAI_COMPUTED_FLAG)='P'                
                
--SELECT @P                    
                
 SELECT @C =  count(A.ACCOUNT_NUMBER)                    
 FROM BANK_ACCOUNTS A, BANKS B, BANK_BALANCE_DETAIL C,                               
 BAI_DATA_IMPORT_SUMMARY D WHERE A.BANK_KEY = B.BANK_KEY AND A.BANK_ACCOUNT_ID = C.BANK_ACCOUNT_ID AND                              
 C.BANK_ACCOUNT_ID = D.BANK_ACCOUNT_ID AND A.STATUS_FLAG='A'                             
 AND Convert(Varchar,C.POSTING_DATE,101) = Convert(Varchar,D.POSTING_DATE,101)                             
 AND D.LAST_LOADED_TIMESTAMP = (select LAST_LOADED_TIMESTAMP FROM @TempTable WHERE DateValue = @i)                    
AND UPPER(D.BAI_COMPUTED_FLAG)='C'                
                
--SELECT @C                
*/
declare @Flag char(1)
select @Flag=BAI_COMPUTED_FLAG FROM @TempTable WHERE DateValue = @i

if(upper(@Flag)='P')
	BEGIN
		SET @P = @AccountReceived
	END

ELSE IF (upper(@Flag)='C')
	BEGIN
		SET @C = @AccountReceived
	END

update @TempTable set ACCOUNT_NUMBER_RECEIVED  = @AccountReceived,                    
        ACCOUNT_NUMBER_NO_TRANSACTION =  @AccountNoTransaction,                    
        ACCOUNT_NUMBER_NOT_RECEIVED =  @AccontNotReceived,                
 P = @P,                
 C = @C                
                  
where DateValue = @i                    
                    
set @i = @i + 1                    
 end                    
                    
--select * from @TempTable                    
select DateValue,LAST_LOADED_TIMESTAMP as POSTING_DATE,ACCOUNT_NUMBER_RECEIVED,ACCOUNT_NUMBER_NO_TRANSACTION,        
ACCOUNT_NUMBER_NOT_RECEIVED,P,C        
from @TempTable      
order by  POSTING_DATE      
        
                    
End