Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 65 - Solving the Running Total Problem

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.

Database Source Control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Bring your database development process forward by 5 years. Get started now.

Sample Data

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

Expected Results

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

Rules

  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.

Sample Script

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

Restrictions

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

Notes

  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.
Database Source Control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Bring your database development process forward by 5 years. Get started now.

Jacob Sebastian
1 · 100% · 32220
Submit a Solution
Previous Challenge
Next Challenge
18
Liked
11
Comments
100
Solutions



Submit

TSQL Challenge 65 - Solving the Running Total Problem

Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.