A common trick to run command in elevated permission is to right click and select Run As administrator. Other work arounds are;
Press Ctrl+Shift+Esc to open the Task Manager. Click on File menu > Run new task. To open a command prompt window, type cmd. Remember to check the Create this task with administrative privileges check-box. Then hit Enter.
You can also open an elevated command prompt from the Task Manager using CTRL Key.
Simply open the Start Menu or Start Screen and start typing the command line. Next, hold the Shift and Ctrl keys, and then hit Enter to open the command line in an elevated command prompt.
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
Start Debugging (pressing F5) is the normal way of launching your application in visual studio. Debugger would be attached automatically and you can expect to see debugging messages and breakpoints in your application.
A popular misconception is that Debugger doesn’t come into play for Release build. This isn’t true. Set a breakpoint in a Release build and then press F5 to see if it stops there. Some debugging messages are not happening in Release build for example message from System.Diagnostics.Debug class.
Start without Debugging
This will start the application (pressing CTRL + F5) without the debugger attached. That’s it. If we need to attached the debugger to this process afterwards, we can select Debug->Attach to Process option.
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.
