在SQL中创建财务报表 [英] Creating Financial statements in sql

问题描述

请考虑以下表格:

现在，在我继续之前，我应该先说一下我不是会计师(如果您是一名会计师，那么读这很可能就是这样！)

Now before I continue I should preface this by stating that I'm not an accountant (which if you happen to be an accountant reading this probably shows!!)

我有一个发票表和一个相关的InvoicePayments表以及一个单独的Contacts报表表.我的意图是使最终用户能够为客户创建编号的语句，至少从理论上讲，将来至少可以在任何时候对其进行回调.从逻辑上讲，应该可以创建一个简单的查询，即给定期初余额的输入参数应能够以一定的余额>来提取属于某个联系人的那些发票的总和. 0(在给定期间内)将其添加到该未清余额中，并且同样仅这次使用同一时期的发票付款从未清余额中减去它，从而使其中一个具有该期间的最终期末余额.

I have an invoices table and a related InvoicePayments table and a separate Contacts statements table. My Intention is to provide the end user with the ability to create numbered statements for clients that in theory , at least, could be called back at any time in the future. Logically It should be possible to create a straightforward query that given an input parameter of the opening balance should be able to take the sum of those invoices belonging to a certain contact with an outstanding balance > 0 (from a given period) add it to that outstanding balance and do likewise with the invoice payments from the same period only this time subtracting it from the outstanding balance, leaving one with a final closing balance for that period.

我想从具有创建复杂查询的更多经验的人员那里了解到，如果给定如上所示的表结构，是否有可能创建一个包含openbalance，开始日期和结束日期的存储过程作为输入参数，并使其按日期顺序生成发票/发票付款交易表(默认情况下，首先在两个日期均存在的日期开具发票)，然后将其用作我也想生成的实际纸质报告的基础

What I would like to know from those of you with more experience in creating complex queries that I have is if, given the table structures you see above, it would be possible to create a stored procedure taking openingbalance, start and end dates as input parameters and have it produce a table of invoice / invoice payment transactions in date order (defaulting to invoice first on dates where there are both) that I could then use as the base for a physical paper report that I would also like to produce.

我不希望有人为我编写查询，我真的想从给定的结构中知道它是否可行，也许您为我提供了一个正确的方向来开始生成查询的微妙指针.如果我被卡住了，我总是可以回来的，但是现在我真的很想知道是否有可能，更重要的是，如果不这样做的话.我在表结构中错过了什么?

I don't expect anyone to write the query for me, I really want to know if it's feasible from the given structure and perhaps have you provide a subtle pointer in the right direction for me to start producing the query. If I get stuck I can always come back but for now I'd really like to know if it's possible, and perhaps more importantly if not why not. What Did I miss in the table structures?

推荐答案

如果是的话，答案是肯定的，例如客户将ContactsStatements连接到其他表.如果ContactsStatements.ContactId是客户联系人，并且您具有包含客户列表的表，则可以先加入ContactsStatements.ContactId = [customer].ContactId，然后再加入[customer].CustomerId = Invoices.CustomerId

The answer is yes, if you have one more table, like customer to connect ContactsStatements to the other tables. If ContactsStatements.ContactId is customer contact and you have table with list of customers, then you can join ContactsStatements.ContactId = [customer].ContactId and then [customer].CustomerId = Invoices.CustomerId

如果能够做到这一点，则可以毫无问题地创建上述过程.

If you are able to achieve this, you can create mentioned procedure without any problem.

这篇关于在SQL中创建财务报表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！