Sample Code As Below

CREATE

TABLE[dbo].[WORK_Q3_RECORDS_2015](

[GBL_RGNL_ACCT_ID][float]NULL,

[GBL_RGNL_ACCT_NM][nvarchar](255)NULL,

[SUB_ACCT_ID][float]NULL,

[SUB_ACCT_NM][nvarchar](255)NULL,

[LOB_DESC][nvarchar](255)NULL,

[BRAND_CATG_DESC][nvarchar](255)NULL,

[SEGMENT][nvarchar](255)NULL,

[FISCAL QUARTER][nvarchar](255)NULL,

[FISCAL WEEK][nvarchar](255)NULL,

[SYSTEM QUANTITY][float]NULL

)

ON[PRIMARY]

INSERT

INTO[dbo].[WORK_Q3_RECORDS_2015]

SELECT

1590909081

,'DIVERSIFIED SYSTEMS INC',1590909081,'DIVERSIFIED SYSTEMS INC','Mobile Workstations','DELL PRECISION M2800','CHANNEL','2015-Q3','2015-W27',2

CREATE

TABLE[dbo].[WORK_Q3_RECORDS_2016](

[GBL_RGNL_ACCT_ID][float]NULL,

[GBL_RGNL_ACCT_NM][nvarchar](255)NULL,

[SUB_ACCT_ID][float]NULL,

[SUB_ACCT_NM][nvarchar](255)NULL,

[LOB_DESC][nvarchar](255)NULL,

[BRAND_CATG_DESC][nvarchar](255)NULL,

[SEGMENT][nvarchar](255)NULL,

[FISCAL QUARTER][nvarchar](255)NULL,

[FISCAL WEEK][nvarchar](255)NULL,

[SYSTEM QUANTITY][float]NULL

)

ON[PRIMARY]

INSERT

INTO[dbo].[WORK_Q3_RECORDS_2016]

SELECT

1590909081

,'DIVERSIFIED SYSTEMS INC','1590909081','DIVERSIFIED SYSTEMS INC','Mobile Workstations','DELL PRECISION M2800','CHANNEL','2016-Q3','2016-W27',4

Desired Result as below

Global Account No	Segment	Account Growth %	FY15Q3 (Units)	FY16Q3 (Units)
1590909081	channel	(need to calculate)	2	4

Basically I need to calculate Growth percentage for the system quantity from 2015-q3 to 2016_q3

2015-q3 has 2 units but 2016-q3 has 4 units for the brand and Global Account No

Hi,

you can do something like this with APPLY.

SELECT
	a.GBL_RGNL_ACCT_ID,
	a.SEGMENT,
	a.[SYSTEM QUANTITY] AS "2015",
	b.[SYSTEM QUANTITY] AS "2016",
	b.[SYSTEM QUANTITY] / a.[SYSTEM QUANTITY] AS "Account Growth %"
FROM dbo.WORK_Q3_RECORDS_2015 AS a
OUTER APPLY(
	SELECT TOP 1 *
	FROM dbo.WORK_Q3_RECORDS_2016 AS b
	WHERE a.GBL_RGNL_ACCT_ID = b.GBL_RGNL_ACCT_ID
	AND a.SUB_ACCT_ID = b.SUB_ACCT_ID
	AND a.SEGMENT = b.SEGMENT
	AND '2016' + RIGHT(a.[FISCAL QUARTER],3)  = b.[FISCAL QUARTER]) AS b

• Edited by Steelleg4 20 hours 11 minutes ago
• Proposed as answer by Eric__ZhangMicrosoft contingent staff, Moderator 5 hours 23 minutes ago

See if this helps you

SELECT  CAST((B.[SYSTEM QUANTITY]-A.[SYSTEM QUANTITY])/A.[SYSTEM QUANTITY]*100.0
AS DECIMAL(5,2)) [Segment Account Growth %] FROM [WORK_Q3_RECORDS_2015] A
JOIN [WORK_Q3_RECORDS_2016] B 
ON A.GBL_RGNL_ACCT_ID=B.GBL_RGNL_ACCT_ID

This is not valid DDL. A table has a key by definition. But his mess cannot ever have a key; everything is NULL-able! This is called a "pile" in RDBMS slang. 

We never use numeric data types for identifiers; we do not do math onthem. Do not use insanely wide columns of the wrong data types. We not live in a world of Chinese Unicode sutras. 

Table names tell us what the set of data is by its nature; but you name them after a temporal attribute value. Swoiydkl you have a Personnel table, or split it on sex_code into Male_Personnel And Female_Personnel? NO! Well, that is what you did with time. 

Your design is so bad it has a name; attribute splitting. There should be one table for the  (terrible, vague name) from which we extract quarterly data in VIEWs or queries. Can we compute the quarters from the weeks? 

When designing an accounting system Chart of accounts, the account identifiers are not split; they are part of one attribute. 

The use of FLOAT in accounting is usually illegal. GAAP and EU regulations require decimals. But if you are copying a spreadsheet instead of doing a valid RDBMS design, you will get them. And how will you have a system quantity of 3.141592653? Quantity is usually an integer or a simple decimal. 

CREATE TABLE Something 
(gbl_rgnl_acct_id CHAR(10) NOT NULL,
 something_week CHAR(8) NOT NULL 
   CHECK(something_week LIKE '[12][0-9][0-9][0-9]W-[0-5][0-9]'),
 PRIMARY KEY (gbl_rgnl_acct_id, something_week), 
 gbl_rgnl_acct_name VARCHAR(35) NOT NULL,
 lob_desc VARCHAR(255) NOT NULL,
 brand_catg_desc VARCHAR(255) NOT NULL,
 segment_name VARCHAR(25) NOT NULL,
 system_qty INTEGER NOT NULL
); 

Is this a good guess? I will need a calendar table to get the quarters.