Category: Data Engineering

After the data refresh (database restore), run this, otherwise the job would be failing;

USE MyDatabase;  
GO  
EXEC sp_change_users_login 'Update_One', 'MySchma', 'MyUser';  
GO  
EXEC sp_change_users_login 'Update_One', 'MySchema', 'MyUser';  
GO  

We can use Regular Expression to find and replace, valid with all versions of SSMS

• Find what: {.+}
• Replace with: ‘\1’,
• Look in: Selection
• Expand Find Option
• Use: Regular expression (checked)

That regular expression indicates find everything and remember what we found Replace everything we found \1 by wrapping it with tic marks and a comma.

If you have more complex requirements, the right chevron next to the drop down arrow on Find what lists the regular expression dialect SSMS/Visual Studio understands

References

https://dba.stackexchange.com/questions/96371/applying-quotes-across-multiple-lines

SQL Server 2012 has two new analytical functions, LEAD() AND LAG(). These functions return data from Next row (LEAD) and Previous row (LAG) of the same dataset without using self-join.

Let’s go with an example;

This is my initial dataset;

Here is the query to create this dataset;

IF OBJECT_ID('tempdb..#SampleDataSet') is not null drop table #SampleDataSet

SELECT *
INTO #SampleDataSet
FROM
(
	SELECT 1 LineId, '2017-04-01' StockId, 'Toyota' AS Make, 'Brown' AS Color, 'Sale' AS ActionType, '2017-4-15' ActionDate UNION
	SELECT 2 LineId, '2017-04-01' StockId, 'Toyota' AS Make, 'Brown' AS Color, 'Quote' AS ActionType, '2017-4-10' ActionDate UNION
	SELECT 3 LineId, '2017-05-01' StockId,'Toyota' AS Make, 'Red' AS Color, 'Sale' AS ActionType, '2017-5-15' ActionDate UNION
	SELECT 4 LineId, '2017-05-01' StockId, 'Toyota' AS Make, 'Red' AS Color, NULL AS ActionType, '2017-5-10' ActionDate UNION
	SELECT 5 LineId, '2017-06-01' StockId, 'Toyota' AS Make, 'Pink' AS Color, 'Sale' AS ActionType, '2017-6-15' ActionDate UNION
	SELECT 6 LineId, '2017-06-01' StockId, 'Toyota' AS Make, 'Pink' AS Color, 'Quote' AS ActionType, '2017-6-10' ActionDate UNION
	SELECT 7 LineId, '2017-07-01' StockId, 'Toyota' AS Make, 'Green' AS Color, 'Sale' AS ActionType, '2017-7-15' ActionDate UNION
	SELECT 8 LineId, '2017-07-01' StockId, 'Toyota' AS Make, 'Green' AS Color, 'Quote' AS ActionType, '2017-7-10' ActionDate UNION
	SELECT 9 LineId, '2017-08-01' StockId, 'Honda' AS Make, 'Silver' AS Color, 'Sale' AS ActionType, '2017-8-15' ActionDate UNION
	SELECT 10 LineId, '2017-08-01' StockId, 'Honda' AS Make, 'Silver' AS Color, 'Quote' AS ActionType, '2017-8-10' ActionDate UNION
	SELECT 11 LineId, '2017-09-01' StockId, 'Honda' AS Make, 'Brown' AS Color, 'Sale' AS ActionType, '2017-9-15' ActionDate UNION
	SELECT 12 LineId, '2017-09-01' StockId, 'Honda' AS Make, 'Brown' AS Color, NULL AS ActionType, '2017-9-10' ActionDate UNION
	SELECT 13 LineId, '2017-10-01' StockId, 'Honda' AS Make, 'Red' AS Color, 'Sale' AS ActionType, '2017-10-15' ActionDate UNION
	SELECT 14 LineId, '2017-10-01' StockId, 'Honda' AS Make, 'Red' AS Color, 'Quote' AS ActionType, '2017-10-10' ActionDate
) src
WHERE 1=1

/*
	This is my initial dataset
*/

SELECT * FROM #SampleDataSet src

I am adding SeqCount and Seq column to identify each group of car (make and color) and each action in the group. LAG and Lead values of LineId column are used to demonstrate function output.

SELECT 
	src.LineId, src.StockId, 
	ROW_NUMBER() OVER (PARTITION BY StockId ORDER BY ActionDate DESC) Seq, 
	COUNT(*) OVER (PARTITION BY StockId) SeqCount, 
	src.Make, src.Color, src.ActionType, src.ActionDate,
	LEAD(src.LineId) OVER (ORDER BY src.LineId) LeadValue,
	LAG(src.LineId) OVER (ORDER BY src.LineId) LagValue
FROM #SampleDataSet src

This is the output of above query;

You can see the application of LEAD() and LAG() function in last two columns on the right side of above dataset.

One of the possible application of this function. As a business rule every quote action must be preceded by sale action in a car dealer dataset. If quote action is null in a group, fix it.

SELECT 
	src.StockId, src.Seq, src.SeqCount, src.Make, src.Color, src.ActionType,
	CASE WHEN src.LeadValue = 'Sale' AND src.ActionType IS NULL THEN 'Quote' ELSE src.ActionType END dActionType,
	src.ActionDate, src.LeadValue, src.LagValue
FROM
(
	--Dataset transformation
	SELECT 
		src.StockId, 
		ROW_NUMBER() OVER (PARTITION BY StockId ORDER BY ActionDate DESC) Seq, 
		COUNT(*) OVER (PARTITION BY StockId) SeqCount, 
		src.Make, src.Color, src.ActionType, src.ActionDate,
		LEAD(ActionType) OVER (ORDER BY ActionDate DESC) LeadValue,
		LAG(ActionType) OVER (ORDER BY ActionDate DESC) LagValue
	FROM #SampleDataSet src
) src
WHERE 1=1

Here is the output;

dActionType is a derived column that has all the correct sequence of actions with the help of Lead() and LAG() function.

Resources

https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-2017