Get Last Two Rows Value Difference (T-sql )

SQL Server 2012 or 2014 solution.

 CREATE TABLE MeterReading
(
 ReadingID int,
 MeterID int,
 DataCounter int,
 Value numeric(16,4)
)
INSERT INTO MeterReading 
VALUES(1, 1, 1, 102.45)
INSERT INTO MeterReading 
VALUES(2, 2, 1, 110.45)
INSERT INTO MeterReading 
VALUES(3, 1, 2, 110.58)
INSERT INTO MeterReading 
VALUES(4, 2, 2, 115.89)
INSERT INTO MeterReading 
VALUES(5, 1, 4, 120.00)
INSERT INTO MeterReading 
VALUES(6, 2, 4, 106.23)
--Here i need the difference of latest (based on DataCounter) difference of Values corresponding MeterID


;with mycte as (
select  * , abs(value-avg(value) Over(Partition by MeterID Order by DataCounter desc ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ))*2 as LastTwoDiffVal 
,row_number() Over(Partition by MeterID Order by DataCounter desc) rn from MeterReading ) 
Select ReadingID,
 MeterID,
 DataCounter, Value, LastTwoDiffVal from mycte
WHERE rn=1
Order by MeterID
--Or;with mycte as (
select  * , max(value) Over(Partition by MeterID Order by DataCounter desc ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) -  min(value) Over(Partition by MeterID Order by DataCounter desc ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) as LastTwoDiffVal  
,row_number() Over(Partition by MeterID Order by DataCounter desc) rn from MeterReading ) 
Select ReadingID, MeterID, DataCounter, Value, LastTwoDiffVal  from mycte
WHERE rn=1
Order by MeterID


drop table MeterReading


 

http://social.msdn.microsoft.com/Forums/en-US/ca938faf-49a7-4212-afd2-19046cbfcc8b/diffrence-of-last-2-rows?forum=transactsql

Sliding AVG From Both Directions–T-SQL

CREATE TABLE [dbo].[testTable](
	[colA] [char](2) NULL,
	[colDate] [date] NULL,
	[colC] [char](2) NULL,
	[colD] [char](4) NULL,
	[colValue] [tinyint] NULL
) ON [PRIMARY]

INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hA', N'0000', 2)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hA', N'0005', 4)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hA', N'0010', 5)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hA', N'0015', 3)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hA', N'0020', 1)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hB', N'0025', 6)--changed from '0035'
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hB', N'0030', 2)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hB', N'0035', 3)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hB', N'0040', 4)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hB', N'0045', 3)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hA', N'0000', 1)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hA', N'0005', 9)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hA', N'0010', 10)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hA', N'0015', 8)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hA', N'0020', 7)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hB', N'0025', 2)--changed from '0035'
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hB', N'0030', 4)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hB', N'0035', 6)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hB', N'0040', 1)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hB', N'0045', 8)

;with mycte as (SELECT *, avg([colValue]) Over(PARTITION BY [colA], ColDate,Colc   Order by colD  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)  myAvg1,
avg([colValue]) Over(PARTITION BY [colA], ColDate,Colc   Order by colD  ROWS BETWEEN CURRENT ROW AND 2 Following )  myAvg2
FROM testTable)

,mycte1 as (select *, row_number() Over(partition by [colA], ColDate, ColC Order by myavg1 DESC) rn
, max(myAvg1) Over(partition by [colA], ColDate, ColC) maxAvg3 
, max(myAvg2) Over(partition by [colA], ColDate, ColC) maxAvg4 from mycte )

,mycte2 as (Select colA, ColDate, ColC, ColD, rn, myAvg1,myAvg2
, Case When maxAvg3>maxAvg4 Then maxAvg3 else maxAvg4 End maxAvg 
,row_number() Over(partition by rn Order by Case When maxAvg3>maxAvg4 Then maxAvg3 else maxAvg4 End  DESC) rn2
from mycte1
WHERE rn=1)

SELECT colA, ColDate,  ColC, ColD,  maxAvg FROM   mycte2
WHERE  rn2 = 1 



drop table testTable
/*colA	ColDate	ColC	ColD	maxAvg
cA	2014-06-02	hA	0015	9
*/


 

http://social.msdn.microsoft.com/Forums/en-US/54987b88-a236-4cce-b3d8-f264c8eca276/help-with-a-complex-group-by?forum=transactsql

ASPState Database Related

I did a quick read about ASPState for developer implementation. I found a blog from Darren Kopp to share his changes to improve performance for this database.
I read through the blog and here is the link for future reference.

http://darrenkopp.com/posts/2013/04/10/Playing-with-fire-Optimizing-the-ASPState-internals

Issue: Long Running Database Restore and a Fix

I have a new QA server with a pretty slow restore operation. I found PREEMPTIVE_OS_WRITEFILEGATHER is very high by using Glenn Berry’s query
and I recalled from previous readings about this issue.
We need to assign SQL Server service account to perform volumn maintenance tasks.
The folllowing is how to and some references to explain why.

How to grant service account to “PERFORM VOLUME MAINTENANCE TASKS”

From Window machines search secpol.msc.

Use secpol.msc (search from window search box or search Gpedit.msc to drill down to Local Policies …)

From UI:
WIn 2012 Administrative Tools ->Local Security Policy>>Local Policies -> User Right Assignment -> Perform Volume Maintenance Tasks
Right click on it and click Add User or Group to add the SQL Server Service account running on your instance.

Restart SQL Server service to make the change into effect.

Instant Initialization – What, Why and How?

http://www.brentozar.com/archive/2014/02/preemptive_os_writefilegather-and-instant-file-initialization-ifi/

SQL Server Diagnostic Information Queries for April 2014

From above link:
(Glenn Berry’s check wait query)

   

;WITH Waits
AS (SELECT wait_type, CAST(wait_time_ms / 1000. AS DECIMAL(12, 2)) AS [wait_time_s],
	CAST(100. * wait_time_ms / SUM(wait_time_ms) OVER () AS DECIMAL(12,2)) AS [pct],
	ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC) AS rn
	FROM sys.dm_os_wait_stats WITH (NOLOCK)
	WHERE wait_type NOT IN (N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP', N'RESOURCE_QUEUE',N'SLEEP_TASK',
			                N'SLEEP_SYSTEMTASK', N'SQLTRACE_BUFFER_FLUSH', N'WAITFOR', N'LOGMGR_QUEUE',
			                N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_TIMER_EVENT',
			                N'BROKER_TO_FLUSH', N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT', N'CLR_AUTO_EVENT',
			                N'DISPATCHER_QUEUE_SEMAPHORE' ,N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'XE_DISPATCHER_WAIT',
			                N'XE_DISPATCHER_JOIN', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'ONDEMAND_TASK_QUEUE',
			                N'BROKER_EVENTHANDLER', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'DIRTY_PAGE_POLL',
			                N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',N'SP_SERVER_DIAGNOSTICS_SLEEP',
							N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
							N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE',
							N'PWAIT_ALL_COMPONENTS_INITIALIZED')),
Running_Waits 
AS (SELECT W1.wait_type, wait_time_s, pct,
	SUM(pct) OVER(ORDER BY pct DESC ROWS UNBOUNDED PRECEDING) AS [running_pct]
	FROM Waits AS W1)
SELECT wait_type, wait_time_s, pct, running_pct
FROM Running_Waits
WHERE running_pct - pct <= 99
ORDER BY running_pct
OPTION (RECOMPILE);



 

Package with XMLDatasource Migrated to SSIS 2014 with Error: “the data source you are using does not support mixed content in elements..”

When I migrate an SSIS package which includes an XMLDatasource to import data from SSIS 2008 to SSIS 2014,
I ran into the following error:
“the data source you are using does not support mixed content in elements..”
We have no control for the coming XML file so I decide to move the logic into database
and use an Execute SQL Task to pull data from the coming XML file.

Here is the query I used for the SQL Task:

--Target table
CREATE TABLE [dbo].[SomeTable](
	[cat-code] [nvarchar](255) NULL,
	[class-code] [nvarchar](255) NULL,
	[subclass-code] [nvarchar](255) NULL,
	[ad-number] [nvarchar](255) NULL,
	[start-date] [nvarchar](255) NULL,
	[end-date] [nvarchar](255) NULL
	
) 


--Solution query

declare @tmp table (XmlCol XML)

INSERT INTO @tmp(XmlCol) 
SELECT * FROM OPENROWSET( BULK 'E:\X\data\myXMLfeed.xml', SINGLE_BLOB) AS x ;
 


 insert into  [dbo].[Sometable]

SELECT 
   p.value('(./cat-code)[1]', 'NVARCHAR(255)') AS [cat-code],
   p.value('(./class-code)[1]', 'NVARCHAR(255)') AS [class-code],
   p.value('(./subclass-code)[1]', 'NVARCHAR(255)') AS [subclass-code],
   p.value('(./ad-number)[1]', 'NVARCHAR(255)') AS [ad-number],
   p.value('(./start-date)[1]', 'NVARCHAR(255)') AS [start-date],
   p.value('(./end-date)[1]', 'NVARCHAR(255)') AS [end-date]     
FROM @tmp 
   CROSS APPLY XmlCol.nodes('/web-export/run-date/pub-code/ad-type/.') t(p)

 

A few Queries that are related to Agent Information

You can tweak a few useful queries Datta Sindol compiled to make them useful for you if you want to get some information about agent jobs from MSDB.
Here is the link:

http://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/

Migrate Encrypted Database to a New Server (with a new key)

 
/* Migrating SQL Server Databases that use Database Master Key
--http://mattsql.wordpress.com/2012/11/13/migrating-sql-server-databases-that-use-database-master-keys/

--Step 1:
--Find databases:  Database Master Keys encrypted by the Service Master Key:

select name
from sys.databases
where is_master_key_encrypted_by_server = 1

--Step 2:
/*----Add one new password for DMK in the source server database
-- If you don’t know a valid password for the Database Master Key, you can create a new one. 
--(! Multiple passwords can encrypt the same Database Master Key)
*/
 
--USE [theDB]
--GO
 
--alter master key 
--add encryption by password = 'myPass1d2f3wStrong'
--go


--Step 3:
--Backup  theDB and restore it to a new SERVER.
--3-1:
USE [theDB]
go
open master key decryption by password = 'myPass1d2f3wStrong'
alter master key 
drop encryption by service master key
go

--3-2:
USE [theDB]
go
open master key decryption by password = 'myPass1d2f3wStrong'
alter master key 
add encryption by service master key
go




*/

  


 

SQL Server 2014 Backup Restore Error (Version 12.00.2000) to Early Version

If you try to restore a backup taken from SQL Server 2014 server, you will run into incompatibility problem.
Just like before, you cannot restore SQL Server 2014 backup to early version.
The error looks like below:

Restore of database ‘xxx’ failed.
The database was backed up on a server running version 12.00.2000.
That version is incompatible with this server, which is running version 11.00.2218.