Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Table-Valued Function SQL
if exists(select * from sys.objects where object_id = object_id('sp_GetBalanceSheet'))
drop function sp_GetBalanceSheet
go
CREATE FUNCTION dbo.sp_GetBalanceSheet(@period char(6))
RETURNS @statistics TABLE (
CompanyID int not null default(0),
Code int not null Identity(1,1),
BranchID int not null,
Description nvarchar(255) not null,
Balance decimal(19,4) not Null
)
begin
--Calculating Cash Assets
INSERT INTO @statistics (CompanyID, BranchID, Description, Balance)
Select g.CompanyID, g.BranchID, 'Cash & Cash Equivalents - ' + Max(a.Description), Sum(g.FinYtdBalance) from GLHistory g
Inner Join Account a on g.CompanyID = a.CompanyID and g.AccountID = a.AccountID
Where a.AccountClassID = 'CASHASSET' and (@period is null or g.FinPeriodID = @period )
Group By g.CompanyID, g.BranchID, g.AccountID
--Calculation AR
INSERT INTO @statistics (CompanyID, BranchID, Description, Balance)
Select g.CompanyID, g.BranchID, 'Account Receivable', Sum(g.FinYtdBalance) from GLHistory g
Inner Join Account a on g.CompanyID = a.CompanyID and g.AccountID = a.AccountID
Where a.AccountClassID = 'AR' and (@period is null or g.FinPeriodID = @period )
Group By g.CompanyID, G.BranchID
return
END
GO
if exists(select * from sys.objects where object_id = object_id('StatisticksView') and objectproperty(object_id,'IsView')=1)
drop view StatisticksView
go
CREATE VIEW StatisticksView
AS
SELECT * FROM sp_GetBalanceSheet('201301') AS Stat
GO
SELECT *
FROM StatisticksView
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.