assignment1homework2.txt

create table [seemap_A16028].[CustomerInfo]
(
	CustId int identity(1,1) primary key,
	CustName varchar(150),		
	DateOfBirth	date,
	Gender char(10),
	CustAddress	varchar(2000)	
)
select * from [seemap_A16028].[CustomerInfo]

drop table [seemap_A16028].[CustomerInfo]
drop table [seemap_A16028].[BranchInfo]
drop table [seemap_A16028].[CustomerBranchMap]
drop table [seemap_A16028].[CustomerStatus]
drop table [seemap_A16028].[TransactionInfo]
drop table [seemap_A16028].[Holding]

alter table [seemap_A16028].[CustomerBranchMap]
drop constraint tblCustomerBranchMap_CustId_FK
alter table [seemap_A16028].[CustomerBranchMap] 
drop constraint tblCustomerBranchMap_BranchId_FK
alter table [seemap_A16028].[CustomerStatus] 
drop constraint tblCustomerStatus_CustId_FK
alter table [seemap_A16028].[TransactionInfo] 
drop constraint tblTransactionInfo_CustId_FK
alter table [seemap_A16028].[Holding] 
drop constraint tblHolding_CustId_FK

create table [seemap_A16028].[BranchInfo]
(
	BranchId int identity(1,1) primary key,
	BranchName varchar(150),		
	BranchAddress varchar(2000)	
)
select * from [seemap_A16028].[BranchInfo]

create table [seemap_A16028].[CustomerBranchMap]
(
	CustId int ,
	BranchId int ,
	BranchName varchar(150),		
	BranchAddress varchar(2000)	
)
select * from [seemap_A16028].[CustomerBranchMap]

alter table [seemap_A16028].[CustomerBranchMap] 
add constraint tblCustomerBranchMap_CustId_FK
Foreign key (CustId) references [seemap_A16028].[CustomerInfo](CustId)

alter table [seemap_A16028].[CustomerBranchMap] 
add constraint tblCustomerBranchMap_BranchId_FK
Foreign key (BranchId) references [seemap_A16028].[BranchInfo](BranchId)

create table [seemap_A16028].[CustomerStatus]
(
	CustId int ,
	SatusId int ,		
	FromDate date,
	ToDate date
	
)
select * from [seemap_A16028].[CustomerStatus]
drop table [seemap_A16028].[CustomerStatus]


alter table [seemap_A16028].[CustomerStatus] 
add constraint tblCustomerStatus_CustId_FK
Foreign key (CustId) references [seemap_A16028].[CustomerInfo](CustId)

create table [seemap_A16028].[TransactionInfo]
(
	CustId int ,
	TransactionId int identity(1,1) primary key,		
	TransactionDate date,
	TransactionType varchar(100) Not Null, CHECK (TransactionType in ('Debit','Credit')),
	Amount money,
	TransactionDescription text	
)
select * from [seemap_A16028].[TransactionInfo]

alter table [seemap_A16028].[TransactionInfo] 
add constraint tblTransactionInfo_CustId_FK
Foreign key (CustId) references [seemap_A16028].[CustomerInfo](CustId)

create table [seemap_A16028].[Holding]
(
	CustId int ,
	HoldingDate date,
	Amount money,
)
select * from [seemap_A16028].[Holding]

alter table [seemap_A16028].[Holding] 
add constraint tblHolding_CustId_FK
Foreign key (CustId) references [seemap_A16028].[CustomerInfo](CustId)

drop table [seemap_A16028].[Holding]
drop table [seemap_A16028].[TransactionInfo] 

/*
CustomerInfo
	CustId  CustName  DateOfBirth  Gender  Address
	1		A			1-1-96		M		A
	2		B			1-2-96		F		B
	3		c			1-3-96		M		C
	4		D			1-4-96		F		D
	5		E			1-5-96		M		E
	6		F			1-6-96		F		F
	7		G			1-7-96		M		G
	8		H			1-8-96		F		H

*/
insert into [seemap_A16028].[CustomerInfo](CustName,DateOfBirth,Gender,CustAddress)
values ('A','1996-01-01','M','A1')
insert into [seemap_A16028].[CustomerInfo](CustName,DateOfBirth,Gender,CustAddress)
values ('B','1996-02-01','F','B1')
insert into [seemap_A16028].[CustomerInfo](CustName,DateOfBirth,Gender,CustAddress)
values ('C','1996-03-01','M','C1')
insert into [seemap_A16028].[CustomerInfo](CustName,DateOfBirth,Gender,CustAddress)
values ('D','1996-04-01','F','D1')
insert into [seemap_A16028].[CustomerInfo](CustName,DateOfBirth,Gender,CustAddress)
values ('E','1996-05-01','M','E1')
insert into [seemap_A16028].[CustomerInfo](CustName,DateOfBirth,Gender,CustAddress)
values ('F','1996-06-01','F','F1')
insert into [seemap_A16028].[CustomerInfo](CustName,DateOfBirth,Gender,CustAddress)
values ('G','1996-07-01','M','G1')
insert into [seemap_A16028].[CustomerInfo](CustName,DateOfBirth,Gender,CustAddress)
values ('H','1996-08-01','F','H1')
/*
BranchInfo
	BranchId  BranchName  Address
	1			Vashi		Vashi
	2			Naigaon		Naigaon
	3			Sanpada		Sanapada
	4			Chembur		Chembur	
*/
insert into [seemap_A16028].[BranchInfo](BranchName,BranchAddress)
values ('Vashi','Vashi')
insert into [seemap_A16028].[BranchInfo](BranchName,BranchAddress)
values ('Naigaon','Naigaon')
insert into [seemap_A16028].[BranchInfo](BranchName,BranchAddress)
values ('Sanpada','Sanpada')
insert into [seemap_A16028].[BranchInfo](BranchName,BranchAddress)
values ('Chembur','Chembur')

/*
CustomerBranchMap
	CustId  BranchId  FromDate  ToDate
	1		1			1-4-16		Null		//then this row's ToDate changes to 30-6-16 
	2		1			1-2-16		Null
	1		3			1-7-16		Null		//when this happens
*/
insert into [seemap_A16028].[CustomerBranchMap](CustId,BranchId,BranchName,BranchAddress)
values (1,1,'2016-04-01',Null)
insert into [seemap_A16028].[CustomerBranchMap](CustId,BranchId,BranchName,BranchAddress)
values (2,1,'2016-02-01',Null)

/*
CustomerStatus
	CustId  StatusId  FromDate  ToDate
	1		active	  1-4-16	Null	//this rows todate changes to 30-4-16
	2       active    1-2-16	Null
	1		suspend	  1-5-16    Null	//when this happens		//it changes to 31-5-16	
	1		active	  1-6-16	Null	//when this happens	
*/
insert into [seemap_A16028].[CustomerStatus](CustId,SatusId,FromDate,ToDate)
values (1,1,'2016-04-01',Null)
insert into [seemap_A16028].[CustomerStatus](CustId,SatusId,FromDate,ToDate)
values (2,1,'2016-02-01',Null)
/*
TransactionInfo 
	TransactionId  CustId  Date  Type(Debit(Withdrawal)/Credit(Deposit))  Amount  Description
	1				1	  1-4-16	Credit								   1000		a			
	//when 1 makes a deposite of 1000 then now his closing balance in holding table would be
	1000 for the date of 1-4-16.So only one record of the closing balance for each month is stored.
	2				1	  2-6-16	Credit									250		a
	//when this transaction takes place for month of june in the holding table a 
	new record for this month closing balance is stored
*/


/*
Holding
	CustId  Amount  Date
	1		0		1-4-16 //for each month 
							i am storing the closing balance only 
							so initially for this month when the account is first created 
							1's opening balance is 0.But no transaction has been done yet
							now lets see transaction info
//So new holding value for 1 for month of april
Holding
	CustId  Amount  Date
	1		1000	1-4-16 
	1		1250	2-6-16	//for month of june//the closing balance is now 1250

*/

insert into [seemap_A16028].[Holding](CustId,HoldingDate,Amount)
values (1,'2016-04-01',0)
insert into [seemap_A16028].[Holding](CustId,HoldingDate,Amount)
values (2,'2016-02-01',0)
-----------------------------------------------------------------------------
/*Execute command: New_Transaction 1,'7-8-16','Debit',1000,'A is withdrawing 1000 rupees'; 
*/
alter procedure New_Transaction
@CustId int ,
@TransactionDate date,
@TransactionType varchar(100),
@Amount money,
@TransactionDescription text
as 
begin

-----------------------------the back dated transaction support---------------------
		----------finding the latest previous and next closing balance for the backdated transaction-----------
		Declare @latest_previous_closing_balance_date_for_backdated_transaction date
		set @latest_previous_closing_balance_date_for_backdated_transaction=(select top 1 HoldingDate
																   from [seemap_A16028].[Holding] 
																   where CustId=@CustId and @TransactionDate>HoldingDate
																   order by HoldingDate desc)
	--	print cast(@latest_previous_closing_balance_date_for_backdated_transaction as varchar(20) )+ 'Previous date ' 
		Declare @latest_previous_closing_balance_amount_for_backdated_transaction money
		set @latest_previous_closing_balance_amount_for_backdated_transaction=(select Amount
																      from [seemap_A16028].[Holding]
																	  where CustId=@CustId and HoldingDate=@latest_previous_closing_balance_date_for_backdated_transaction)
	--	print  @latest_previous_closing_balance_amount_for_backdated_transaction

		--i need to use the above two variables to implemnt the case 2---

		Declare @latest_next_closing_balance_date_for_backdated_transaction date
		set @latest_next_closing_balance_date_for_backdated_transaction=(select top 1 HoldingDate
																		 from [seemap_A16028].[Holding] 
																		 where CustId=@CustId and @TransactionDate<HoldingDate
																		 order by HoldingDate desc)
	--	print @latest_next_closing_balance_date_for_backdated_transaction
		Declare @latest_next_closing_balance_amount_for_backdated_transaction money
		set @latest_next_closing_balance_amount_for_backdated_transaction=(select Amount
																			from [seemap_A16028].[Holding]
																			where CustId=@CustId and HoldingDate=@latest_next_closing_balance_date_for_backdated_transaction)
		
		--print @latest_next_closing_balance_amount_for_backdated_transaction
		-------------------------------------------------------------------ends here----------------------------
----------finds out whether it is a backdated transaction or not--------------
--if(	(@TransactionType='Debit'  and (DATEPART(MM,@latest_previous_closing_balance_date_for_backdated_transaction) <= DATEPART(MM,@TransactionDate) )and ( DATEPART(MM,@latest_next_closing_balance_amount_for_backdated_transaction) >= DATEPART(MM,@TransactionDate)) and (DATEPART(DD,@TransactionDate)<DATEPART(DD,@latest_next_closing_balance_amount_for_backdated_transaction)) and (DATEPART(DD,@TransactionDate)>DATEPART(DD,@latest_previous_closing_balance_date_for_backdated_transaction)))
--print DATEPART(DD,@latest_next_closing_balance_amount_for_backdated_transaction) 
--print DATEPART(DD,@TransactionDate)
--------------------------------------------------------------------

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

declare @prevtransactiondate date
declare @nexttransactiondate date
declare @prevtransactionType varchar(10)
declare @nexttransactionType varchar(10)
declare @prevtransactionamount money
declare @nexttransactionamount money

set  @prevtransactiondate=(select top 1 TransactionDate
						   from [seemap_A16028].[TransactionInfo]
						   where CustId=@CustId and month(TransactionDate)<=month(@TransactionDate) and TransactionDate<@TransactionDate 
						   order by TransactionId desc)

set @nexttransactiondate=(select top 1 TransactionDate
						  from [seemap_A16028].[TransactionInfo]
						  where CustId=@CustId and month(TransactionDate)>=month(@TransactionDate) and TransactionDate>@TransactionDate 
						  order by TransactionId desc)

set @prevtransactionType=(select top 1 TransactionType 
						  from [seemap_A16028].[TransactionInfo] 
						  where CustId=@CustId and TransactionDate=@prevtransactiondate
						  order by TransactionId desc)
set @nexttransactionType=(select top 1 TransactionType 
						  from [seemap_A16028].[TransactionInfo] 
						  where CustId=@CustId and TransactionDate=@nexttransactiondate
						  order by TransactionId desc)

set @prevtransactionamount=(select top 1 Amount 
							from [seemap_A16028].[TransactionInfo] 
							where CustId=@CustId and TransactionDate=@prevtransactiondate
							order by TransactionId desc)
set @nexttransactionamount=(select top 1 Amount 
							from [seemap_A16028].[TransactionInfo] 
							where CustId=@CustId and TransactionDate=@nexttransactiondate
							order by TransactionId desc)



if	(@TransactionType='Debit' and  @TransactionDate BETWEEN @prevtransactiondate AND @nexttransactiondate) 
   
--	DATEPART(MM,@latest_previous_closing_balance_date_for_backdated_transaction) <= DATEPART(MM,@TransactionDate) and
	-- DATEPART(MM, @latest_next_closing_balance_date_for_backdated_transaction) >= DATEPART(MM,@TransactionDate)  and
	  --DATEPART(DD,@TransactionDate)<DATEPART(DD, @latest_next_closing_balance_date_for_backdated_transaction) and 
	 -- DATEPART(DD,@TransactionDate)>DATEPART(DD,@latest_previous_closing_balance_date_for_backdated_transaction))
			begin             
			print'this is a back dated transaction'
			--return
			 -----------this is the first case of finding minimum balance  of subsequent closing balances from the holding table ------
			 -----------and checking if the subtraction results in a negative.If it does then dont insert the back dated transaction--- 
		
			declare @minbalancecase1 int
			set @minbalancecase1=(select  min(Amount)
								  from [seemap_A16028].[Holding] 
	                              where CustId=@CustId and month(@TransactionDate)<=month(HoldingDate)
	                              group by CustId)
			if(( @minbalancecase1-@Amount)<0)
				begin
					print'can''t insert as somewhere down the line the balance would turn negative'
					return
				end

			 ------------------------case 1 works------------------------------------------
			 ------------------------this is testing for case two where we need to check the total balance from the 
			 ---previous months closing balance in the holding table and if the difference in the calculated balance 
			 ---and the current amount difference is less then there is no transaction-----------------
			  Declare @previous_closing_balance_date date
			  set @previous_closing_balance_date=(select top 1 HoldingDate
											 from [seemap_A16028].[Holding] 
											 where CustId=@CustId and month(@TransactionDate)>month(HoldingDate)
											 order by HoldingDate desc)
			  Declare @previous_closing_balance_amount money
			  set @previous_closing_balance_amount=(select Amount
													from [seemap_A16028].[Holding] 
													where CustId=@CustId and HoldingDate=@previous_closing_balance_date)
			 Declare @balancecheck money
			 set @balancecheck=@previous_closing_balance_amount
			
				while ( (select  count(*)
							  from [seemap_A16028].[TransactionInfo] 
							  where CustId=@CustId and @TransactionDate>TransactionDate and TransactionDate>(select top 1 HoldingDate
																								   from [seemap_A16028].[Holding] 
																								   where CustId=@CustId and month(@TransactionDate)>=month(HoldingDate)
																								   order by HoldingDate )
								group by TransactionId)>0)
					begin
						Declare @tempamount money
						Declare @temptype varchar(10)
						set @tempamount = (select top 1 Amount
										  from [seemap_A16028].[TransactionInfo] 
										  where CustId=@CustId and @TransactionDate>TransactionDate and TransactionDate>(select top 1 HoldingDate
																														  from [seemap_A16028].[Holding] 
																														   where CustId=@CustId and month(@TransactionDate)>=month(HoldingDate)
																														   order by HoldingDate )
										group by TransactionId)
					
						set @temptype = (select top 1 TransactionType
										from [seemap_A16028].[TransactionInfo] 
										where CustId=@CustId and @TransactionDate>TransactionDate and TransactionDate>(select top 1 HoldingDate
																													   from [seemap_A16028].[Holding] 
																													   where CustId=@CustId and month(@TransactionDate)>=month(HoldingDate)
																													   order by HoldingDate ))
					
						if(@temptype='Debit')
						begin
							set @balancecheck = @balancecheck-@tempamount
						end	
						if(@temptype='Credit')
						begin
							set @balancecheck = @balancecheck+@tempamount
						end			
						  					
					end/*
				Declare @row	int=1
				while (@row>0)
					begin
						Declare @tempamount money
						Declare @temptype varchar(10)
						set @tempamount = (select top 1 Amount
										   from [seemap_A16028].[TransactionInfo] 
							               where CustId=@CustId and @TransactionDate>TransactionDate and TransactionDate>(select top 1 HoldingDate
																														  from [seemap_A16028].[Holding] 
																														  where CustId=@CustId and month(@TransactionDate)>=month(HoldingDate)
																														  order by HoldingDate ))
						set @temptype = (select top 1 TransactionType
										   from [seemap_A16028].[TransactionInfo] 
							               where CustId=@CustId and @TransactionDate>TransactionDate and TransactionDate>(select top 1 HoldingDate
																														  from [seemap_A16028].[Holding] 
																														  where CustId=@CustId and month(@TransactionDate)>=month(HoldingDate)
																														  order by HoldingDate ))
					     set @row=@@ROWCOUNT

						if(@temptype='Debit')
						begin
							set @balancecheck = @balancecheck-@tempamount
						end	
						if(@temptype='Credit')
						begin
							set @balancecheck = @balancecheck+@tempamount
						end			
						  					
					end*/

				if((@balancecheck-@Amount)<0)
				 begin
					print 'cannot insert transaction as there are insufficient funds based on previous transactions'
					return
				 end
				 Declare @tempbalance money
				set @tempbalance= @balancecheck-@Amount
				
				if exists (select  *  from [seemap_A16028].[TransactionInfo] where CustId=@CustId and @TransactionDate<TransactionDate )
					begin
						
						Declare @tempamount1 money
						Declare @temptype1 varchar(10)
						set @tempamount1 = (select top 1 Amount from [seemap_A16028].[TransactionInfo]  where CustId=@CustId and @TransactionDate<TransactionDate )	
						set @temptype1 = (select top 1 TransactionType from [seemap_A16028].[TransactionInfo]  where CustId=@CustId and @TransactionDate<TransactionDate )		
						if(@temptype1='Debit')
						begin
							set @tempbalance = @tempbalance-@tempamount1
						end	
						if(@temptype1='Credit')
						begin
							set @tempbalance = @tempbalance+@tempamount1
						end			
						if(@tempbalance<0)
						begin
							print'cant insert transaction because subsequent transactions end up with negative balance even if holding table doesnt show it' 
							return
						end  					
					end/*
					Declare @row1 int=1
					while (@row1>0)
					begin
						
						Declare @tempamount1 money
						Declare @temptype1 varchar(10)
						set @tempamount1 = (select top 1 Amount  from [seemap_A16028].[TransactionInfo] where CustId=@CustId and @TransactionDate<TransactionDate )
						set @temptype1 = (select  top 1 TransactionType  from [seemap_A16028].[TransactionInfo] where CustId=@CustId and @TransactionDate<TransactionDate )		
						set @row1= @@ROWCOUNT
						if(@temptype1='Debit')
						begin
							set @tempbalance = @tempbalance-@tempamount1
						end	
						if(@temptype1='Credit')
						begin
							set @tempbalance = @tempbalance+@tempamount1
						end			
						if(@tempbalance<0)
						begin
							print'cant insert transaction because subsequent transactions end up with negative balance even if holding table doesnt show it' 
							return
						end  					
					end*/
					------------------------------------------------------
					if(@tempbalance>0)
						begin
							 Declare @tempupdatedtranamount money
							if(@TransactionDate<(select HoldingDate from [seemap_A16028].[Holding] where CustId=@CustId and month(@TransactionDate)=month(HoldingDate)) )
								begin
									 UPDATE [seemap_A16028].[Holding]
									 SET Amount=Amount-@Amount
									 WHERE CustId = @CustId and  HoldingDate=(select HoldingDate from [seemap_A16028].[Holding] where CustId=@CustId and month(@TransactionDate)=month(HoldingDate) ) ;
								end
							
							if (@TransactionDate>(select HoldingDate from [seemap_A16028].[Holding] where CustId=@CustId and month(@TransactionDate)=month(HoldingDate)))
								begin
									UPDATE [seemap_A16028].[Holding]
									SET HoldingDate = @TransactionDate,Amount=Amount-@Amount
									WHERE CustId = @CustId and  HoldingDate=(select HoldingDate from [seemap_A16028].[Holding] where CustId=@CustId and month(@TransactionDate)=month(HoldingDate) ) ;
								end
							--need to check condition for dates in between two different months-come here please-
							-- if(month(@TransactionDate)!=month((select HoldingDate from [seemap_A16028].[Holding] where CustId=@CustId))
							 set @tempupdatedtranamount=(select Amount from [seemap_A16028].[Holding] where CustId = @CustId and  HoldingDate=(select HoldingDate from [seemap_A16028].[Holding] where CustId=@CustId and month(@TransactionDate)=month(HoldingDate))  )
							
							print @tempupdatedtranamount 
							--need to update holding tables
							---i have stoped here continue from here------------------
							
							
							
							if exists (select  *   from [seemap_A16028].[TransactionInfo]  where CustId=@CustId and @TransactionDate<TransactionDate and month(@TransactionDate)!=month(TransactionDate)  )
								begin							
									Declare @tempamount2 money
									Declare @tempdate date
									Declare @temptype2 varchar(10)
									set @tempamount2 = (select top 1 Amount from [seemap_A16028].[TransactionInfo]  where CustId=@CustId  and month(@TransactionDate)!=month(TransactionDate) and @TransactionDate<TransactionDate)	
									set @temptype2 = (select top 1 TransactionType from [seemap_A16028].[TransactionInfo]  where CustId=@CustId  and month(@TransactionDate)!=month(TransactionDate) and @TransactionDate<TransactionDate)		
									set @tempdate=(select top 1 TransactionDate from [seemap_A16028].[TransactionInfo]  where CustId=@CustId and month(@TransactionDate)!=month(TransactionDate) and @TransactionDate<TransactionDate )
									if(@temptype2='Debit')
										begin
											set  @tempupdatedtranamount =  @tempupdatedtranamount-@tempamount2
											UPDATE [seemap_A16028].[Holding]
											SET Amount=@tempupdatedtranamount
											WHERE CustId = @CustId and  month(HoldingDate)=month(@tempdate)
										end	
									if(@temptype2='Credit')
										begin
											set  @tempupdatedtranamount =  @tempupdatedtranamount+@tempamount2
											UPDATE [seemap_A16028].[Holding]
											SET Amount=@tempupdatedtranamount
											WHERE CustId = @CustId and  month(HoldingDate)=month(@tempdate)
										end			
								--	if(@tempbalance<0)
									--	begin
									--		print'cant insert transaction because subsequent transactions end up with negative balance even if holding table doesnt show it' 
										--	return
									--	end  					
								end/*
								Declare @row2 int=1
								while  (@row2>0)
								begin							
									Declare @tempamount2 money
									Declare @tempdate date
									Declare @temptype2 varchar(10)
									set @tempamount2 = (select  top 1 Amount  from [seemap_A16028].[TransactionInfo]  where CustId=@CustId and month(@TransactionDate)!=month(TransactionDate) and @TransactionDate<TransactionDate )
									set @temptype2 = (select  top 1 TransactionType   from [seemap_A16028].[TransactionInfo]  where CustId=@CustId and month(@TransactionDate)!=month(TransactionDate) and @TransactionDate<TransactionDate )	
									set @tempdate=(select  top 1 TransactionDate   from [seemap_A16028].[TransactionInfo]  where CustId=@CustId and month(@TransactionDate)!=month(TransactionDate) and @TransactionDate<TransactionDate )
									set @row2=@@ROWCOUNT
									if(@temptype2='Debit')
										begin
											set  @tempupdatedtranamount =  @tempupdatedtranamount-@tempamount2
											UPDATE [seemap_A16028].[Holding]
											SET Amount=@tempupdatedtranamount
											WHERE CustId = @CustId and  month(HoldingDate)=month(@tempdate)
										end	
									if(@temptype2='Credit')
										begin
											set  @tempupdatedtranamount =  @tempupdatedtranamount+@tempamount2
											UPDATE [seemap_A16028].[Holding]
											SET Amount=@tempupdatedtranamount
											WHERE CustId = @CustId and  month(HoldingDate)=month(@tempdate)
										end			
								--	if(@tempbalance<0)
									--	begin
									--		print'cant insert transaction because subsequent transactions end up with negative balance even if holding table doesnt show it' 
										--	return
									--	end  					
								end*/
						end
					
					--select Amount
					--from [seemap_A16028].[Holding] 
				--	where CustId=1 


		--	select  TransactionDate,TransactionType,Amount,TransactionId,CustId
			--from [seemap_A16028].[TransactionInfo] 
			--where CustId=1 and '2016-06-03'>TransactionDate and TransactionDate>(select top 1 HoldingDate
				--																 from [seemap_A16028].[Holding] 
					--															 where CustId=1 and month('2016-06-03')>=month(HoldingDate)
						--														 order by HoldingDate )
			/* if(@prevtransactionType='Credit' and @Amount> @prevtransactionamount )
			  begin
				print 'insufficient funds in transaction dated'+cast( @prevtransactiondate as varchar(20))
				return
			  end

			 if(@Amount= @prevtransactionamount and @nexttransactionType='Debit' )
			  begin
				print 'this transaction reduces balance to zero and the next transaction is also Debit'
				return
			  end

			 if()
			  begin
			  end

			 if( @latest_next_closing_balance_amount_for_backdated_transaction)
			  begin
			  end
			 */
	 end
	
	 
	 
   --  @TransactionDate BETWEEN @latest_previous_closing_balance_date_for_backdated_transaction) AND @latest_next_closing_balance_date_for_backdated_transaction) 
   

		print'step 5'
		----------------------------ends here-----------------------------------

Declare @holdingDate date
Declare @HoldingUpdateAmount money

Set @holdingDate =(select  max(HoldingDate) from [seemap_A16028].[Holding] where CustId = @CustId  ) 
Set @HoldingUpdateAmount =(select Amount from [seemap_A16028].[Holding] where CustId = @CustId and HoldingDate=@holdingDate)

--DATEPART(MM,@holdingDate) = DATEPART(MM,@TransactionDate) and datepart(DD,@TransactionDate)>=datepart(DD,@holdingDate)
   
if( @TransactionDate>=@holdingDate and @TransactionType='Debit' and @HoldingUpdateAmount<@Amount )
			begin             
			 print 'insufficient funds'
			 return
        End
	--if( DATEPART(MM,@holdingDate) < DATEPART(MM,@TransactionDate) and datepart(DD,@TransactionDate)>=datepart(DD,@holdingDate) and @TransactionType='Debit' and @HoldingUpdateAmount<@Amount  )
		--	begin             
			-- print 'insufficient funds'
			 --return
        --End
		
--	if(@TransactionDate<@holdingDate)
	--begin             

		--	 print 'transaction not allowed'
			-- return
    --End
		
insert into [seemap_A16028].[TransactionInfo](CustId,TransactionDate ,TransactionType,Amount,TransactionDescription)
values (@CustId,@TransactionDate,@TransactionType,@Amount,@TransactionDescription)
--DATEPART(MM,@holdingDate) = DATEPART(MM,@TransactionDate) and datepart(DD,@TransactionDate)>=datepart(DD,@holdingDate)
  if( @TransactionDate=@holdingDate and @TransactionType='Credit' )
        begin
             
			 SET @HoldingUpdateAmount = @HoldingUpdateAmount+ @Amount
			 SET @holdingDate = @TransactionDate
			 UPDATE [seemap_A16028].[Holding]
			 SET HoldingDate = @holdingDate,Amount=@HoldingUpdateAmount
			 WHERE CustId = @CustId and  HoldingDate=(select max(HoldingDate) from [seemap_A16028].[Holding] where CustId = @CustId ) ;

        End
		--DATEPART(MM,@holdingDate) < DATEPART(MM,@TransactionDate) and datepart(DD,@TransactionDate)>=datepart(DD,@holdingDate)
	if( @TransactionDate>@holdingDate and @TransactionType='Credit')
			begin
             
			 SET @HoldingUpdateAmount = @HoldingUpdateAmount+ @Amount
			 SET @holdingDate = @TransactionDate
			 insert into [seemap_A16028].[Holding] (CustId,HoldingDate,Amount)
			 values(@CustId,@holdingDate,@HoldingUpdateAmount)		 

        End
---		print 'update balance'
--((DATEPART(MM,@holdingDate) = DATEPART(MM,@TransactionDate)) and (datepart(DD,@TransactionDate)>=datepart(DD,@holdingDate) ))
	if ( @TransactionType='Debit' and @HoldingUpdateAmount>=@Amount and @TransactionDate=@holdingDate)
	 	begin
   --          print 'In update balance1'
			 SET @HoldingUpdateAmount = @HoldingUpdateAmount- @Amount
			 SET @holdingDate = @TransactionDate
			 UPDATE [seemap_A16028].[Holding]
			 SET HoldingDate = @holdingDate,Amount=@HoldingUpdateAmount
			 where CustId = @CustId and  HoldingDate=(select max(HoldingDate) from [seemap_A16028].[Holding] where CustId = @CustId ) 			 
			End
		print 'update balance2'
		--DATEPART(MM,@holdingDate) < DATEPART(MM,@TransactionDate) and datepart(DD,@TransactionDate)>=datepart(DD,@holdingDate)
	if(@TransactionType='Debit' and @HoldingUpdateAmount>=@Amount and @TransactionDate>@holdingDate )
			begin
         --    print 'In update balance2'
			 SET @HoldingUpdateAmount = @HoldingUpdateAmount- @Amount
			 SET @holdingDate = @TransactionDate
			 insert into [seemap_A16028].[Holding] (CustId,HoldingDate,Amount)
			 values(@CustId,@holdingDate,@HoldingUpdateAmount)				 
        End		
end

---------------------------------------------------------
select * from [seemap_A16028].[TransactionInfo]
--select max(HoldingDate) from [seemap_A16028].[Holding] 
select * from [seemap_A16028].[Holding] 
select t.TransactionId,t.TransactionDate ,t.TransactionType,t.Amount,h.Amount,h.HoldingDate
from [seemap_A16028].[TransactionInfo] t,[seemap_A16028].[Holding] h
where t.CustId=h.CustId;
--------------------------------------------------------------------
-----------------this gives the previous transaction from the transactioninfo------------
select top 1 CustId,TransactionDate,TransactionId,TransactionType,Amount
from [seemap_A16028].[TransactionInfo]
where CustId=1 and month(TransactionDate)<=month('2016-06-03') and TransactionDate<'2016-06-03' 
order by TransactionId desc
-----------------it ends here-------------------------------
-------------------------------this gives the next transaction from the transactioninfo------
select top 1 CustId,TransactionId,TransactionDate,TransactionType,Amount
from [seemap_A16028].[TransactionInfo]
where CustId=1 and month(TransactionDate)>=month('2016-06-03') and TransactionDate>'2016-06-03' 
order by TransactionId desc
---------------------------------it ends here-------------------
-------------------------------------------------------------------------
---------------------------------------------------------
drop procedure New_Transaction
exec New_Transaction 1,'2016-06-03','Debit',3000,'A is withdrawing 3000 rupees';
---debit and credit works 
--holding stores only one value for each month also works


if( @TransactionType='Debit' and @HoldingUpdateAmount<@Amount and DATEPART(MM,@holdingDate) = DATEPART(MM,@TransactionDate) and datepart(DD,@TransactionDate)>=datepart(DD,@holdingDate) )
			begin             
			 print 'insufficient funds'
			 return
        End
	if(@TransactionType='Debit' and @HoldingUpdateAmount<@Amount and DATEPART(MM,@holdingDate) < DATEPART(MM,@TransactionDate) and datepart(DD,@TransactionDate)>=datepart(DD,@holdingDate)  )
			begin             
			 print 'insufficient funds'
			 return
        End
	

	if(@TransactionDate<@holdingDate)
	begin             

			 print 'transaction not allowed'
			 return
    End
---------------------end of procedure 1---------------------------------------------------------
CREATE FUNCTION [seemap_A16028].[return_balance] (@CustId int, @any_date date)
RETURNS money
AS BEGIN
    DECLARE @balance money	
	set @balance=0
	Declare @prev_month_closing_balance_amount money
	set @prev_month_closing_balance_amount=(select Amount 
											from [seemap_A16028].[Holding]
											where CustId=@CustId and HoldingDate=(select max(HoldingDate)
																				  from [seemap_A16028].[Holding] 
																				  where CustId=1 and month(HoldingDate)<month(@any_date)
																				  group by month(HoldingDate) ) )
    print @prev_month_closing_balance_amount
    RETURN @balance
END

--returns the first opening balance
select top 1 HoldingDate 
from [seemap_A16028].[Holding] 
where CustId=1
--returns the latest closing balance
select top 1 HoldingDate 
from [seemap_A16028].[Holding] 
where CustId=1
order by HoldingDate desc
--group by month(HoldingDate) 

select [seemap_A16028].[return_balance] (1, '7-8-16')
--and HoldingDate=select max(month(7-8-16)>month(HoldingDate))
						--		 from [seemap_A16028].[Holding]
						--		  where CustId=1
    ----check order by clause------------
	
	---this works to determine the latest previous closing balance for a back dated transaction--------
	select top 1 HoldingDate
	from [seemap_A16028].[Holding] 
	where CustId=1 and month('2016-06-03')>=month(HoldingDate)
	order by HoldingDate desc
	-----------ends here------------------------------
	---this works to determine the latest previous closing balance for a back dated transaction for the previous month i guess--------
	select top 1 HoldingDate
	from [seemap_A16028].[Holding] 
	where CustId=1 and month('2016-06-03')>month(HoldingDate)
	order by HoldingDate desc
	-----------ends here------------------------------

	-------this works to determine the latest next closing balance for a back dated transaction--------
	select top 1 HoldingDate
	from [seemap_A16028].[Holding] 
	where CustId=1 and month('3-6-16')<=month(HoldingDate)
	order by HoldingDate desc
	----------ends here---------------------------------
	select * from [seemap_A16028].[Holding] 

	--insert into [seemap_A16028].[Holding](CustId,HoldingDate,Amount) values(1,'2-5-16',2010)

	-------this works to determine the minimum of the latest next closing balance for a back dated transaction--------
	select  min(Amount)
	from [seemap_A16028].[Holding] 
	where CustId=1 and month('3-6-16')<=month(HoldingDate)
	group by CustId
	
	----------ends here---------------------------------
	select * from [seemap_A16028].[TransactionInfo]
	DELETE FROM [seemap_A16028].[TransactionInfo]
WHERE Amount=500
	DELETE FROM [seemap_A16028].[Holding] 
WHERE CustId=1 or CustId=2
--------------this gives all the transactions from the back date to the immediate months closing balance in which there was some transaction-----------
select  TransactionDate,TransactionType,Amount,TransactionId,CustId
from [seemap_A16028].[TransactionInfo] 
where CustId=1 and '2016-06-03'>TransactionDate and TransactionDate>(select top 1 HoldingDate
																	 from [seemap_A16028].[Holding] 
																	 where CustId=1 and month('2016-06-03')>=month(HoldingDate)
																	 order by HoldingDate )
--order by TransactionId desc
------------------it ends here-------------------------------------------------

insert into [seemap_A16028].[TransactionInfo](CustId,TransactionDate ,TransactionType,Amount,TransactionDescription)
values (1,'2016-04-01','Credit',1000,'a')
insert into [seemap_A16028].[TransactionInfo](CustId,TransactionDate ,TransactionType,Amount,TransactionDescription)
values (1,'2016-04-01','Credit',1000,'a')
insert into [seemap_A16028].[TransactionInfo](CustId,TransactionDate ,TransactionType,Amount,TransactionDescription)
values (1,'2016-06-01','Debit',100,'a')
insert into [seemap_A16028].[TransactionInfo](CustId,TransactionDate ,TransactionType,Amount,TransactionDescription)
values (1,'2016-06-02','Debit',100,'a')
insert into [seemap_A16028].[TransactionInfo](CustId,TransactionDate ,TransactionType,Amount,TransactionDescription)
values (1,'2016-08-07','Debit',1000,'a')
insert into [seemap_A16028].[TransactionInfo](CustId,TransactionDate ,TransactionType,Amount,TransactionDescription)
values (1,'2016-08-07','Debit',100,'a')

insert into [seemap_A16028].[Holding](CustId,HoldingDate ,Amount)
values (1,'2016-04-01',2000)
insert into [seemap_A16028].[Holding](CustId,HoldingDate ,Amount)
values (1,'2016-06-02',1800)
insert into [seemap_A16028].[Holding](CustId,HoldingDate ,Amount)
values (1,'2016-08-07',700)

select * from [seemap_A16028].[TransactionInfo]
--select max(HoldingDate) from [seemap_A16028].[Holding] 
select * from [seemap_A16028].[Holding] 


exec New_Transaction 1,'2016-06-03','Debit',1,'A is withdrawing 1 rupees';

while exists (select  count(*)	  from [seemap_A16028].[TransactionInfo] 
	   where CustId=1 )   													    
								
begin
	print 'some'
end