Assignment 1.txt

Please maintain the scripts for each command you execute on SQL server.

Create a separate schema for each user.

Write procedure for core banking system

Create the following tables.
1. CustomerInfo (CustId, CustName, DateOfBirth, Gender, Address)
2. BranchInfo (BranchId, BranchName, Address)
3. CustomerBranchMap (CustId, BranchId, FromDate, ToDate)
4. CustomerStatus (CustId, StatusId, FromDate, ToDate)
5. TransactionInfo (TransactionId, CustId, Date, Type(Debit/Credit),
 Amount, Description)
6. Holding (CustId, Amount, Date)

Insert Dummy Records in each master.

1. Create a procedure to record new transaction. 
The procedure will take (CustId, Date, Type, Amount and Description) as input,
insert record into transactionInfo table and update the holding table.
2. The Holding table will have one record for each month for each customer, 
3. Back dated transaction entries are allowed
4. Debit transaction will not be allowed if there are insufficient funds 
which may result into negative balance for any subsequent transaction.
5. Write a function to return balance of a customer for any given date.
6. Create a stored procedure to select the bank statement for any given date range.
 the output shall be 
   CustId, CustName, BranchName (latest branch id), 
	Date, DebitAmuont, CreditAmount, Balance
 The first and the last rows will only have opening balance and closing balance 
 respectively. for all other rows, running balance shall be shown.
 ------------------------------------------question 1 ------------------------------
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','1-1-96','M','A1')
insert into [seemap_A16028].[CustomerInfo](CustName,DateOfBirth,Gender,CustAddress)
values ('B','1-2-96','F','B1')
insert into [seemap_A16028].[CustomerInfo](CustName,DateOfBirth,Gender,CustAddress)
values ('C','1-3-96','M','C1')
insert into [seemap_A16028].[CustomerInfo](CustName,DateOfBirth,Gender,CustAddress)
values ('D','1-4-96','F','D1')
insert into [seemap_A16028].[CustomerInfo](CustName,DateOfBirth,Gender,CustAddress)
values ('E','1-5-96','M','E1')
insert into [seemap_A16028].[CustomerInfo](CustName,DateOfBirth,Gender,CustAddress)
values ('F','1-6-96','F','F1')
insert into [seemap_A16028].[CustomerInfo](CustName,DateOfBirth,Gender,CustAddress)
values ('G','1-7-96','M','G1')
insert into [seemap_A16028].[CustomerInfo](CustName,DateOfBirth,Gender,CustAddress)
values ('H','1-8-96','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,'1-4-16',Null)
insert into [seemap_A16028].[CustomerBranchMap](CustId,BranchId,BranchName,BranchAddress)
values (2,1,'1-2-16',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,'1-4-16',Null)
insert into [seemap_A16028].[CustomerStatus](CustId,SatusId,FromDate,ToDate)
values (2,1,'1-2-16',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,'1-4-16',0)
insert into [seemap_A16028].[Holding](CustId,HoldingDate,Amount)
values (2,'1-2-16',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
insert into [seemap_A16028].[TransactionInfo](CustId,TransactionDate ,TransactionType,Amount,TransactionDescription)
values (@CustId,@TransactionDate,@TransactionType,@Amount,@TransactionDescription)
Declare @holdingDate date
Declare @HoldingUpdateAmount money

Set @holdingDate =(select  max(HoldingDate) from [seemap_A16028].[Holding] where CustId = @CustId  ) 
Set @HoldingUpdateAmount =(select max(Amount) from [seemap_A16028].[Holding] where CustId = @CustId and HoldingDate=@holdingDate)
    if(DATEPART(MM,@holdingDate) = DATEPART(MM,@TransactionDate) and datepart(DD,@TransactionDate)>=datepart(DD,@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
	if(DATEPART(MM,@holdingDate) < DATEPART(MM,@TransactionDate) and datepart(DD,@TransactionDate)>=datepart(DD,@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'
	if ( @TransactionType='Debit' and @HoldingUpdateAmount>=@Amount and ((DATEPART(MM,@holdingDate) = DATEPART(MM,@TransactionDate)) and (datepart(DD,@TransactionDate)>=datepart(DD,@holdingDate) )))
	  If @TransactionType = 'Debit'		
			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'
	if(@TransactionType='Debit' and @HoldingUpdateAmount>=@Amount and DATEPART(MM,@holdingDate) < DATEPART(MM,@TransactionDate) and datepart(DD,@TransactionDate)>=datepart(DD,@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
	
		
	if( @TransactionType='Debit' and @HoldingUpdateAmount<@Amount and DATEPART(MM,@holdingDate) = DATEPART(MM,@TransactionDate) and datepart(DD,@TransactionDate)>=datepart(DD,@holdingDate) )
			begin             
			 print 'insufficient funds'
        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'
        End
	
	if(@TransactionDate<@holdingDate)
	begin             
			 print 'transaction not allowed'
    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;
---------------------------------------------------------
drop procedure New_Transaction
New_Transaction 1,'7-8-16','Credit',1000,'A is withdrawing 1000 rupees';

---------------------end of procedure 1---------------------------------------------------------