RE: Calculating percentages within same scope

michielbijnen

Ten Centuries

Points: 1308

July 5, 2014 at 4:16 am

#1727143

Okay, I have prepared some code:

Create table:

/****** Object: Table [dbo].[_FinancialReportingTEST] Script Date: 5-7-2014 10:36:19 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[_FinancialReportingTEST](

[ID] [int] IDENTITY(1,1) NOT NULL,

[GeneralLedgerAccount] [nchar](9) NOT NULL,

[GeneralLedgerDescription] [nchar](60) NOT NULL,

[GeneralLedgerAccountDivision] [nchar](9) NULL,

[GeneralLedgerDescriptionDivision] [nchar](60) NULL,

[Company] [nchar](3) NULL,

[CompanyName] [nvarchar](60) NULL,

[CompanyClass1Code] [nvarchar](30) NULL,

[CompanyClass1Description] [nvarchar](60) NULL,

[CompanyClass3Code] [nvarchar](30) NULL,

[CompanyClass3Description] [nvarchar](60) NULL,

[CompanyClass4Code] [nvarchar](30) NULL,

[CompanyClass4Description] [nvarchar](60) NULL,

[FinancialYear] [smallint] NULL,

[FinancialPeriod] [smallint] NULL,

[FinancialQuarter] [smallint] NULL,

[GeneralLedgerType] [nchar](1) NULL,

[EntryNumber] [nvarchar](20) NULL,

[Journal] [nchar](10) NULL,

[DescriptionEntryLine] [nvarchar](60) NULL,

[Amount] [float] NULL,

[Quantity] [float] NULL,

[UnitCode] [nchar](8) NULL,

[CreditorNumber] [nvarchar](12) NULL,

[CreditorName] [nvarchar](100) NULL,

[DebtorNumber] [nvarchar](12) NULL,

[DebtorName] [nvarchar](100) NULL,

[CostcenterCode] [nchar](10) NULL,

[CostcenterDescription] [nchar](60) NULL,

[CostUnitCode] [nchar](10) NULL,

[YourRef] [nvarchar](30) NULL,

[Date] [datetime] NULL,

[ReportingDate] [datetime] NULL,

[StartDatePeriod] [datetime] NULL,

[EndDatePeriod] [datetime] NULL,

[InvoiceNumber] [nchar](8) NULL,

[DocumentURL] [nchar](200) NULL,

[ResourceNumber] [int] NULL,

[BudgetVersion] [nchar](8) NULL,

[BudgetDescription] [nchar](60) NULL,

[ItemCode] [nvarchar](30) NULL,

[ItemDescription] [nvarchar](100) NULL,

[Category6Code] [nvarchar](30) NULL,

[Category6Description] [nchar](160) NULL,

[Category7Code] [nvarchar](30) NULL,

[Category7Description] [nvarchar](160) NULL,

[Category8Code] [nvarchar](30) NULL,

[Category8Description] [nvarchar](160) NULL,

[Category9Code] [nvarchar](30) NULL,

[Category9Description] [nvarchar](160) NULL,

[Category10Code] [nvarchar](30) NULL,

[Category10Description] [nvarchar](160) NULL,

CONSTRAINT [PK_ManRappFin] PRIMARY KEY CLUSTERED

(

[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

Insert 10 rows for testing:

INSERT INTO _FinancialReportingTEST

(

GeneralLedgerAccount,

GeneralLedgerDescription,

GeneralLedgerAccountDivision,

GeneralLedgerDescriptionDivision,

Company,

CompanyName,

CompanyClass1Code,

CompanyClass1Description,

CompanyClass3Code,

CompanyClass3Description,

CompanyClass4Code,

CompanyClass4Description,

FinancialYear,

FinancialPeriod,

FinancialQuarter,

GeneralLedgerType,

EntryNumber,

Journal,

DescriptionEntryLine,

Amount,

Quantity,

UnitCode,

CreditorNumber,

CreditorName,

DebtorNumber,

DebtorName,

CostcenterCode,

CostcenterDescription,

CostUnitCode,

YourRef,

Date,

ReportingDate,

StartDatePeriod,

EndDatePeriod,

InvoiceNumber,

DocumentURL,

ResourceNumber,

BudgetVersion,

BudgetDescription,

ItemCode,

ItemDescription,

Category6Code,

Category6Description,

Category7Code,

Category7Description,

Category8Code,

Category8Description,

Category9Code,

Category9Description,

Category10Code,

Category10Description

)

VALUES

(

'4000',

'GL Account Division 1',

'40000',

'GL Account Company 1',

'4',

'Company 1',

'Comp',

'Company',

'NL',

'Comp Netherlands',

'BR',

'Comp Netherlands Brabant',

2014,

1,

'W',

'14510014',

'51',

'Description',

240041.1,

2728.02,

'Unknown',

'410532',

'Creditor 1',

'Unknown',

'4290',

'CostCenter',

'9000',

'20140001',

'2014-01-10 00:00:00.000',

'2014-01-01 00:00:00.000',

'2014-01-31 00:00:00.000',

'20092139',

'http://someapp01/somewebsite/docs/DocView.aspx?DocumentID={A77AC40E-B6EA-4F07-AC40-F6E00FDA0440}',

6066,

'Actuals',

'Unknown',

'40',

'EBITDA',

'100',

'Gross Margin',

'1000',

'Net Turnover',

'Unknown',

'Unknown'

)

,

(

'4100',

'GL Account Division 2',

'41000',

'GL Account Company 2',

'4',

'Company 2',

'Comp',

'Company',

'NL',

'Comp Netherlands',

'BR',

'Comp Netherlands Brabant',

2014,

1,

'W',

'14510014',

'51',

'Description',

40041.1,

728.02,

'Unknown',

'410532',

'Creditor 2',

'Unknown',

'4290',

'CostCenter',

'9000',

'20140001',

'2014-01-10 00:00:00.000',

'2014-01-01 00:00:00.000',

'2014-01-31 00:00:00.000',

'20092139',

'http://someapp01/somewebsite/docs/DocView.aspx?DocumentID={F09D5B4F-29AC-4F18-A2F2-72A5B5F40CB0}',

6066,

'Actuals ',

'Unknown',

'40',

'EBITDA',

'100',

'Gross Margin',

'1000',

'Net Turnover',

'Unknown',

'Unknown'

)

,

(

'4200',

'GL Account Division 3',

'42000',

'GL Account Company 3',

'4',

'Company 3',

'Comp',

'Company',

'NL',

'Comp Netherlands',

'BR',

'Comp Netherlands Brabant',

2014,

2,

1,

'W',

'14510016',

'51',

'Description',

30426,

553.2,

'Unknown',

'410532',

'Creditor 3',

'Unknown',

'4290',

'CostCenter',

'9000',

'20140002',

'2014-02-17 00:00:00.000',

'2014-02-01 00:00:00.000',

'2014-02-28 00:00:00.000',

'20092143',

'http://someapp01/somewebsite/docs/DocView.aspx?DocumentID={353A4A84-8129-4352-8112-7590FBD3D7CC}',

6066,

'Actuals ',

'Unknown',

'40',

'EBITDA',

'100',

'Gross Margin',

'2000',

'Raw Materials',

'Unknown',

'Unknown'

)

,

(

'4300',

'GL Account Division 4',

'43000',

'GL Account Company 4',

'4',

'Company 4',

'Comp',

'Company',

'NL',

'Comp Netherlands',

'BR',

'Comp Netherlands Brabant',

2014,

2,

1,

'W',

'14510036',

'51',

'Description',

30426,

553.2,

'Unknown',

'410532',

'Creditor 4',

'Unknown',

'4290',

'CostCenter',

'9000',

'20140005',

'2014-02-24 00:00:00.000',

'2014-02-01 00:00:00.000',

'2014-02-28 00:00:00.000',

'20092258',

'http://someapp01/somewebsite/docs/DocView.aspx?DocumentID={4B958E6C-A00F-4D82-9224-CFE6C3F91A32}',

6066,

'Actuals ',

'Unknown',

'40',

'EBITDA',

'100',

'Gross Margin',

'2000',

'Raw Materials',

'Unknown',

'Unknown'

)

,

(

'4400',

'GL Account Division 5',

'44000',

'GL Account Company 5',

'4',

'Company 5',

'Comp',

'Company',

'NL',

'Comp Netherlands',

'BR',

'Comp Netherlands Brabant',

2014,

2,

1,

'W',

'14510036',

'51',

'Description',

30426,

553.2,

'Unknown',

'410532',

'Creditor 5',

'Unknown',

'4290',

'CostCenter',

'9000',

'20140005',

'2014-02-24 00:00:00.000',

'2014-02-01 00:00:00.000',

'2014-02-28 00:00:00.000',

'20092258',

'http://someapp01/somewebsite/docs/DocView.aspx?DocumentID={A91E71A9-82CC-4D51-8B3B-4E3AFC8E55DA}',

6066,

'Actuals ',

'Unknown',

'40',

'EBITDA',

'400',

'Costs',

'4000',

'Wages and Salaries',

'Unknown',

'Unknown'

)

,

(

'4500',

'GL Account Division 6',

'45000',

'GL Account Company 6',

'4',

'Company 6',

'Comp',

'Company',

'NL',

'Comp Netherlands',

'BR',

'Comp Netherlands Brabant',

2014,

3,

1,

'W',

'14510038',

'51',

'Description',

7157.7,

130.14,

'Unknown',

'410532',

'Creditor 6',

'Unknown',

'4290',

'CostCenter',

'9000',

'20140006',

'2014-03-24 00:00:00.000',

'2014-03-01 00:00:00.000',

'2014-03-31 00:00:00.000',

'20092304',

'http://someapp01/somewebsite/docs/DocView.aspx?DocumentID={32936F30-2951-44AE-A010-C066D4D55668}',

6066,

'Actuals ',

'Unknown',

'40',

'EBITDA',

'400',

'Costs',

'4000',

'Wages and Salaries',

'Unknown',

'Unknown'

)

,

(

'4600',

'GL Account Division 7',

'46000',

'GL Account Company 7',

'4',

'Company 7',

'Comp',

'Company',

'NL',

'Comp Netherlands',

'BR',

'Comp Netherlands Brabant',

2014,

3,

1,

'W',

'14510044',

'51',

'Description',

37780.6,

686.92,

'Unknown',

'410532',

'Creditor 7',

'Unknown',

'4290',

'CostCenter',

'9000',

'20140007',

'2014-03-31 00:00:00.000',

'2014-03-01 00:00:00.000',

'2014-03-31 00:00:00.000',

'20092342',

'http://someapp01/somewebsite/docs/DocView.aspx?DocumentID={6D962F1E-2A44-4F11-BC47-B26F28CA9246}',

6066,

'Actuals ',

'Unknown',

'40',

'EBITDA',

'400',

'Costs',

'6000',

'Housing',

'Unknown',

'Unknown'

)

,

(

'4700',

'GL Account Division 8',

'47000',

'GL Account Company 8',

'4',

'Company 8',

'Comp',

'Company',

'NL',

'Comp Netherlands',

'BR',

'Comp Netherlands Brabant',

2014,

4,

2,

'W',

'14510048',

'51',

'Description',

30542.6,

555.32,

'Unknown',

'410532',

'Creditor 8',

'Unknown',

'4290',

'CostCenter',

'9000',

'20140010',

'2014-04-07 00:00:00.000',

'2014-04-01 00:00:00.000',

'2014-04-30 00:00:00.000',

'20092376',

'http://someapp01/somewebsite/docs/DocView.aspx?DocumentID={24DA0D0C-FC7D-481B-8DF7-D9CF73A33DE0}',

6066,

'Actuals ',

'Unknown',

'40',

'EBITDA',

'400',

'Costs',

'7000',

'Sales',

'Unknown',

'Unknown'

)

,

(

'4800',

'GL Account Division 9',

'48000',

'GL Account Company 9',

'4',

'Company 9',

'Comp',

'Company',

'NL',

'Comp Netherlands',

'BR',

'Comp Netherlands Brabant',

2014,

5,

2,

'W',

'14510057',

'51',

'Description',

30797.8,

559.96,

'Unknown',

'410532',

'Creditor 9',

'Unknown',

'4290',

'CostCenter',

'9000',

'20140011',

'2014-05-14 00:00:00.000',

'2014-05-01 00:00:00.000',

'2014-05-31 00:00:00.000',

'20092492',

'http://someapp01/somewebsite/docs/DocView.aspx?DocumentID={B3A9FCA6-8DC9-4C15-842C-1406BC9D5E10}',

6066,

'Actuals ',

'Unknown',

'40',

'EBITDA',

'400',

'Costs',

'3000',

'General',

'Unknown',

'Unknown'

)

,

(

'4900',

'GL Account Division 10',

'49000',

'GL Account Company 10',

'4',

'Company 10',

'Comp',

'Company',

'NL',

'Comp Netherlands',

'BR',

'Comp Netherlands Brabant',

2014,

7,

3,

'W',

'14510064',

'51',

'Description',

38775,

705,

'Unknown',

'410532',

'Creditor 10',

'Unknown',

'4290',

'CostCenter',

'9000',

'20140013',

'2014-07-21 00:00:00.000',

'2014-07-01 00:00:00.000',

'2014-07-31 00:00:00.000',

'20092565',

'http://someapp01/somewebsite/docs/DocView.aspx?DocumentID={A2A6C6A8-DECD-44BE-B84E-A304E9B4127D}',

6066,

'Actuals ',

'Unknown',

'40',

'EBITDA',

'400',

'Costs',

'3000',

'General',

'Unknown',

'Unknown'

);

Attached is a screenshot (from Excel) what I would like to achieve.

Also a screenshot of the actual RDL that i use.

My table has field Category9Code with Net Turnover that I would like to have as 100%.

The other Category9Codes should be calculated as a percentage of this Net Turnover.

Thanks!