Help changing the format of a temporal db

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

General SQL Server Forums

New to SQL Server Programming

Help changing the format of a temporal db

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2014-11-08 : 10:20:10

Initially I created my own version of what a temporal table should look like. After some research I discovered the type 2 slowly changing dimension which makes more sense. (http://en.wikipedia.org/wiki/Slowly_changing_dimension).

Now I have 3 million rows that I don't want to lose (and can probably aggregate into 500K) if I can query/update them properly. So I need to create an insert statement from a select - easy enough :-p - but I can't wrap my mind around how.

MySKUHere represents a single SKU across all of it's data in my original table. However, there are multiple.

ID	ProductID	Actual_Quantity	Cost	Posted_Quantity	DateChanged
108788	MySKUHere	19	350.69	14	8/29/14 1:06 PM
435287	MySKUHere	19	350.69	14	9/3/14 6:15 PM
506959	MySKUHere	19	350.69	14	9/3/14 7:51 PM
564179	MySKUHere	19	350.69	14	9/4/14 6:47 AM
657927	MySKUHere	19	350.69	14	9/4/14 3:30 PM
755882	MySKUHere	20	350.69	15	9/5/14 4:39 PM
786945	MySKUHere	20	350.69	15	9/5/14 5:45 PM
1158396	MySKUHere	20	350.69	15	9/8/14 11:06 AM
1223164	MySKUHere	19	350.69	14	9/9/14 12:55 PM
1270492	MySKUHere	19	350.69	14	9/10/14 1:01 AM
1338233	MySKUHere	19	350.69	14	9/10/14 9:55 AM
3337978	MySKUHere	16	350.69	11	9/20/14 3:45 AM
3506143	MySKUHere	16	350.69	11	9/20/14 11:38 AM
3517289	MySKUHere	16	350.69	11	9/20/14 12:45 PM
9821408	MySKUHere	16	339.96	11	10/2/14 11:45 AM
9846910	MySKUHere	16	339.96	11	10/2/14 12:45 PM
9874745	MySKUHere	16	339.96	11	10/2/14 1:45 PM
10022199	MySKUHere	16	339.96	11	10/2/14 6:45 PM
10052747	MySKUHere	16	339.96	11	10/2/14 7:45 PM
11310398	MySKUHere	0	339.96	0	10/5/14 12:19 PM

The second dataset represents the way that the data above would be formatted if done properly. You'll notice that when one new row is created the prior row is updated/expired. I'm debating whether to use NULLs or not for the unexpired rows, but I'll circle back to that in a later post.

ID	ProductID	Actual_Quantity	Cost	Posted_Quantity	DateAdded	DateUpdated	DateExpired
108788	MySKUHere	19	350.69	14	8/29/14 1:06 PM	9/5/14 4:39 PM	9/5/14 4:39 PM
755882	MySKUHere	20	350.69	15	9/5/14 4:39 PM	9/9/14 12:55 PM	9/9/14 12:55 PM
1338233	MySKUHere	19	350.69	14	9/10/14 9:55 AM	9/20/14 3:45 AM	9/20/14 3:45 AM
3517289	MySKUHere	16	350.69	11	9/20/14 12:45 PM	10/2/14 11:45 AM	10/2/14 11:45 AM
10052747	MySKUHere	16	339.96	11	10/2/14 7:45 PM	10/5/14 12:19 PM	10/5/14 12:19 PM
11310398	MySKUHere	0	339.96	0	10/5/14 12:19 PM	NULL	NULL

-Sergio
I use Microsoft SQL 2008

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-11-10 : 07:55:13

I do not understand your data, but you may want to start with something like:


-- *** Test Data ***
CREATE TABLE #t
(
	ID int NOT NULL
	,ProductID varchar(20) NOT NULL
	,Actual_Quantity int NOT NULL
	,Cost money NOT NULL
	,Posted_Quantity int NOT NULL
	,DateChanged datetime NOT NULL
);
INSERT INTO #t
VALUES (108788, 'MySKUHere', 19, 350.69, 14, '20140829 1:06 PM') - -added
	,(435287, 'MySKUHere', 19, 350.69, 14, '20140903 6:15 PM')
	,(506959, 'MySKUHere', 19, 350.69, 14, '20140903 7:51 PM')
	,(564179, 'MySKUHere', 19, 350.69, 14, '20140904 6:47 AM')
	,(657927, 'MySKUHere', 19, 350.69, 14, '20140904 3:30 PM')
	,(755882, 'MySKUHere', 20, 350.69, 15, '20140905 4:39 PM') --Expired/updated
	,(786945, 'MySKUHere', 20, 350.69, 15, '20140905 5:45 PM')
	,(1158396, 'MySKUHere', 20, 350.69, 15, '20140908 11:06 AM')

	,(1223164, 'MySKUHere', 19, 350.69, 14, '20140909 12:55 PM')--
	,(1270492, 'MySKUHere', 19, 350.69, 14, '20140910 1:01 AM')
	,(1338233, 'MySKUHere', 19, 350.69, 14, '20140910 9:55 AM')

	,(3337978, 'MySKUHere', 16, 350.69, 11, '20140920 3:45 AM')--
	,(3506143, 'MySKUHere', 16, 350.69, 11, '20140920 11:38 AM')
	,(3517289, 'MySKUHere', 16, 350.69, 11, '20140920 12:45 PM')
	,(9821408, 'MySKUHere', 16, 339.96, 11, '20141002 11:45 AM')
	,(9846910, 'MySKUHere', 16, 339.96, 11, '20141002 12:45 PM')
	,(9874745, 'MySKUHere', 16, 339.96, 11, '20141002 1:45 PM')
	,(10022199, 'MySKUHere', 16, 339.96, 11, '20141002 6:45 PM')
	,(10052747, 'MySKUHere', 16, 339.96, 11, '20141002 7:45 PM')

	,(11310398, 'MySKUHere', 0, 339.96, 0, '20141005 12:19 PM');
-- *** End Test Data ***

WITH Grps
AS
(
	SELECT MIN(ID) AS ID
		,ProductID, Actual_Quantity, Cost, Posted_Quantity
		,MIN(DateChanged) AS DateChanged
		,ROW_NUMBER() OVER (ORDER BY MIN(DateChanged)) AS rn
	FROM #t
	GROUP BY ProductID, Actual_Quantity, Cost, Posted_Quantity
)
SELECT G1.ID, G1.ProductID, G1.Actual_Quantity, G1.Cost, G1.Posted_Quantity
	,G1.DateChanged AS DateAdded
	,G2.DateChanged AS DateUpdated
	,G2.DateChanged AS DateExpired
FROM Grps G1
	LEFT JOIN Grps G2
		ON G1.rn = G2.rn -1
ORDER BY ID;

SergioM
Posting Yak Master

170 Posts

Posted - 2014-11-11 : 17:33:15

Wow! That was exactly what I needed. Thank you!

-Sergio
I use Microsoft SQL 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources