Populate new record based on previous period data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Populate new record based on previous period data

Author

Topic

yingchai
Starting Member

33 Posts

Posted - 2012-01-18 : 22:13:22

Hi SQL gurus,

I have my original fact table here and would like to translate to a view. The goal was to always bring the prior month data forward if it existed to the next month.

[Original fact table]


Account   | Organization    | Period   | Year | Amount 
----------------------------------------------------- 
CMP1000   | PM1             | 11       | 2011 | 100 --Row A
CMP1001   | PM1             | 11       | 2011 | 101
CMP1002   | PM1             | 11       | 2011 | 102
CMP1003   | PM1             | 11       | 2011 | 103

CMP1001   | PM1             | 12       | 2011 | 111 --Row B
CMP1002   | PM1             | 12       | 2011 | 112
CMP1003   | PM1             | 12       | 2011 | 113

CMP1002   | PM1             | 01       | 2012 | 122
CMP1003   | PM1             | 01       | 2012 | 123

[desired view]


Account   | Organization    | Period   | Year | Amount 
----------------------------------------------------- 
CMP1000   | PM1             | 11       | 2011 | 100
CMP1001   | PM1             | 11       | 2011 | 101
CMP1002   | PM1             | 11       | 2011 | 102
CMP1003   | PM1             | 11       | 2011 | 103

CMP1001   | PM1             | 12       | 2011 | 111
CMP1002   | PM1             | 12       | 2011 | 112
CMP1003   | PM1             | 12       | 2011 | 113
CMP1000   | PM1             | 12       | 2011 | 100 --Copy from Row A

CMP1002   | PM1             | 01       | 2012 | 122
CMP1003   | PM1             | 01       | 2012 | 123
CMP1001   | PM1             | 01       | 2012 | 111 --Copy from Row B
CMP1000   | PM1             | 01       | 2012 | 100 --Copy from Row A

Please advise. Thanks!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-01-19 : 07:51:57

You probably have separate tables for Accounts and Periods but working with the current informataion they will have to be derived.
Try something like:


-- *** Test Data ***
CREATE TABLE #t
(
	Account varchar(20) NOT NULL
	,Organization varchar(20) NOT NULL
	,Period char(2) NOT NULL
	,[Year] char(4) NOT NULL
	,Amount money NOT NULL
);
INSERT INTO #t
VALUES('CMP1000', 'PM1', '11', '2011', 100)
	,('CMP1001', 'PM1', '11', '2011', 101)
	,('CMP1002', 'PM1', '11', '2011', 102)
	,('CMP1003', 'PM1', '11', '2011', 103)
	,('CMP1001', 'PM1', '12', '2011', 111)
	,('CMP1002', 'PM1', '12', '2011', 112)
	,('CMP1003', 'PM1', '12', '2011', 113)
	,('CMP1002', 'PM1', '01', '2012', 112)
	,('CMP1003', 'PM1', '01', '2012', 113);
-- *** End Test Data ***

WITH Accounts
AS
(
	SELECT DISTINCT Account, Organization
	FROM #t
)
, Periods
AS
(
	SELECT DISTINCT [Year], Period
	FROM #t
)
SELECT A.Account, A.Organization, P.Period, P.[Year]
	,COALESCE(T.Amount, L.Amount, CAST(0 AS money)) AS Amount
FROM Accounts A
	CROSS JOIN Periods P
	LEFT JOIN #t T
		ON A.Account = T.Account
			AND A.Organization = T.Organization
			AND P.[Year] = T.[Year]
			AND P.Period = T.Period
	OUTER APPLY
	(
		SELECT TOP (1) T1.Amount
		FROM #t T1
		WHERE T1.Account = A.Account
			AND T1.Organization = A.Organization
			AND
			(
				T1.[Year] < P.[Year]
				OR
				(
					T1.[Year] = P.[Year]
					AND T1.Period < P.Period
				)
			)
		ORDER BY T1.[Year] DESC, T1.Period DESC
	) AS L
ORDER BY [Year], Period, Organization, Account;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources