SQL query Design

Question

Table 1 : Employee

EmpId  CreatedAt
100    2015-11-09 07:21:02
200    2017-01-24 18:24:01
300    2016-08-20 06:55:35

Table 2 : Account

AccId   EmpID  Currency  CreatedAt
9000     100       USD     2017-04-20 19:40:55
9001     200       USD     2017-04-20 19:40:55
9002     100       EUR     2017-05-20 19:40:55
9003     200       USD     2017-04-20 19:40:55
9004     100       USD     2017-04-20 19:40:55

Table 3 : Transaction

 TrnsId   AccId Amount CreatedAt
  10       9000   3000  2017-04-25 19:40:55
  11       9001    500  2017-05-25 19:40:55
  12       9000   -200  2017-05-30 19:40:55
  13       9000   -500  2017-06-11 19:40:55

Create a table that provides the day end balance (at midnight) for each account since it was first created, i.e. there should be a single entry in the table for each day an account exists, and its balance at the end of that day.

Can anybody help me in writing query to above scenario?

Thanks.

Welcome to Stackoverflow! Yes, we can help you write it, but we won't write it for you. Post what you've tried and where you're stuck. — squillman, Sep 19 '18 at 19:32
Not many of us are going to willingly do your homework for you. — dfundako, Sep 19 '18 at 19:32
Possible duplicate of [SQL Server, SQL Query Assistance Required](https://stackoverflow.com/questions/52412124/sql-server-sql-query-assistance-required) — SMor, Sep 19 '18 at 20:14

score 0 · Answer 1 · answered Sep 19 '18 at 19:58

Since you haven't posted any attempt to solve this yourself, I will assume you need an initial nudge in the right direction. Hopefully this will help.

Outer-Join your Account table to a table (create it if you don't have one) that has one row for every day in the calendar (this is often referred to as a "tally table"). Filter out the days in the calendar that were before the date the account was created.

That will produce a result of one row for every Account-Date combination, which is all the rows you want in your result.

From there it's just a matter of adding the column with the End-Of-Day balance for that Account on that Date. There are lots of ways to do this. Google "SQL Running Total" and pick a method you like.

SQL query Design

1 Answers1