Last active
September 18, 2015 16:25
-
-
Save smarenich/4d7a28f6ce2a2c4e93a6 to your computer and use it in GitHub Desktop.
Table-Valued Function SQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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