Business Profile Code Module: GEOGRAPHIC UNIT

tori.vanloenhout
21 March 2024

Module Output

SQL and SAS: see below
How to access a code module in the Data Lab : Read here

Context:

This module creates a set of tables and views of permanent enterprises - three each at for the following respective geographic levels the summary dataset pertains to: regional council (RC), territorial authority (TA), and area unit (AU). An Enterprise (ENT) is an institutional unit which generally corresponds to a legal entity operating in New Zealand - that is, to the legal definition of a business or firm. This can be a company, partnership, trust, estate, incorporated society, producer board, local or central government organisation, voluntary organisation, or self-employed individual (DataInfo+). Some enterprises operate in multiple locations. Geographic units (GEOs) refer to these separate operating units. GEOs (or business locations) are units which are engaged in one, or predominantly one, kind of economic activity from a single physical location or base (DataInfo+).

Operational Definition

GEOs (also called “plants” or “establishments”) can be a whole ENT or part of an ENT and are normally a physical location from which predominantly one kind of activity takes place on a permanent basis. Statistics NZ also creates Permanent Business Numbers (PBNs), which identify continuing activity at the level of the geographic unit, repairing breaks in GEO numbers. Information on GEOs can be used to identify the number of geographic units a firm operates, their locations, and the approximate spread of employment across those units.

Location, industry and employment data are available at the GEO level in [load_lbf_fact_business] and [load_lbf_fact_pbn_employee_count] respectively. Researchers also use the PBN-level employment data from the Fabling and Mare Labour tables (Fabling and Mare 2015), which include adjustments to strip out working proprietors and to adjust for observable part-time employment [pent_year_l_idi_yyyymm_rfabling].

The underlying administrative (tax) data used to generate employment information at the firm level does not identify which plants an individual works at. The allocation of individual workers to plants is done by Stats NZ based on known (survey-based) information about the distribution of employment across geographic units and the observed (administrative data-based) home addresses of individual workers. See ( https://www.stats.govt.nz/methods/guide-to-interpreting-the-leed-data ) for information on the allocation mechanism, and Fabling and Mare (2020) for an alternative method and discussion.

Known multi-unit firms are surveyed annually as part of the Business Register updating process to determine the allocation of employment across geographic units. However, Stats NZ does not have independent information about the number of locations a firm operates. As such, some multi-unit firms will appear as single units unless they also meet other criteria to be included in the Business Register Update Survey.

Information at the plant level in the LBD is typically used to allocate firms to different regions, in order to allow analysis based on regional characteristics (eg, employment density, number of migrants, characteristics of neighbouring firms, or the availability of digital infrastructure). Most firms in New Zealand have only a single location. However, larger firms with multiple locations make up a significant share of total employment. Researchers typically allow for multi-location and multi-region firms by generating regional employment shares at the firm-level.

Key Notes

  1. The boundaries of statistical regions have changed over time. Each instance of the LBD is based on the current classification (ie, a firm that has remained at the same location will be listed consistently in the same statistical region even if the boundaries of that region have changed over time). Concordance tables are available to enable linking of external geographic information with the firm level data in the LBF [load_lbf_fact_business].
  2. Reference tables provide information on the various regional classifications used in the BR, including meshblocks, area units, territorial authorities and regional councils.
  3. The set of codes produces tables and views. Where there is a series of processing involved, tables were produced in an optimised extent for the users. Views were created to provide users with more user or purpose friendly virtual tables, whenver possible. The {geo} flag pertains to the level of geography the summary is for, e.g. Regional Council (rc), Territorial Authority (ta), or Area Unit (au).
  4. Each long view represent firms and their fte/rme distribution across geography in long format. Wide format views are also made as users more often use this format in analysis. The limitation is that objects in SQL such as views cannot accommodate more than 1024 columns, hence disaggregation such as area unit is not done.

References & Contacts

  1. Fabling, R., & Mare, D. C. (2015). Production function estimation using New Zealand’s Longitudinal Business Database. Motu Working Paper 15-15. September 2015.
  2. Fabling, R., & Mare, D. C. (2015). Addressing the absence of hours information in linked employer-employee data. Motu Working Paper 15-17. October 2015.
  3. Fabling, R., & Sanderson, L. (2016). A Rough Guide to New Zealand’s Longitudinal Business Database 2nd Ed. Motu Workig Paper 16-03. February 2016.
  4. Fabling, R., & Mare, D. C. (2019). Improved productivity measurement in New Zealand’s Longitudinal Business Database. Motu Working Paper 19-03. April 2019.
  5. Fabling, R., & Mare, D. C. (2020). Measuring commute patterns over time: Using administrative data to identify where employees live and work. Motu Working Paper 20-05. July 2020.
  6. Statistics New Zealand. Linked Employer-Employee Data (LEED) annual technical notes.
  7. Statistics New Zealand DataInfo+. Business Demography Statistics.

Community of Interest

Who Involvement
Lynda Sanderson Lead Subject Matter Expert
Joe Bancolita Initial draft of the code
Corey Allen Contributor
Christopher Bell Contributor
Rodney Jer Contributor
Jeroen Kole Contributor
Samuel Mortlock Contributor
Guanyu “Fish” Zheng Contributor

Parameters

The following parameters should be supplied to this module to run it in the database:

  1. IDI_Sandpit: The SQL database on which the module dataset is to be created.
  2. DL-MAA2020-47: The project schema under the target database into which the module dataset is to be created.
  3. 202310: Version date of the labour tables to be used in YYYYMMDD, e.g. 202111020
  4. pent_geo: The name of this module, e.g. “pent_profile”
  5. {codedb}: Database where the code will be stored, i.e. IDI_UserCode

Dependencies

[ibuldd_clean].[dbo].[pent_pbn_month_l_idi_$(penttbldate)_rfabling]
[idi_adhoc].[clean_read_ir].[pent_year_l_idi_$(penttbldate)_rfabling]
[ibuldd_clean].[dbo].[pent_bal_ind_idi_$(penttbldate)_rfabling]
[ibuldd_clean].[dbo].[ref_meshblock]
[ibuldd_clean].[dbo].[load_lbf_fact_business]

Outputs

{targetdb}.{targetschema}.pent_geo_pbn
{targetdb}.{targetschema}.pent_geo_year_emp_denom
{targetdb}.{targetschema}.pent_geo_year_emp
{codedb}.{targetschema}.pent_geo_year_fte_{geo}_wide
{codedb}.{targetschema}.pent_geo_year_rme_{geo}_wide
{codedb}.{targetschema}.pent_geo_year_{geo}_long

Module output table names

SQL:
[IDI_Community].[pent_geo_pbn].geo_pbn_YYYYMM
[IDI_Community].[pent_geo_year_emp_denom].geo_year_emp_denom_YYYYMM
[IDI_Community].[pent_geo_year_emp].geo_year_emp_YYYYMM
[IDI_Community].[pent_geo_year_fte_rc_wide].geo_year_fte_rc_wide_YYYYMM
[IDI_Community].[pent_geo_year_fte_ta_wide].geo_year_fte_ta_wide_YYYYMM
[IDI_Community].[pent_geo_year_rme_rc_wide].geo_year_rme_rc_wide_YYYYMM
[IDI_Community].[pent_geo_year_rme_ta_wide].geo_year_rme_ta_wide_YYYYMM
[IDI_Community].[pent_geo_year_rc_long].geo_year_rc_long_YYYYMM
[IDI_Community].[pent_geo_year_ta_long].geo_year_ta_long_YYYYMM
[IDI_Community].[pent_geo_year_au_long].geo_year_au_long_YYYYMM

SAS:
libname cmbp ODBC dsn=idi_community_srvprd schema=pent_geo_pbn; proc print data = cmbp.geo_pbn_YYYYMM ; run ;
libname cmbp ODBC dsn=idi_community_srvprd schema=pent_geo_year_emp_denom; proc print data = cmbp.geo_year_emp_denom_YYYYMM ; run ;
libname cmbp ODBC dsn=idi_community_srvprd schema=pent_geo_year_emp; proc print data = cmbp.geo_year_emp_YYYYMM ; run ;
libname cmbp ODBC dsn=idi_community_srvprd schema=pent_geo_year_fte_rc_wide; proc print data = cmbp.geo_year_fte_rc_wide_YYYYMM ; run ;
libname cmbp ODBC dsn=idi_community_srvprd schema=pent_geo_year_fte_ta_wide; proc print data = cmbp.geo_year_fte_ta_wide_YYYYMM ; run ;
libname cmbp ODBC dsn=idi_community_srvprd schema=pent_geo_year_rme_rc_wide; proc print data = cmbp.geo_year_rme_rc_wide_YYYYMM ; run ;
libname cmbp ODBC dsn=idi_community_srvprd schema=pent_geo_year_rme_ta_wide; proc print data = cmbp.geo_year_rme_ta_wide_YYYYMM ; run ;
libname cmbp ODBC dsn=idi_community_srvprd schema=pent_geo_year_rc_long; proc print data = cmbp.geo_year_rc_long_YYYYMM ; run ;
libname cmbp ODBC dsn=idi_community_srvprd schema=pent_geo_year_ta_long; proc print data = cmbp.geo_year_ta_long_YYYYMM ; run ;
libname cmbp ODBC dsn=idi_community_srvprd schema=pent_geo_year_au_long; proc print data = cmbp.geo_year_au_long_YYYYMM ; run ;

Variable Descriptions

PBN_PENT_GEO - IDI_Sandpit Table

Contains monthly employment summary at the plant and its geography (rc, ta, or au).

Aspect Variables Description
Entity pent The Permanent Enterprise Number of the firm.
pbn_nbr Permanent Business Number
Period dim_year_key Financial year (aligned to closest March year, in YYYY03)
dim_month_key Month in format (in format YYYYMM)
Event information fte Total FTE employment measure of labour input at PBN. In each of the table with geo, this is estimated for that geography.
employee_count Headcount measure of employees at PBN. In each of the table with geo, this is estimated for that geography.

YEAR_EMP_DENOM_PENT_GEO - IDI_Sandpit Table

Contains annual employment summary at the firm level which will serve as denominator for computing share within a geography.

Aspect Variables D escription
Entity pent The Permanent Enterprise Number of the firm.
Period dim_year_key Financial year (aligned to closest March year, in YYYY03)
Event information tot_fte FTE employment (12-month average) in dim_year_key for the whole firm.
tot_rme Employee headcount (12-month average) in dim_year_key (rolling mean employment) for the whole firm.

YEAR_EMP_PENT_GEO - IDI_Sandpit Table

A long table containing annual firm level employment sumamry at each geography rc, ta, and au together with the computed share of employment at that geographic level.

Aspect Variables Description
Entity pent The Permanent Enterprise Number of the firm.
Period dim_year_key Financial year (aligned to closest March year, in YYYY03)
Event information geo Geographic level.
geo_code Geographic code.
fte FTE employment (month or 12-month average) in dim_year_key. In each of the table with geo, this is estimated for that geography.
rme Employee headcount (month or 12-month average) in dim_year_key (rolling mean employment). In each of the table with geo, this is estimated for that geography.
fte_pr Ratio of fte in dim_year_key for each geo to the whole firm, i.e. from denom table, e.g. 20 out of 100 fte in Wellington, 80 out 100 in Auckland, etc.
rme_pr Ratio of rme in dim_year_key for each geo to the whole firm, i.e. from denom table, e.g. 1 out of ten people in Christchurch, 9 out 10 in Auckland, etc.

IDI_UserCode Views

Each long view represent firms and their fte/rme distribution across geography in long format. Wide format views are also made as users more often use this format in analysis. The limitation is that objects in SQL such as views cannot accommodate more than 1024 columns, hence disaggregation such as area unit is not done.

  1. [{codedb}].[{targetschema}].[pent_geo_year_fte_{geo}_wide] - A wide layout of firms’ annual average of monthly fte (rows) over different geography (rc, ta only).
  2. [{codedb}].[{targetschema}].[pent_geo_year_rme_{geo}_wide] - A wide layout of firms’ annual average of monthly rme (rows) over different geography (rc, ta only).
  3. [{codedb}].[{targetschema}].[pent_geo_year_{geo}_long] - An alternate layout of the year_emp_pent_geo table written to the IDI_Sandpit with geography labels (rc, ta and au).

Module Version & Change History

Date Version Comments
1-Sep-22 L Sanderson - v0 Base Code
22-Sep-22 J Bancolita - First Review
27-Sep-22 J Bancolita - Modularise
30-Sep-22 J Bancolita - Parameterise
7-Oct-22 J Bancolita - Encapsulation
19-Jan-24 T Van Loenhout - streamlining and documenting for modular code
05 Jun-24 T Van Loenhout - Changing output names and updating source datasets

Code

***********************************************************************************/
/* Set Parameters */

/*PARAMETERS
SQLCMD only (Activate by clicking Query->SQLCMD Mode)
*/

:setvar codedb "IDI_Usercode"
:setvar targetdb "IDI_Sandpit"
:setvar targetschema "DL-MAA2020-47"
:setvar penttbldate "202310"
:setvar module "pent_geo"
:setvar idimetadataversion "IDI_Metadata_202406"

USE [$(codedb)];

DROP TABLE IF EXISTS [$(targetdb)].[$(targetschema)].[$(module)_pbn];
GO

CREATE TABLE [$(targetdb)].[$(targetschema)].[$(module)_pbn] (
    pent char(10) NOT NULL
	, pbn_nbr char(10) NOT NULL
	, dim_year_key int NOT NULL
	, dim_month_key int NOT NULL
	, fte float NOT NULL
	, employee_count int NOT NULL
	, au int NOT NULL
	, ta int NOT NULL
	, rc int NOT NULL
);

INSERT INTO [$(targetdb)].[$(targetschema)].[$(module)_pbn] (
    pent
    , pbn_nbr
    , dim_year_key
    , dim_month_key
    , fte
    , employee_count
    , au
    , ta
    , rc
	)

	SELECT
	    pent
		, b.pbn_nbr
		, dim_year_key
		, dim_month_key
		, fte
		, employee_count
		, au=area_unit_code
		, ta=ta_code
		, rc=rc_code
	FROM [ibuldd_clean].[dbo].[pent_pbn_month_l_idi_$(penttbldate)_rfabling] b
	JOIN [ibuldd_clean].dbo.load_lbf_fact_business p ON b.pbn_nbr=p.pbn_nbr AND b.dim_month_key BETWEEN dim_start_month_key AND dim_end_month_key
	LEFT JOIN [$(idimetadataversion)].[data].[meshblock_concordance] mc on p.geo_meshblock_code = mc.MB2023_code
    JOIN [ibuldd_clean].[dbo].[ref_meshblock] m ON mc.MB2018_code = m.meshblock_code
	WHERE b.pent IN (SELECT DISTINCT 
		                pent
						FROM [ibuldd_clean].[dbo].[pent_bal_ind_IDI_$(penttbldate)_rfabling] 
						)
		AND b.pent IN (SELECT DISTINCT 
		                pent
						FROM [IDI_Adhoc].[clean_read_IR].[pent_year_l_idi_$(penttbldate)_rfabling]
						);

ALTER TABLE [$(targetdb)].[$(targetschema)].[$(module)_pbn] 
	ADD PRIMARY KEY clustered (
	pent
	, pbn_nbr
	, dim_month_key
	, dim_year_key);

DROP TABLE IF EXISTS [$(targetdb)].[$(targetschema)].[$(module)_year_emp_denom];
GO

CREATE TABLE [$(targetdb)].[$(targetschema)].[$(module)_year_emp_denom]  (
    pent char(10) NOT NULL
	, dim_year_key int NOT NULL
	, tot_fte float NOT NULL
	, tot_rme float NOT NULL PRIMARY KEY clustered(pent, dim_year_key));

INSERT INTO [$(targetdb)].[$(targetschema)].[$(module)_year_emp_denom]  (
    pent
    , dim_year_key
    , tot_fte
    , tot_rme)

	SELECT 
	    pent
		, dim_year_key
		, tot_fte=sum(fte/12.0) 
		, tot_rme=sum(employee_count/12.0)
	FROM [$(targetdb)].[$(targetschema)].[$(module)_pbn] 
	GROUP BY 
	pent
	, dim_year_key;

DROP TABLE IF EXISTS [$(targetdb)].[$(targetschema)].[$(module)_year_emp];
GO

CREATE TABLE [$(targetdb)].[$(targetschema)].[$(module)_year_emp] (
    pent char(10) NOT NULL
    , dim_year_key int NOT NULL
    , geo char(2) NOT NULL
    , geo_code int NOT NULL
    , fte float NOT NULL
    , rme float NOT NULL
    , fte_pr float NOT NULL
    , rme_pr float NOT NULL	);

INSERT INTO [$(targetdb)].[$(targetschema)].[$(module)_year_emp]
						SELECT 
						    a.pent 
							, a.dim_year_key
							, 'rc' as geo
							, a.rc as geo_code
							, sum(a.fte)/12.0 as fte
							, sum(a.employee_count)/12.0 as rme
							, (sum(a.fte)/12.0)/b.tot_fte as fte_pr
							, (sum(a.employee_count)/12.0)/b.tot_rme as rme_pr
						FROM [$(targetdb)].[$(targetschema)].[$(module)_pbn] a
						JOIN  [$(targetdb)].[$(targetschema)].[$(module)_year_emp_denom] b ON a.pent=b.pent AND a.dim_year_key=b.dim_year_key
						GROUP BY 
						a.pent
						,a.dim_year_key
						,a.rc
						,b.tot_fte
						,b.tot_rme

						UNION ALL

						SELECT 
						    a.pent 
							, a.dim_year_key
							, 'ta' as geo
							, a.ta as geo_code
							, sum(a.fte)/12.0 as fte
							, sum(a.employee_count)/12.0 as rme
							, (sum(a.fte)/12.0)/b.tot_fte as fte_pr
							, (sum(a.employee_count)/12.0)/b.tot_rme as rme_pr
						FROM [$(targetdb)].[$(targetschema)].[$(module)_pbn] a
						JOIN  [$(targetdb)].[$(targetschema)].[$(module)_year_emp_denom] b ON a.pent=b.pent AND a.dim_year_key=b.dim_year_key
						GROUP BY 
						a.pent
						,a.dim_year_key
						,a.ta
						,b.tot_fte
						,b.tot_rme

						
						UNION ALL

						SELECT 
						    a.pent 
							, a.dim_year_key
							, 'au' as geo
							, a.au as geo_code
							, sum(a.fte)/12.0 as fte
							, sum(a.employee_count)/12.0 as rme
							, (sum(a.fte)/12.0)/b.tot_fte as fte_pr
							, (sum(a.employee_count)/12.0)/b.tot_rme as rme_pr
						FROM [$(targetdb)].[$(targetschema)].[$(module)_pbn] a
						JOIN  [$(targetdb)].[$(targetschema)].[$(module)_year_emp_denom] b ON a.pent=b.pent AND a.dim_year_key=b.dim_year_key
						GROUP BY 
						a.pent
						,a.dim_year_key
						,a.au
						,b.tot_fte
						,b.tot_rme;
	

ALTER TABLE [$(targetdb)].[$(targetschema)].[$(module)_year_emp] 
ADD PRIMARY KEY clustered(
    pent
    , dim_year_key
    , geo
	, geo_code);

	GO

/* Long views */

/*regional council*/
IF OBJECT_ID('[$(targetschema)].[$(module)_year_rc_long]','V') IS NOT NULL
	DROP VIEW [$(targetschema)].[$(module)_year_rc_long]
GO

CREATE VIEW [$(targetschema)].[$(module)_year_rc_long]  AS
	SELECT
		[pent]
		,[dim_year_key]
		,geo_code as rc
		,rc_name
		,[fte]
		,[rme]
		,[fte_pr]
		,[rme_pr]
	FROM [$(targetdb)].[$(targetschema)].[$(module)_year_emp] a
	INNER JOIN (
		SELECT DISTINCT 
		cast(rc_code as int) as rc_code
		,rc_name_text as rc_name 
		FROM [ibuldd_clean].[dbo].[ref_meshblock]
	) b ON a.geo_code=b.rc_code
	WHERE [geo]='rc'
	GO

/*territorial authority*/
IF OBJECT_ID('[$(targetschema)].[$(module)_year_ta_long]','V') IS NOT NULL
	DROP VIEW [$(targetschema)].[$(module)_year_ta_long]
GO

CREATE VIEW [$(targetschema)].[$(module)_year_ta_long]  AS
	SELECT 
		[pent]
		,[dim_year_key]
		,geo_code as ta
		,ta_name
		,[fte]
		,[rme]
		,[fte_pr]
		,[rme_pr]
	FROM [$(targetdb)].[$(targetschema)].[$(module)_year_emp] a
	INNER JOIN (
	SELECT DISTINCT 
		cast(ta_code as int) as ta_code
		,ta_name_text as ta_name 
		FROM [ibuldd_clean].[dbo].[ref_meshblock]
	) b ON a.geo_code=b.ta_code
	WHERE [geo]='ta'
	GO

/*area unit*/
IF OBJECT_ID('[$(targetschema)].[$(module)_year_au_long]','V') IS NOT NULL
	DROP VIEW [$(targetschema)].[$(module)_year_au_long]
GO

CREATE VIEW [$(targetschema)].[$(module)_year_au_long]  AS
	SELECT 
		[pent]
		,[dim_year_key]
		,geo_code as au
		,au_name
		,[fte]
		,[rme]
		,[fte_pr]
		,[rme_pr]
	FROM [$(targetdb)].[$(targetschema)].[$(module)_year_emp] a
	INNER JOIN (
	SELECT DISTINCT 
		cast(area_unit_code as int) as au_code
		,area_unit_name_text as au_name 
		FROM [ibuldd_clean].[dbo].[ref_meshblock]
	) b ON a.geo_code=b.au_code
	WHERE [geo]='au'
	GO

/* Wide views */

/*rme wide regional council*/
IF OBJECT_ID('[$(targetschema)].[$(module)_year_rme_rc_wide]','V') IS NOT NULL
	DROP VIEW [$(targetschema)].[$(module)_year_rme_rc_wide]
GO

CREATE VIEW [$(targetschema)].[$(module)_year_rme_rc_wide]  AS
	SELECT 
		pva.pent
		,pva.dim_year_key
		,b.tot_rme
		,[1] as rme_1
		,[2] as rme_2
		,[3] as rme_3
		,[4] as rme_4
		,[5] as rme_5
		,[6] as rme_6
		,[7] as rme_7
		,[8] as rme_8
		,[9] as rme_9
		,[12] as rme_12
		,[13] as rme_13
		,[14] as rme_14
		,[15] as rme_15
		,[16] as rme_16
		,[17] as rme_17
		,[18] as rme_18
	FROM (
		SELECT *
		FROM (
			SELECT 
				pent
				,dim_year_key
				,rme
				,geo_code
			FROM [$(targetdb)].[$(targetschema)].[$(module)_year_emp]
			WHERE geo = 'rc'
			) as t
		PIVOT (
		sum([rme]) 
		FOR [geo_code] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[12],[13],[14],[15],[16],[17],[18])
		) as pivot_table
	) AS pva
	INNER JOIN [$(targetdb)].[$(targetschema)].[$(module)_year_emp_denom] b ON pva.pent=b.pent and pva.dim_year_key=b.dim_year_key
	GO

/*rme wide territorial authority*/
IF OBJECT_ID('[$(targetschema)].[$(module)_year_rme_ta_wide]','V') IS NOT NULL
	DROP VIEW [$(targetschema)].[$(module)_year_rme_ta_wide]
GO

CREATE VIEW [$(targetschema)].[$(module)_year_rme_ta_wide]  AS
	SELECT 
		pva.pent
		,pva.dim_year_key
		,b.tot_rme
		,[1] as rme_1 ,[2] as rme_2	,[3] as rme_3 ,[11] as rme_11 ,[12] as rme_12 ,[13] as rme_13 ,[15] as rme_15 ,[16] as rme_16 ,[17] as rme_17
		,[18] as rme_18	,[19] as rme_19	,[20] as rme_20	,[21] as rme_21	,[22] as rme_22	,[23] as rme_23	,[24] as rme_24	,[25] as rme_25	,[26] as rme_26
		,[27] as rme_27 ,[28] as rme_28 ,[29] as rme_29 ,[30] as rme_30 ,[31] as rme_31 ,[32] as rme_32 ,[33] as rme_33 ,[34] as rme_34 ,[35] as rme_35
		,[36] as rme_36 ,[37] as rme_37 ,[38] as rme_38 ,[39] as rme_39 ,[40] as rme_40 ,[41] as rme_41 ,[42] as rme_42 ,[43] as rme_43 ,[44] as rme_44
		,[45] as rme_45 ,[46] as rme_46 ,[47] as rme_47 ,[48] as rme_48 ,[49] as rme_49 ,[50] as rme_50 ,[51] as rme_51 ,[52] as rme_52 ,[53] as rme_53
		,[54] as rme_54 ,[55] as rme_55 ,[56] as rme_56 ,[57] as rme_57 ,[58] as rme_58 ,[59] as rme_59 ,[60] as rme_60 ,[62] as rme_62 ,[63] as rme_63
		,[64] as rme_64 ,[65] as rme_65 ,[66] as rme_66 ,[67] as rme_67 ,[68] as rme_68 ,[69] as rme_69 ,[70] as rme_70 ,[71] as rme_71 ,[72] as rme_72
		,[73] as rme_73 ,[74] as rme_74 ,[75] as rme_75 ,[76] as rme_76 
	FROM (
		SELECT 
		*
		FROM (
			SELECT 
				pent
				,dim_year_key
				,rme
				,geo_code
			FROM [$(targetdb)].[$(targetschema)].[$(module)_year_emp]
			WHERE geo = 'ta'
			) as t
		PIVOT (
		sum([rme]) 
		FOR [geo_code] IN ([1],[2],[3],[11],[12],[13],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],
		[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[57],[58],[59],[60],[62],[63],[64],[65],[66],[67],[68],[69],[70],[71],[72],[73],[74],[75],[76])
		) as pivot_table
	) AS pva
	INNER JOIN [$(targetdb)].[$(targetschema)].[$(module)_year_emp_denom] b ON pva.pent=b.pent and pva.dim_year_key=b.dim_year_key
	GO

/*fte wide regional council*/
IF OBJECT_ID('[$(targetschema)].[$(module)_year_fte_rc_wide]','V') IS NOT NULL
	DROP VIEW [$(targetschema)].[$(module)_year_fte_rc_wide]
GO

CREATE VIEW [$(targetschema)].[$(module)_year_fte_rc_wide]  AS
	SELECT 
		pva.pent
		,pva.dim_year_key
		,b.tot_fte
		,[1] as fte_1
		,[2] as fte_2
		,[3] as fte_3
		,[4] as fte_4
		,[5] as fte_5
		,[6] as fte_6
		,[7] as fte_7
		,[8] as fte_8
		,[9] as fte_9
		,[12] as fte_12
		,[13] as fte_13
		,[14] as fte_14
		,[15] as fte_15
		,[16] as fte_16
		,[17] as fte_17
		,[18] as fte_18
	FROM (
		SELECT 
		*
		FROM (
			SELECT 
				pent
				,dim_year_key
				,fte
				,geo_code
			FROM [$(targetdb)].[$(targetschema)].[$(module)_year_emp]
			WHERE geo = 'rc'
			) as t
		PIVOT (
		sum([fte]) 
		FOR [geo_code] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[12],[13],[14],[15],[16],[17],[18])
		) as pivot_table
	) AS pva
	INNER JOIN [$(targetdb)].[$(targetschema)].[$(module)_year_emp_denom] b ON pva.pent=b.pent and pva.dim_year_key=b.dim_year_key
	GO

/* fte wide territorial authority*/
IF OBJECT_ID('[$(targetschema)].[$(module)_year_fte_ta_wide]','V') IS NOT NULL
	DROP VIEW [$(targetschema)].[$(module)_year_fte_ta_wide]
GO

CREATE VIEW [$(targetschema)].[$(module)_year_fte_ta_wide]  AS

		SELECT 
		pva.pent
		,pva.dim_year_key
		,b.tot_fte
		,[1] as fte_1 ,[2] as fte_2	,[3] as fte_3 ,[11] as fte_11 ,[12] as fte_12 ,[13] as fte_13 ,[15] as fte_15 ,[16] as fte_16 ,[17] as fte_17
		,[18] as fte_18	,[19] as fte_19	,[20] as fte_20	,[21] as fte_21	,[22] as fte_22	,[23] as fte_23	,[24] as fte_24	,[25] as fte_25	,[26] as fte_26
		,[27] as fte_27 ,[28] as fte_28 ,[29] as fte_29 ,[30] as fte_30 ,[31] as fte_31 ,[32] as fte_32 ,[33] as fte_33 ,[34] as fte_34 ,[35] as fte_35
		,[36] as fte_36 ,[37] as fte_37 ,[38] as fte_38 ,[39] as fte_39 ,[40] as fte_40 ,[41] as fte_41 ,[42] as fte_42 ,[43] as fte_43 ,[44] as fte_44
		,[45] as fte_45 ,[46] as fte_46 ,[47] as fte_47 ,[48] as fte_48 ,[49] as fte_49 ,[50] as fte_50 ,[51] as fte_51 ,[52] as fte_52 ,[53] as fte_53
		,[54] as fte_54 ,[55] as fte_55 ,[56] as fte_56 ,[57] as fte_57 ,[58] as fte_58 ,[59] as fte_59 ,[60] as fte_60 ,[62] as fte_62 ,[63] as fte_63
		,[64] as fte_64 ,[65] as fte_65 ,[66] as fte_66 ,[67] as fte_67 ,[68] as fte_68 ,[69] as fte_69 ,[70] as fte_70 ,[71] as fte_71 ,[72] as fte_72
		,[73] as fte_73 ,[74] as fte_74 ,[75] as fte_75 ,[76] as fte_76 
	FROM 
		(
		SELECT 
		*
		FROM (
			SELECT 
				pent
				,dim_year_key
				,fte
				,geo_code
			FROM [$(targetdb)].[$(targetschema)].[$(module)_year_emp]
			WHERE geo = 'ta'
			) as t
		PIVOT (
		sum([fte])
		FOR [geo_code] IN ([1],[2],[3],[11],[12],[13],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],
		[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[57],[58],[59],[60],[62],[63],[64],[65],[66],[67],[68],[69],[70],[71],[72],[73],[74],[75],[76])
		) as pivot_table
	) AS pva
	INNER JOIN [$(targetdb)].[$(targetschema)].[$(module)_year_emp_denom] b ON pva.pent=b.pent and pva.dim_year_key=b.dim_year_key
	GO