SQL

How to find your Current bank balance using SQL query?

How to find your Current bank balance using SQL query?, someone asked me to explain?
SQL

In this section, I explain how to find your bank balance using sql query. This questions asked by many interviewer, so please do this practice before go to interview. Here I am created one Transaction table with three fields such as TransactionDate, Description and Amount. Here add primery key  to TransactionDate.

You have create a table using below query. And enter some value manually like how I mentioned below.

CREATE TABLE [dbo].[Transaction](

      [Transaction Date] [date] NOT NULL,

      [Description] [nvarchar](50) NULL,

      [Amount] [int] NULL,

 CONSTRAINT[PK_Transaction] PRIMARY KEY CLUSTERED

(
 [Transaction Date] ASC

)WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

 
) ON [PRIMARY]


Input

Transaction Date

Description

Amount

1-Jan-12

Deposit

1000

1-Mar-12

Withdraw

-150

1-Apr-13

Withdraw

-350

1-May-13

Deposit

500



select *,

case when Amount > 0 then Amount end as Deposit,

case when Amount < 0 then ABS(Amount) end as Withdraw,

(select SUM(Amount) from dbo.[Transaction] where[Transaction Date]<= A.[Transaction Date])

from dbo.[Transaction] as A

order by[Transaction Date]

 

Output

 

              Transaction Date

Description

Deposit

Withdraw

Balance

1-Jan-12

Deposit

1000

 

1000

1-Mar-12

Withdraw

 

150

850

1-Apr-13

Withdraw

 

350

500

1-May-13

Deposit

500

 

1000

 

  

Post your comments / questions