This challenge invites you to solve one of the most common calculation challenges seen in applications that deal with financial transactions.

This is not a very complicated challenge and there are different ways to solve this. The key here is to see some of the very efficient logical approaches to solve the Running Total problem.

Date       CustomerID Type  Amount
---------- ---------- ----- --------
2011-01-01 CUST1001   INV   12000.00
2011-01-02 CUST1001   PAY    3000.00
2011-01-03 CUST1001   INV    8000.00
2011-01-04 CUST1001   PAY    9000.00
2011-01-04 CUST1002   INV    1000.00
2011-01-05 CUST1002   PAY    5000.00
2011-01-05 CUST1002   INV    6000.00

Date       CustomerID Type  Amount   Balance
---------- ---------- ----- -------- ---------
2011-01-01 CUST1001   INV   12000.00  12000.00
2011-01-02 CUST1001   PAY    3000.00   9000.00
2011-01-03 CUST1001   INV    8000.00  17000.00
2011-01-04 CUST1001   PAY    9000.00   8000.00
2011-01-04 CUST1002   INV    1000.00   1000.00
2011-01-05 CUST1002   INV    6000.00   7000.00
2011-01-05 CUST1002   PAY    5000.00   2000.00

1. There can be more than one INV and more than one PAY record on the same day for the same customer.
2. If there are INV and PAY records on the same day for the same customer, the INV rows should be processed first and should appear before the PAY rows.
3. Thus the output should be processed and ordered by CustomerID, Date, Type, Amount.
4. The balance can become negative.

Use the TSQL Script given below to generate the source table and fill them with sample data.

  
IF OBJECT_ID('TC65','U') IS NOT NULL BEGIN   
	DROP TABLE TC65  
END  
GO    

CREATE TABLE TC65
(
	Date DATETIME,
	CustomerID VARCHAR(50),
	Type VARCHAR(3),
	Amount MONEY
)  
INSERT INTO TC65(Date,CustomerID,Type,Amount)
SELECT '2011-01-01','CUST1001','INV','12,000' UNION ALL
SELECT '2011-01-02','CUST1001','PAY','3,000' UNION ALL
SELECT '2011-01-03','CUST1001','INV','8,000' UNION ALL
SELECT '2011-01-04','CUST1001','PAY','9,000' UNION ALL
SELECT '2011-01-04','CUST1002','INV','1,000' UNION ALL
SELECT '2011-01-05','CUST1002','PAY','5,000' UNION ALL
SELECT '2011-01-05','CUST1002','INV','6,000'

SELECT * FROM TC65

1. The solution should be a single query that starts with a "SELECT" or “;WITH”

1. Read the Submission Guidelines and make sure that your solution follows them.
2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.