How to calculate previous year retained earnings based on fiscal date in sql

1

Please I,m new sql so I have no code. Any help will be appreciated.

2

What data do you have?
What output do you expect?

Please provide the table definition and example data for both input and output.

3

CREATE TABLE [dbo].[journal_voucher_t](
[jv_id] [int] IDENTITY(1,1) NOT NULL,
[jv_date] [date] NOT NULL,
[coa_name] nvarchar NOT NULL,
[debit] [money] NOT NULL,
[credit] [money] NOT NULL,
CONSTRAINT [PK_journal_voucher_t] PRIMARY KEY CLUSTERED
(
[jv_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

4

Something like this perhaps:

declare @fiscal_start date=cast('2014-09-01' as date);

select coa_name
      ,sum(debit) as debit
      ,sum(credit) as credit
  from dbo.journal_voucher_t
 where jv_date>=@fiscal_start
   and jv_date<dateadd(year,1,@fiscal_start)
 group by coa_name
;
5

Thank you for your reply. But this does not solve my problem. What I want is, at the of every year, there should be a function or stored procedure that will deduct all expense from income and roll it up to balance sheet.

6

Which tables are to be rolled up?

Please provide:

  • table description in form of create statement(s)
  • sample data in form of insert statement(s)
  • expected result from your sample data
7

Below is my table with sample data

CREATE TABLE [dbo].[chart_of_account_t](
[coa_id] [int] IDENTITY(1,1) NOT NULL,
[coa_code] nvarchar NOT NULL,
[coa_name] nvarchar NOT NULL,
[coa_type] nvarchar NOT NULL,
[coa_type_cate] nvarchar NOT NULL,
[ext_rep] nvarchar NULL,
CONSTRAINT [PK_chart_of_account_t] PRIMARY KEY CLUSTERED
(
[coa_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[journal_voucher_t](
[jv_id] [int] IDENTITY(1,1) NOT NULL,
[jv_date] [date] NOT NULL,
[coa_name] nvarchar NOT NULL,
[debit] [money] NOT NULL,
[credit] [money] NOT NULL,
CONSTRAINT [PK_journal_voucher_t] PRIMARY KEY CLUSTERED
(
[jv_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Insert Into journal_voucher_t (jv_date ,coa_name ,debit ,credit ) Values ('2014-09-01','Cash in Vault','500000.0000','0.0000')
Insert Into journal_voucher_t (jv_date ,coa_name ,debit ,credit ) Values ('2014-09-01','Savings','0.0000','500000.0000')
Insert Into journal_voucher_t (jv_date ,coa_name ,debit ,credit ) Values ('2014-09-01','Cash in Vault','10000.0000','0.0000')
Insert Into journal_voucher_t (jv_date ,coa_name ,debit ,credit ) Values ('2014-09-01','Income from Stock','0.0000','10000.0000')

This stored procedure is working fine. But what i want is a function in the stored procedure that will look at the fiscal end date and auto insert net income into the database as a retained earnings.

CREATE PROCEDURE [dbo].[mfi_PL]
AS
SELECT 'Income :' AS [Accounts],CAST('+' AS Int) AS [US]

UNION ALL

SELECT chart_of_account_t.coa_name AS 'Income', CAST(SUM(Credit-Debit)AS INT)AS [US]
FROM chart_of_account_t JOIN journal_voucher_t
ON chart_of_account_t .coa_name = journal_voucher_t .coa_name
WHERE chart_of_account_t.coa_type IN ('REV') and chart_of_account_t .coa_type_cate in ('PL')
GROUP BY chart_of_account_t .coa_name,chart_of_account_t .coa_code

UNION ALL SELECT '', '' UNION ALL

SELECT 'Total Income', CAST(SUM(Credit-Debit)AS INT)AS [US]
FROM chart_of_account_t JOIN journal_voucher_t
ON chart_of_account_t .coa_name = journal_voucher_t .coa_name
WHERE chart_of_account_t .coa_type IN ('REV')

UNION ALL SELECT '', '' UNION ALL SELECT '','' UNION ALL

SELECT 'Expenses :' AS [Account Name],CAST('+' AS Int) AS [US]

UNION ALL

SELECT 'Personnel Expenses :' AS [Account Name],CAST('+' AS Int) AS [US]
UNION ALL SELECT chart_of_account_t.coa_name AS 'Personnel Expenses', CAST(SUM(Debit-Credit)AS INT)AS [US]
FROM chart_of_account_t JOIN journal_voucher_t
ON chart_of_account_t .coa_name = journal_voucher_t .coa_name
WHERE chart_of_account_t.coa_type IN ('PEREX') and chart_of_account_t .coa_type_cate in ('PL')
GROUP BY chart_of_account_t .coa_name,chart_of_account_t .coa_code

UNION ALL SELECT '', '' UNION ALL SELECT '', ''

UNION ALL SELECT 'Financial Expenses :' AS [Account Name],CAST('+' AS Int) AS [US]

UNION ALL

SELECT chart_of_account_t.coa_name AS 'Financial Expenses', CAST(SUM(Debit-Credit)AS INT)AS [US]
FROM chart_of_account_t JOIN journal_voucher_t
ON chart_of_account_t .coa_name = journal_voucher_t .coa_name
WHERE chart_of_account_t.coa_type IN ('FINEX') and chart_of_account_t .coa_type_cate in ('PL')
GROUP BY chart_of_account_t .coa_name,chart_of_account_t .coa_code

UNION ALL SELECT '', '' UNION ALL SELECT '', '' UNION ALL

SELECT 'Administarive Expenses :' AS [Account Name],CAST('+' AS Int) AS [US]
UNION ALL SELECT chart_of_account_t.coa_name AS 'Administarive Expenses', CAST(SUM(Debit-Credit)AS INT)AS [US]
FROM chart_of_account_t JOIN journal_voucher_t
ON chart_of_account_t .coa_name = journal_voucher_t .coa_name
WHERE chart_of_account_t.coa_type IN ('ADMEX') and chart_of_account_t .coa_type_cate in ('PL')
GROUP BY chart_of_account_t .coa_name,chart_of_account_t .coa_code

UNION ALL SELECT '','' union all select '', ''

union all select 'Total Expenses ',cast(sum(Debit-Credit) as INT) as [US]
from chart_of_account_t join journal_voucher_t on chart_of_account_t .coa_name = journal_voucher_t .coa_name
where chart_of_account_t .coa_type in ('PEREX','FINEX','ADMEX') and chart_of_account_t .coa_type_cate in ('PL')

union all select '','' union all select '',''

union all select 'Net Operating Income ',cast(sum(Credit-Debit) as INT) as [US]
from chart_of_account_t join journal_voucher_t on chart_of_account_t .coa_name = journal_voucher_t .coa_name
where chart_of_account_t .coa_type in ('REV','PEREX','FINEX','ADMEX') and chart_of_account_t .coa_type_cate in ('PL')

union all select '','' union all select '','' UNION ALL

SELECT '', '' UNION ALL

SELECT 'Other Non Operating Income :' AS [Account Name],CAST('+' AS Int) AS [US]

UNION ALL

SELECT chart_of_account_t.coa_name AS 'Other Non Operating Income', CAST(SUM(Debit-Credit)AS INT)AS [US]
FROM chart_of_account_t JOIN journal_voucher_t
ON chart_of_account_t .coa_name = journal_voucher_t .coa_name
WHERE chart_of_account_t.ext_rep IN ('GFA','OPC') and chart_of_account_t .coa_type_cate in ('PL')
GROUP BY chart_of_account_t .coa_name,chart_of_account_t .coa_code

UNION ALL SELECT '', '' union all

select 'Total Income ',cast(sum(Credit-Debit) as INT) as [US]
from chart_of_account_t join journal_voucher_t on chart_of_account_t .coa_name = journal_voucher_t .coa_name
where chart_of_account_t .coa_type in ('REV','PEREX','FINEX','ADMEX') and chart_of_account_t .coa_type_cate in ('PL')
union all select '','' union all select '',''