SQLTeam.com | Weblogs | Forums

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

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

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

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

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

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
;

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.

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

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 '',''