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
Recent Article
- Fix-Gradient effect turning to gray in after effects
- How to blur an image in python?
- ModuleNotFoundError: No module named 'whois' in Python GoviralHost Without Terminal
- How to Convert Image to Pencil Sketch in Python?
- AttributeError: module 'urllib' has no attribute 'request' - Python
- How to Extract audio from video files using python?
- PermissionError: [Errno 13] Permission denied: 'shampoo_sales.csv' - Python
- [WinError 145] The directory is not empty: 'FolderPath' - Python
Related Article